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

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.

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 "Page of Pages" format.

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

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 "1" 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 "Employee sales by country" 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.

1. Start Access.
2. Open the Northwind.mdb sample database.
3. 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
   

4. In the Database window, click Reports under Objects.
5. In the right pane, right-click Employee Sales By Country, and then click Design View.
6. Set the ForceNewPage property of the Country Footer section to After Section. To do this, follow these steps:
   1. In Design view, right-click Country Footer, and then click Properties.
   2. In the Section: GroupFooter1 dialog box, click the Format tab.
   3. In the Force New Page box, click After Section, and then close the Section: GroupFooter1 dialog box.
7. Make sure that the event procedure for the OnFormat event of the Country Header section sets the page number to "1" when a new group starts. To do this, follow these steps:
   1. On the View menu, click Code to open the Visual Basic Editor.
   2. In the Object box, click GroupHeader0.
   3. In the Procedure box, click Format.
   4. 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

8. 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 "=", Me![Country]
      If Not GrpPages.NoMatch Then
         GetGrpPages = GrpPages![Page Number]
      End If
   End Function

9. 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 "Delete * From [Category Group Pages];"
      DoCmd.SetWarnings True
      Set GrpPages = DB.OpenRecordset("Category Group Pages", DB_Open_Table)
      GrpPages.Index = "PrimaryKey"
   End Sub
   

10. 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 "=", 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
    

11. Close the Visual Basic Editor.
12. In Design view, add two text box controls with the following properties under the Page Footer section:

       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.
13. In Design view, click PageNumber in the Object drop-down list on the Formatting toolbar.
14. On the View menu, click Properties.
15. In the Textbox: PageNumber dialog box, click the Data tab. Put the following code in the Control Source box:

    =[Country] & " -  Page " & [Page] & " of " & [GroupXY]

16. On the View menu, click Layout Preview to preview the report.
    
    Note You must enter the correct date parameter values to preview the report.

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

For additional information about how to reset page numbers for each group in a report, visit the following Microsoft Web site: For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base: