Knowledge Base

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

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

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

Preparing the Report

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

Grouping the "Pages"

The grouping of "Page" 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 "Category Group Pages".

   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 "=", 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 "Delete * From [Category Group Pages];"

          DoCmd.SetWarnings True

          Set GrpPages = DB.OpenRecordset("Category Group Pages", DB_Open_Table)

          GrpPages.Index = "PrimaryKey"

      End Sub

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

In the page footer section, add the following two text box controls:

   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.

Change the control source of the PageNumber text box in the page footer to the following:

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

Preview the report.

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

REFERENCES

For additional information about grouping of pages, click the article numbers below to view the articles in the Microsoft Knowledge Base:

104760 How to Reset the Page Number on Group Level in a Report

131937 How to Reset Page of Pages Numbering for Report Groups

Additional query words: inf

Keywords: kbhowto KB306127

Microsoft KB Archive/306127

Contents