ACC: How to Hide Duplicate Group Header Information on Report

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article shows you how to use Visual Basic for Applications to hide duplicate information in successive group headers in a report.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

In a sorting and grouping report where each group header can contain information that may be duplicated from the previous group header, you can use the following code to hide duplicate information in successive group headers.

NOTE: In the following steps, some of the fields have a space in their names in version 2.0. Replace all occurrences of CategoryName, ProductName and OrderID with Category Name, Product Name, and Order ID respectively when recreating this example in version 2.0.

1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).



2. Create the following new select query in Design view based on the Categories, Products, and Order Details tables:

   
          Query: qryOrders
          ------------------------
          Field: CategoryName
             Table: Categories
          Field: ProductName
             Table: Products
          Field: OrderID
             Table: Order Details
             Criteria: <10300
          Field: Quantity
             Table: Order Details 




3. Save the query as qryOrders, and then close it.



4. Create a new module. In the module Declarations section, declare a global variable called DupeHeader:

   Global DupeHeader as String




5. Save the module as basPrint and close it.



6. Create a new report in Design view based on the qryOrders query.



7. On the View menu, click Sorting And Grouping.



8. In the first row of the Sorting And Grouping dialog box, select CategoryName in the Field/Expression box.



9. In the second row of the Sorting And Grouping dialog box, select ProductName, and set the GroupHeader property to Yes.



10. Close the Sorting And Grouping dialog box.



11. On the View menu, click Field List.



12. Drag ProductName and CategoryName from the field list to the ProductName Header section of the report.



13. Drag OrderID and Quantity to the detail section of the report.



14. On the View menu, click Properties.



15. Set the OnFormat property of the ProductName header to the following event procedure:
    
    In Microsoft Access 7.0 and 97:

    
          Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount _
                As Integer)
          If DupeHeader = Me![CategoryName] Then
             Me![CategoryName].Visible = False
          Else
             Me![CategoryName].Visible = True
          End If
          DupeHeader = Me![CategoryName]
          End Sub 

    In Microsoft Access 2.0:

    NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

    
          Sub GroupHeader3_Format(Cancel As Integer, FormatCount _
                As Integer)
          If DupeHeader = Me![Category Name] Then
             Me![Category Name].Visible = False
          Else
             Me![Category Name].Visible = True
          End If
          DupeHeader = Me![Category Name]
          End Sub 




16. Save the report as rptTest and close it.



17. Open the report in Print Preview and scroll through it. Any duplication of the CategoryName field in successive ProductName Headers will not be visible.

REFERENCES

For more information about techniques for report grouping, please see the following articles in the Microsoft Knowledge Base:

Q138770 ACC: Repeating Group Name at Top of New Column or Page 95/97

Q93927 ACC2: Repeating Group Name at Top of New Column or Page

Additional query words: invisible suppress

Keywords : kbprg RptEvent
Version : WINDOWS:2.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto