ACC: How to Hide Duplicate Group Header Information on Report

ID: Q158935

The information in this article applies to:

• 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

4. Create a new module. In the module Declarations section, declare a

   global variable called DupeHeader:

      Global DupeHeader as String

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.

   ProductName, and set the GroupHeader property to Yes.

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.

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

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

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

Last Reviewed: November 21, 1998