Conditionally Hide / Show Group Footer (1 Viewer)

[Insane_ai]

I have a report with two grouping levels, Customer and Trade Class. The Data source is a query called CIA_RPT_BASE. The purpose of the report is to show Current Invoice Aging by Customer then Trade Class.

The Customer group has a header and footer that summarizes the totals. The Trade Class group has only a footer which also summarizes the totals.

My Goal is to hide the footer if there is only one trade class to avoid double summation. I was hoping to find a way to reference the parent control but could not.

Here is my current (Working) solution:

1. I copied the CNUM field from the header to the TradeClass Footer section and named the field txtTC_CNUM to give a direct reference to the Customer Number. This facilitates the following code.

Private Sub TradeClassFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim intCount As Integer
Dim TCF_DB As DAO.Database
Dim TCF_RS As DAO.Recordset
Dim strMessage As String

Set TCF_DB = CurrentDb
Set TCF_RS = TCF_DB.OpenRecordset("SELECT DISTINCT CIA_RPT_BASE.TradeClass" _
& " From CIA_RPT_BASE" _
& " WHERE (((CIA_RPT_BASE.CNUM)= '" & Me.[txtTC_CNUM] & "'));")

intCount = TCF_RS.RecordCount

If intCount > 1 Then
Me.TradeClassFooter.Visible = True
Else
Me.TradeClassFooter.Visible = False
End If

TCF_RS.Close
TCF_DB.Close

End Sub



My question is:

Does anyone have a more efficient way of accomplishing my goal?

I am working in Access 2000.
References:
Visual Basic For Applications (default)
Microsoft Access 9.0 Object Library (default)
OLE Automation (default)
Microsoft Visual Basic for Applications Extensibility 5.3 (default)
Microsoft DAO 3.6 Object Library (Must add for DAO commands to work.)

 

[Brianwarnock]

I am as sure as I can be that altering the visibility as you are is the only way to do this.

Brian

 

[Insane_ai]

I am confident in the fact that Access has a way to refer to the parent link to the CNUM field as the Trade Class Group is subordinate to it.

I was not so confident that there is a way for me to manipulate that link.

Thank you for you feedback.

 

[Simon_MT]

I've had this problem Page Footers since Access 1997.

I realise that the this is a Section Footer but I got around the problem by addressing the individual fields with the footer. In My case the Page Header set the individual controls to Visible = False and Customer Footer sets them back to visible as it the appropriate time to display these values.

Simon