Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-24-2018, 11:58 AM   #1
jeran042
Newly Registered User
 
Join Date: Jun 2017
Posts: 95
Thanks: 20
Thanked 1 Time in 1 Post
jeran042 is on a distinguished road
Set visibility Based on Calculated field

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?

jeran042 is offline   Reply With Quote
Old 10-24-2018, 12:01 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,037
Thanks: 13
Thanked 4,064 Times in 3,999 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Set visibility Based on Calculated field

Presuming they're in the detail section, try that section's format event.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 10-24-2018, 12:16 PM   #3
jeran042
Newly Registered User
 
Join Date: Jun 2017
Posts: 95
Thanks: 20
Thanked 1 Time in 1 Post
jeran042 is on a distinguished road
Re: Set visibility Based on Calculated field

I know what yo mean by the detail section, but from the looks of it, I don't see which event would apply?

jeran042 is offline   Reply With Quote
Old 10-24-2018, 12:21 PM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,037
Thanks: 13
Thanked 4,064 Times in 3,999 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Set visibility Based on Calculated field

The format event of the section is normally where I set value-based visibility.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 10-24-2018, 07:16 PM   #5
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,706
Thanks: 2
Thanked 2,073 Times in 2,028 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Set visibility Based on Calculated field

Beside from what pbaldy writes is, (I also use the format event):
Quote:
[Reports]![RPT: ON_SCREEN_REPORT_2019]![txtMajor_2017]
the report in which the code runs? If yes then use Me instead.
Quote:
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)!
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 10-24-2018, 07:44 PM   #6
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,149
Thanks: 0
Thanked 503 Times in 499 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Set visibility Based on Calculated field

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?
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 10-24-2018 at 07:56 PM.
June7 is offline   Reply With Quote
Old 10-24-2018, 09:50 PM   #7
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,992
Thanks: 64
Thanked 2,546 Times in 2,445 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Set visibility Based on Calculated field

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]

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 10-25-2018, 05:47 AM   #8
jeran042
Newly Registered User
 
Join Date: Jun 2017
Posts: 95
Thanks: 20
Thanked 1 Time in 1 Post
jeran042 is on a distinguished road
Re: Set visibility Based on Calculated field

Quote:
Originally Posted by June7 View Post
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,

jeran042 is offline   Reply With Quote
Reply

Tags
reports , vba , visibility

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Field Requirements based on conditional visibility dkmoreland Forms 8 12-20-2017 06:07 AM
Calculated field in a query/table based on a calculated field joannkt General 5 11-07-2013 11:06 AM
Visibility based on whether table field is empty acedeno Reports 1 07-11-2013 11:23 AM
Line Visibility property change based on field value virencm Reports 4 05-22-2012 08:01 PM
Conditional Visibility Based on Value of Another Field kryptkitten Reports 6 11-30-2010 10:14 AM




All times are GMT -8. The time now is 11:35 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World