Microsoft KB Archive/131937

= ACC2: How to Reset "Page of Pages" Numbering for Report Groups =

Article ID: 131937

Article Last Modified on 11/6/2000

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q131937



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

This article demonstrates how to reset a report's page-numbering scheme so that both the page number and the total page count start at 1 for each new group. For example, if the first group of records consists of two pages, you can number them "1 of 2" and "2 of 2." If the second group of records consists of three pages, you can number them "1 of 3," "2 of 3," and "3 of 3."

CAUTION: Use the method described in this article only for a single-user database. If you use this method in a shared database, you may receive inconsistent results.

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



MORE INFORMATION
By default, when Microsoft Access generates a report, it formats and prints each record individually. Using the Pages property, you can force Microsoft Access to make an additional formatting pass of all the records in the report to determine the total number of pages required. The first formatting pass calculates the number of pages in the report; the second formatting pass uses the number of pages returned in the first pass to print the correct number of pages.

The method described in this article uses two-pass formatting and the Page property to reset the current page and total pages for each group. The first formatting pass sets the first page number in a new group to 1 and writes the total number of pages in the group to a table. The second pass retrieves that total number and prints it with the current page for each group.

To reset the current page and total pages numbering scheme for each group in a report, follow these steps:

 Open the sample database NWIND.MDB.  Create a table with the following structure and name it Category Group Pages:

      Table: Category Group Pages ---      Field Name: Category Name Data Type: Text Field Size: 15 Indexed: Yes (No Duplicates) Field Name: Page Number Data Type: Number Field Size: Long Integer

Table Properties: Category Group Pages --      PrimaryKey: Category Name  Open the List Of Products By Category report in Design view. Set the detail section's Height property to 1 inch.

NOTE: This step ensures that some Category groups are printed on more than one page. If you use this method on another report, step 4 may not be necessary. Set the Category Name footer section's ForceNewPage property to After Section.  From the View menu, choose Code to create a module, and then type the following lines in the Declarations section:

<pre class="fixed_text">      Option Explicit Dim DB As Database Dim GrpPages As RecordSet </li> From the Edit menu, choose New Procedure. In the New Procedure dialog box, type GetGrpPages in the Name box, and then choose the OK button.</li>  Type the following function:

Function GetGrpPages ' Return the group X of Y pages. ' Find the group name. GrpPages.Seek "=", Me![Category name] If Not GrpPages.NoMatch Then GetGrpPages = Me.page & "/" & GrpPages![Page Number] End If      End Function </li>  Set the report's OnOpen property to the following event procedure:

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.

Sub Report_Open (Cancel As Integer) Set DB = dbengine.workspaces(0).databases(0) DoCmd.RunSQL "Delete * From [Category Group Pages];" Set GrpPages = DB.OpenRecordset("Category Group Pages", _          DB_Open_Table) GrpPages.Index = "PrimaryKey" End Sub </li>  Set the Category Name header's OnFormat property to the following event procedure:

Sub GroupHeader2_Format (Cancel As Integer, FormatCount As Integer) ' Reset the page number at the start of the group. Me.page = 1 End Sub </li>  Set the page footer section's OnFormat property to the following event procedure:

Sub PageFooter5_Format (Cancel As Integer, FormatCount As Integer) ' Find the group. GrpPages.Seek "=", Me![Category name] If Not GrpPages.NoMatch Then ' The group is already there. If GrpPages![Page Number] < Me.page Then GrpPages.Edit GrpPages![Page Number] = Me.page GrpPages.Update End If        Else ' First page of group, so add it. GrpPages.AddNew GrpPages![Category name] = Me![Category name] GrpPages![Page Number] = Me.page GrpPages.Update End If      End Sub </li>  In the page footer section, add two text box controls as follows:

<pre class="fixed_text">      Text box: Name: GroupXY ControlSource: =GetGrpPages

Text box: Name: ReferToPages ControlSource: =Pages Visible: No

NOTE: The ReferToPages text box is necessary because it forces the report to use two-pass formatting when it is printed. </li> Preview the report. Note that the page footer displays the current page and the total pages for each group.</li></ol>

<div class="references_section">