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

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.