Article ID: 158935
Article Last Modified on 1/19/2007
APPLIES TO
- Microsoft Access 2.0 Standard Edition
- Microsoft Access 95 Standard Edition
- Microsoft Access 97 Standard Edition
This article was previously published under Q158935
Moderate: Requires basic macro, coding, and interoperability skills.
SUMMARY
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.
- Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).
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
- Save the query as qryOrders, and then close it.
- Create a new module. In the module Declarations section, declare a global variable called DupeHeader:
Global DupeHeader as String
- Save the module as basPrint and close it.
- Create a new report in Design view based on the qryOrders query.
- On the View menu, click Sorting And Grouping.
- In the first row of the Sorting And Grouping dialog box, select CategoryName in the Field/Expression box.
- In the second row of the Sorting And Grouping dialog box, select ProductName, and set the GroupHeader property to Yes.
- Close the Sorting And Grouping dialog box.
- On the View menu, click Field List.
- Drag ProductName and CategoryName from the field list to the ProductName Header section of the report.
- Drag OrderID and Quantity to the detail section of the report.
- On the View menu, click Properties.
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
- Save the report as rptTest and close it.
- 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:
Additional query words: invisible suppress
Keywords: kbhowto kbprogramming KB158935