How do I control the Visible property of a report section

[General Ledger]

Dear All,

I am a hack using Access 2007 that is looking for help on controlling the Visible property of a report section.

I have a report with sorts, and group headers and footers. Footers contain subtotals for the group. I want to show or hide the footer based on the count of records in that group. I know a section has a Visible property but don't know how to set to Yes or No based on the Count of records.

For example, if the count of records in a group is 1, there is no need to display subtotals for that group. However, if there is more than one record in a group, I want to show the footer section with the subtotals.

Please be explicit with any responses as I am not too smart.

Best regards,

GL

 

[vbaInet]

Well you managed to get your reports working from our first explanation in your first thread so you're smart

In the On Format event of that group section put something like this:

Me.[COLOR=Red][B]NameOfSection[/B][/COLOR].visible = (Count(*)>1)

Or you can refer to the control that does the count directly, same On Format event:

Me.[COLOR=Red][B]NameOfSection[/B][/COLOR].visible = (Val(Nz(Me.[COLOR=Red][B]NameOfCountControl[/B][/COLOR], 0))>1)

 

[General Ledger]

Sorry but I am getting an error:

Private Sub Customer_Footer_Format (Cancel As Integer, FormatCount As Integer)
Me.Customer_Footer.Visible=(Count(*)>1)
End Sub

Returns Compile error: Syntax error


I tried adding the field name I want to count
Private Sub Customer_Footer_Format (Cancel As Integer, FormatCount As Integer)
Me.Customer_Footer.Visible=(Count([So Number])>1)
End Sub

Returns Wrong number of arguments or invalid property assignment


I am at a complete loss for a solution. This kind of stuff is way over my head.

GL

 

[pbaldy]

Detail section:

Me.Detail.Visible = False

others:

Me.GroupHeader0.Visible = False

which are obviously numbered.

 

[vbaInet]

Oops...

I forgot to mention that you need to refer to the control that does the counting. So create a textbox in the Customer Footer section and put =Count(*) as the Control Source.

Here's pbaldy's suggestion which is much easier to understand:

Private Sub Customer_Footer_Format (Cancel As Integer, FormatCount As Integer)
    If Val(Me.[COLOR=Red][B]NameOfCountControl[/B][/COLOR])>1 then
        Me.Customer_Footer.Visible=True
    else
        Me.Customer_Footer.Visible=False
    end if
End Sub

Once you have that working you could try this:

Private Sub Customer_Footer_Format (Cancel As Integer, FormatCount As Integer)
    Me.Customer_Footer.Visible=(Val(Me.[COLOR=Red][B]NameOfCountControl[/B][/COLOR])>1)
End Sub

NameOfCountControl is the name of the textbox I mentioned above.