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!!