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 demonstrates 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:

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

   ARTICLE-ID: Q93927
   TITLE     : ACC2: Repeating Group Name at Top of New Column or Page