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

ID: Q131937


The information in this article applies to:


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

Q104760 ACC: Sample Macro to Reset Page Number on Group Level Report

Additional query words: page number numbering


Keywords          : kbusage RptPagen 
Version           : 2.0
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: April 23, 1999