Microsoft KB Archive/841779

= How to reset the page number and the total page count for each group in a Microsoft Access report =

Article ID: 841779

Article Last Modified on 6/3/2004

-

APPLIES TO


 * Microsoft Office Access 2003
 * Microsoft Access 2002 Standard Edition
 * Microsoft Access 2000 Standard Edition

-



This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

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



For a Microsoft Access 97 version of this article, see 306127.



INTRODUCTION
This article describes how to reset the page number and the total page count for each group in a report that is created in Microsoft Access. You can use this method to reset the page number and the total page count for each group in a report when the page number appears in the Page Footer section of the report.

Note You must only use this method with a single-user database. If you try to use this method in a shared database, you may receive inconsistent results.



MORE INFORMATION
By default, when you generate a report in Access, the Page property and the Pages property are set based on the current total number of records in the report at the time that you generate the report. The page number and the total page count are not automatically reset for each new entry in a group for the report.

You can reset the page number and the total page count based on a group either by using a macro or by using code that is in the Section property of the report. You can then use the page number and the total page count that have been reset to display the page numbers in the &quot;Page of Pages&quot; format.

For example, if the records for the first group continue for two pages, you can display the page numbers as &quot;1 of 2&quot; and &quot;2 of 2&quot;. If the records for the second group continue for three pages, you can display the page numbers as &quot;1 of 3&quot;, &quot;2 of 3&quot;, and &quot;3 of 3&quot;.

Prepare the reports and group the pages
To group the pages, a two-pass formatting is used, along with 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 the value &quot;1&quot; and then writes the total number of pages in the group to a table. The second formatting pass retrieves the total number of pages for each group. These values are then used to display the page number in the Page Footer section of the report. To prepare the reports and group the pages, follow these steps.

Note The following steps use the &quot;Employee sales by country&quot; report in the Northwind.mdb sample database to demonstrate how to prepare the report and how to group the pages in the report.

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.  Start Access. Open the Northwind.mdb sample database.  Create a table with the following properties:   Table: -  Table Name: 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

PrimaryKey: Country  In the Database window, click Reports under Objects. In the right pane, right-click Employee Sales By Country, and then click Design View. Set the ForceNewPage property of the Country Footer section to After Section. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> In Design view, right-click Country Footer, and then click Properties.</li> In the Section: GroupFooter1 dialog box, click the Format tab.</li> In the Force New Page box, click After Section, and then close the Section: GroupFooter1 dialog box.</li></ol> </li> Make sure that the event procedure for the OnFormat event of the Country Header section sets the page number to &quot;1&quot; when a new group starts. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> On the View menu, click Code to open the Visual Basic Editor.</li> In the Object box, click GroupHeader0.</li> In the Procedure box, click Format.</li>  Make sure that the following code appears in the code window: Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer) 'Set page number to 1 when a new group starts. Page = 1 End Sub </li></ol> </li>  Define a variable for a Database object, a variable for a RecordSet object, and a function that retrieves the total number of pages in a group. To do this in the Visual Basic Editor, append the following code.

Note The Visual Basic Editor may already contain some code. Do not remove any existing code. Append the following code to any existing code: Dim DB As Database Dim GrpPages As RecordSet 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 </li>  Create an event procedure for the OnOpen event of the report. To do this, append the following code: 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>  Create an event procedure for the OnFormat event of the Page Footer section of the report. To do this, append the following code: 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 'This is the first page of the group. Therefore, add it. GrpPages.AddNew GrpPages![Country] = Me![Country] GrpPages![Page Number] = Me.Page GrpPages.Update End If End Sub </li> Close the Visual Basic Editor.</li>  In Design view, add two text box controls with the following properties under the Page Footer section: <pre class="fixed_text">  Text box: -  Name: GroupXY ControlSource: =GetGrpPages Visible: No

Text box: --  Name: ReferToPages ControlSource: =Pages Visible: No Note The ReferToPages text box forces the report to use the two-pass formatting when the report is printed. </li> In Design view, click PageNumber in the Object drop-down list on the Formatting toolbar.</li> On the View menu, click Properties.</li> <li> In the Textbox: PageNumber dialog box, click the Data tab. Put the following code in the Control Source box: =[Country] & &quot; - Page &quot; & [Page] & &quot; of &quot; & [GroupXY] </li> <li>On the View menu, click Layout Preview to preview the report.

Note You must enter the correct date parameter values to preview the report.</li></ol>

The Page Footer displays the current page and the total pages for each group in the &quot;Page of Pages&quot; format.

<div class="references_section">