Microsoft KB Archive/306127

= ACC97: How to Reset the Page Number and Total Page Count for Each Section of a Report =

Article ID: 306127

Article Last Modified on 1/31/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q306127



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



SUMMARY
This article demonstrates how to reset a report's page-numbering scheme and the total page count so that each new group of pages starts with &quot;1.&quot;

You can use this method to reset the page number and total page count for each group when the page number is displayed in the page footer.

IMPORTANT: Use the method that is described in this article only in a single-user database. If you try to use this method in a shared database, you may receive inconsistent results.



MORE INFORMATION
By using a macro or code with a report's section properties, you can design a report that breaks the page for each new entry in a group and resets the report's page number. For example, if the first group of records consists of two pages, you can number them &quot;1 of 2&quot; and &quot;2 of 2.&quot; If the second group of records consists of three pages, you can number them &quot;1 of 3,&quot; &quot;2 of 3,&quot; and &quot;3 of 3.&quot;

To accomplish this goal, group the report's &quot;Page&quot; and &quot;Pages&quot; to get the &quot;Page of Pages&quot; grouping. The following steps use the Employee Sales By Country report in the sample database, Northwind.mdb, to demonstrate the grouping of &quot;Page&quot; capabilities.

Preparing the Report

 * 1) Open the sample database, Northwind.mdb.
 * 2) Open the Employee Sales By Country report in Design view.
 * 3) Click the Country Header section, and then click the Build button of the OnFormat property. Review the code for the OnFormat event.
 * 4) Select the Country footer section, and then set the ForceNewPage property to After Section.

Grouping the &quot;Pages&quot;
The grouping of &quot;Page&quot; capabilities that is described in this section uses two-pass formatting and the Page property to reset the 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 number for each group.

To reset the total-pages numbering scheme for each group in a report:  Open the sample database, Northwind.mdb.  Create a table that has the following structure, and then save the table as &quot;Category Group Pages&quot;.   Table: Category Group Pages Field Name: Country Data Type: Text Field Size: 15 Indexed: Yes (No Duplicates)

Field Name: Page Number Date Type: Number Field Size: Long Integer

Table Properties: Table1 PrimaryKey: Country  Open the Employee Sales By Country report in Design view.  On the View menu, click Code, and then type or paste the following lines in the Declarations section: Dim DB As Database Dim GrpPages As RecordSet   Type or paste the following function: Function GetGrpPages

' Find the group name.

GrpPages.Seek &quot;=&quot;, Me![Country]

If Not GrpPages.NoMatch Then

GetGrpPages = GrpPages![Page Number]

End If

End Function   Type the following in the report's OnOpen event procedure: Private Sub Report_Open (Cancel As Integer)

Set DB = dbengine.workspaces(0).databases(0)

DoCmd.SetWarnings False

DoCmd.RunSQL &quot;Delete * From [Category Group Pages];&quot;

DoCmd.SetWarnings True

Set GrpPages = DB.OpenRecordset(&quot;Category Group Pages&quot;, DB_Open_Table)

GrpPages.Index = &quot;PrimaryKey&quot;

End Sub </li>  Type the following in the page footer section's OnFormat event procedure: Private Sub PageFooter_Format (Cancel As Integer, FormatCount As Integer)

' Find the group.

GrpPages.Seek &quot;=&quot;, Me![Country]

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![Country] = Me![Country]

GrpPages![Page Number] = Me.Page

GrpPages.Update

End If

End Sub </li>  In the page footer section, add the following two text box controls: <pre class="fixed_text">  Text box:

Name: GroupXY

ControlSource: =GetGrpPages

Visible: No

Text box:

Name: ReferToPages

ControlSource: =Pages

Visible: No Note that the ReferToPages text box is necessary because it forces the report to use two-pass formatting when it is printed.

</li>  Change the control source of the PageNumber text box in the page footer to the following: =[Country] & &quot; - Page &quot; & [Page] & &quot; of &quot; & [GroupXY] </li> Preview the report.</li></ol>

Note that the page footer displays the current page and the total pages for each group.

<div class="references_section">