ACC97: How to Reset the Page Number and Total Page Count for Each Section of a Report (306127)

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

1. Open the sample database, Northwind.mdb.
2. Open the Employee Sales By Country report in Design view.
3. Click the Country Header section, and then click the Build button of the OnFormat property. Review the code for the OnFormat event.
4. 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:

1. Open the sample database, Northwind.mdb.
2. 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
   					

3. Open the Employee Sales By Country report in Design view.
4. 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

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

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

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

8. 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.
   
   
9. Change the control source of the PageNumber text box in the page footer to the following:

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

10. Preview the report.

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