ACC2000: How to Hide Duplicate Group Header Information in a Report

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

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.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

1. Start Microsoft Access and open the sample database Northwind.mdb.
2. Create the following new select query in Design view based on the Categories, Products, and Order Details tables:
   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 then 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 then set the GroupHeader property to Yes.
10. Close the Sorting and Grouping dialog box.
11. On the View menu, click Field List.
12. Drag CategoryName and ProductName 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. If the property sheet is not visible, click Properties on the View menu.
15. Set the Height property of the Detail section to .25".
16. Set the OnFormat property of the ProductName header to the following event procedure:

    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
    					

17. Save the report as rptTest, and then close it.
18. Open the report in Print Preview and scroll through it. Any duplication of the CategoryName field in successive ProductName headers is not visible.

For additional information about techniques for report grouping, click the article number below to view the article in the Microsoft Knowledge Base: