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



The information in this article applies to:

  • Microsoft Access 97

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

  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.

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


Modification Type:MajorLast Reviewed:9/25/2003
Keywords:kbhowto KB306127