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.
Back to the top
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.
|
Back to the top
For more information about resetting the page number per group, please see
the following article in the Microsoft Knowledge Base:
104760 (http://support.microsoft.com/kb/104760/EN-US/) ACC: Sample Macro to Reset Page Number on Group Level Report
Back to the top
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Help and Support
