Macro using IF to set visibility in report

[kyle87]

I have created a report in Access 2010, creatively entitled "Report," with the following (relevant) fields:

Prog Type
Relationship
PROJECT_ID
SumOfAmount

I have created text boxes in the Report Footer that count distinct records for Prog Type, Relationship, and Project_ID. The count text boxes are called:

txtSumProg_Type
txtSumRelationship
txtSumPROJECT_ID

Each of the fields (Prog Type, Relationship, and PROJECT_ID) has its own footer to give a subtotal of SumOfAmount at each level. What I would like to do now is hide certain footers based on the number of unique records. For example, if there is only one Prog Type, I do not need to see the Report Footer because the Prog Type Footer and Report Footer will show the same subtotal.

What I have tried is to create a Macro called Report_Visibility that runs On Open of Report. I am using the built-in macro functionality because I am not very comfortable with VBA.

The closest I have been able to come was this attempt:

If [Reports]![Report]![txtSumProg_Type].[AfterUpdate]="1" Then

SetProperty

Control Name ReportFooter
Property Visible
Value No

Else

SetProperty

Control Name ReportFooter
Property Visible
Value Yes

End If

I wanted to get this one right before I move onto hiding the other two sections. What is happening now is that somehow the IF statement is always evaluating to TRUE and hiding my ReportFooter, regardless of the number displaying in txtSumProg_Type (which is either 1 or 3, depending on the filter).

Any help would be greatly appreciated!!

[pbaldy]

You appear to be testing after update instead of value. If you want the VBA:

http://www.baldyweb.com/ConditionalVisibility.htm

[kyle87]

Thanks for your recommendation. I'm now getting an error. When I try to run the macro as follows:

If [Reports]![Report]![txtSumProg_Type]="1" Then
SetProperty
Control Name ReportFooter
Property Visible
Value No
Else
SetProperty
Control Name ReportFooter
Property Visible
Value Yes
End If

I get an error "Type mismatch," which is less than helpful. I also tried the above without the quotation marks around the 1 with the same error. Other ideas about what I might be doing wrong?

Thanks!

[kyle87]

I tried switching over to VBA to see how that would go. I now have the following:

If Me.txtSumProg_Type = 1 Then
    Me.ReportFooter.Visible = False
Else
    Me.ReportFooter.Visible = True
End If

I am getting Run-time error '2424': The expression you entered has a field, control, or property name that Microsoft Access can't find. The debugger then highlights the line containing Me.txtSumProg_Type.

I don't understand why it can't find it

[pbaldy]

Can you post the db here? What event is the code in?

[kyle87]

I tried attaching. Let me know if this doesn't work. The VBA runs when Report opens.

Historical Analysis_scrubbed.zip

[pbaldy]

Gotta go to lunch, but try the load event rather than the open event.

[kyle87]

That worked!!! Thanks so much! I'll be able to sleep tonight

[pbaldy]

Happy to help! Now if only I could cure my wife's insomnia.