Set visibility Based on Calculated field (1 Viewer)

jeran042

Registered User.
Local time
Today, 08:35
Joined
Jun 26, 2017
Messages
127
Good afternoon all,

Here is what I am trying to accomplish

I have a group of controls (unbound calculated text boxes and labels) on a report that I only want to be visible if there are values in the calculation.

For example: When I open the Accounting report, I only want to make fields visible in this group if there is a dollar amount in the control.

Here is what I have for code:
Code:
Private Sub Report_Load()

Dim ctrl As Control
Dim lTotal As Long

lTotal = [Reports]![RPT: ON_SCREEN_REPORT_2019]![txtMajor_2017]  'this is the line that throws the error

For Each ctrl In Me.Controls
    If ctrl.Tag = "Major" And lTotal > 2000000 Then
        ctrl.Visible = FALSE
    End If
Next


Debug.Print Me.txt_2017_Total_Employees

End Sub

I am getting the Run-time error 2424 Cannot find object.
Please note that I have also tried this in the On Open event, but with no success. I believe the problem is it takes a second for all the calculations to happen, so I think its isn't finding a filed because no data is loaded yet. Please correct me if I am incorrect?

My questions is, would I be better served by going about this in a different way or using a different event?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:35
Joined
Aug 30, 2003
Messages
36,118
Presuming they're in the detail section, try that section's format event.
 

jeran042

Registered User.
Local time
Today, 08:35
Joined
Jun 26, 2017
Messages
127
I know what yo mean by the detail section, but from the looks of it, I don't see which event would apply?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:35
Joined
Aug 30, 2003
Messages
36,118
The format event of the section is normally where I set value-based visibility.
 

JHB

Have been here a while
Local time
Today, 16:35
Joined
Jun 17, 2012
Messages
7,732
Beside from what pbaldy writes is, (I also use the format event):
[Reports]![RPT: ON_SCREEN_REPORT_2019]![txtMajor_2017]
the report in which the code runs? If yes then use Me instead.
Me.txtMajor_2017
That you get the error is because, (if it is same report), that the report isn't loaded and opened yet, (you run the code in the Open event)!
 

June7

AWF VIP
Local time
Today, 07:35
Joined
Mar 9, 2014
Messages
5,423
Must be OnFormat event which only triggers for PrintPreview or direct to printer.

If there are multiple records, I think need to conditionally set the visibility dynamically for each record. A one-liner can handle that.

ctrl.Visible = Not (ctrl.Tag = "Major" And lTotal > 2000000)

lTotal variable not really needed.

ctrl.Visible = Not (ctrl.Tag = "Major" And Me.txtMajor_2017 > 2000000)

Why give report and control a year specific name? Aren't you going to run report in future years?
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:35
Joined
May 7, 2009
Messages
19,169
when your report is in Load event, all controls now have been initialized and calculated.
you can use, if the control is on same form.

lTotal=[Form]![txtMajor_2017]
 

jeran042

Registered User.
Local time
Today, 08:35
Joined
Jun 26, 2017
Messages
127
Must be OnFormat event which only triggers for PrintPreview or direct to printer.

If there are multiple records, I think need to conditionally set the visibility dynamically for each record. A one-liner can handle that.

ctrl.Visible = Not (ctrl.Tag = "Major" And lTotal > 2000000)

lTotal variable not really needed.

ctrl.Visible = Not (ctrl.Tag = "Major" And Me.txtMajor_2017 > 2000000)

Why give report and control a year specific name? Aren't you going to run report in future years?

Thank you all,

So the "ITotal" and the "[Reports]![RPT: ON_SCREEN_REPORT_2019]![txtMajor_2017]" was all placeholder data. I was trying to get the actual function to work, then change the pieces to be dynamic based on which report it needed to be on.

I will try the suggestions, and report back.

Much appreciated,
 

Users who are viewing this thread

Top Bottom