Knowledge Base

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 "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.

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:
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.
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
```
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
```

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

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

Close the Visual Basic Editor.

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.

In Design view, click PageNumber in the Object drop-down list on the Formatting toolbar.
On the View menu, click Properties.
In the Textbox: PageNumber dialog box, click the Data tab. Put the following code in the Control Source box:
```
=[Country] & " -  Page " & [Page] & " of " & [GroupXY]
```
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.

REFERENCES

For additional information about how to reset page numbers for each group in a report, visit the following Microsoft Web site:

http://office.microsoft.com/assistance/preview.aspx?AssetID=HP051874011033

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

209215 How to reset the page number on group level in a report

216311 How to create page totals on a report

Additional query words: acc2000 acc2002 acc2003

Keywords: kbhowto kbreport kbcode kbevent KB841779

Microsoft KB Archive/841779

Contents