ACC2: How to Reset "Page of Pages" Numbering for Report Groups

• Microsoft Access version 2.0

SUMMARY

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

This article demonstrates how to reset a report's page-numbering scheme so that both the page number and the total page count start at 1 for each new group. 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."

CAUTION: Use the method described in this article only for a single-user database. If you use this method in a shared database, you may receive inconsistent results.

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

By default, when Microsoft Access generates a report, it formats and prints each record individually. Using the Pages property, you can force Microsoft Access to make an additional formatting pass of all the records in the report to determine the total number of pages required. The first formatting pass calculates the number of pages in the report; the second formatting pass uses the number of pages returned in the first pass to print the correct number of pages.

The method described in this article uses two-pass formatting and the Page property to reset the current page and 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 total number and prints it with the current page for each group.

To reset the current page and total pages numbering scheme for each group in a report, follow these steps:

  1. Open the sample database NWIND.MDB.

  2. Create a table with the following structure and name it Category Group
     Pages:

       Table: Category Group Pages
       -------------------------------
       Field Name: Category Name
          Data Type: Text
          Field Size: 15
          Indexed: Yes (No Duplicates)
       Field Name: Page Number
          Data Type: Number
          Field Size: Long Integer

       Table Properties: Category Group Pages
       --------------------------------------
       PrimaryKey: Category Name

  3. Open the List Of Products By Category report in Design view.

  4. Set the detail section's Height property to 1 inch.

     NOTE: This step ensures that some Category groups are printed on more
     than one page. If you use this method on another report, step 4 may
     not be necessary.

  5. Set the Category Name footer section's ForceNewPage property to After
     Section.

  6. From the View menu, choose Code to create a module, and then type the
     following lines in the Declarations section:

       Option Explicit
       Dim DB As Database
       Dim GrpPages As RecordSet

  7. From the Edit menu, choose New Procedure. In the New Procedure dialog
     box, type "GetGrpPages" (without the quotation marks) in the Name box,
     and then choose the OK button.

  8. Type the following function:

       Function GetGrpPages ()
          ' Return the group X of Y pages.
          ' Find the group name.
          GrpPages.Seek "=", Me![Category name]
          If Not GrpPages.NoMatch Then
             GetGrpPages = Me.page & "/" & GrpPages![Page Number]
          End If
       End Function

  9. Set the report's OnOpen property to the following event procedure:

     NOTE: In the following sample code, an underscore (_) at the end of a
     line is used as a line-continuation character. Remove the underscore
     from the end of the line when re-creating this code.

       Sub Report_Open (Cancel As Integer)
         Set DB = dbengine.workspaces(0).databases(0)
         DoCmd RunSQL "Delete * From [Category Group Pages];"
         Set GrpPages = DB.OpenRecordset("Category Group Pages", _
           DB_Open_Table)
         GrpPages.Index = "PrimaryKey"
       End Sub

 10. Set the Category Name header's OnFormat property to the following
     event procedure:

       Sub GroupHeader2_Format (Cancel As Integer, FormatCount As Integer)
         ' Reset the page number at the start of the group.
         Me.page = 1
       End Sub

 11. Set the page footer section's OnFormat property to the following
     event procedure:

       Sub PageFooter5_Format (Cancel As Integer, FormatCount As Integer)
         ' Find the group.
         GrpPages.Seek "=", Me![Category name]
         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![Category name] = Me![Category name]
           GrpPages![Page Number] = Me.page
           GrpPages.Update
         End If
       End Sub

 12. In the page footer section, add two text box controls as follows:

       Text box:
          Name: GroupXY
          ControlSource: =GetGrpPages()

       Text box:
          Name: ReferToPages
          ControlSource: =Pages
          Visible: No

     NOTE: The ReferToPages text box is necessary because it forces the
     report to use two-pass formatting when it is printed.

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

REFERENCES

For more information about resetting the page number per group, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q104760
   TITLE     : ACC: Sample Macro to Reset Page Number on Group Level Report