Hi there
This is Wade Jackson with Microsoft Access Developer Support.
There is actually an article written that describes what you are trying to
do. Unfortunately it is designed for Access 2.0, which uses Access Basic
rather than VBA. I've got a modified version of that article below, which
should work in VBA starting with Access 97. Hope this helps!
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
To accomplish this goal, we will need to group both the report's "Page" and
"Pages" to get the "Page of Pages" grouping.
Grouping the "Page"
To demonstrate the grouping of "Page" capability in Microsoft Access 97, we
will use the Employee Sales By Country report in the sample database
Northwind.mdb.
By using a macro, or some code and 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, in Microsoft Access 97, the Employee
Sales By Country report in the sample database Northwind.mdb is designed
with
this feature.
If Page Number Is Displayed in Page Footer:
1. Open the sample database Northwind.mdb.
2. Open the Employee Sales By Country report in Design view.
3. Select the Country Header section, and then click the Build button for
the
OnFormat property to examine the event procedure code.
4. Select the Country footer section and set the ForceNewPage property to
After
Section.
For additional information on grouping the "Page," please see article:
Q104760
ACC: How to Reset the Page Number on Group Level in a Report at:
<
http://support.microsoft.com/support/kb/articles/q104/7/60.asp>
Grouping the "Pages"
The grouping of "Pages" described here 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
total number and for each group.
To reset the total pages numbering scheme for each group in a
report, follow these steps:
1. Open the sample database Northwind.mdb used in the "Page" section above.
2. Create a table with the following structure and name it Category Group
Pages:
Table: Category Group Pages
-------------------------------
Field Name: Country
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: Country
3. Open the Employee Sales By Country report in Design view.
4. From the View menu, choose Code to create a module, and then type the
following lines in the Declarations section:
Dim DB As Database
Dim GrpPages As RecordSet
5. Type 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. Set the report's OnOpen property to the following 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. Set the page footer section's OnFormat property to the following 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 two text box controls as follows:
Text box:
Name: GroupXY
ControlSource: =GetGrpPages()
Visible: No
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.
9. Change the Page Footer, PageNumber Text Box, Control Source Property to:
=[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.
For additional information on the grouping of "Pages" please see the Access
2.0
article:Q131937 ACC2: How to Reset "Page of Pages" Numbering for Report
Groups
at:
<
http://support.microsoft.com/support/kb/articles/q131/9/37.asp>
This article was modified above for use with Access 97.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Hope this helps!
Sincerely,
Wade Jackson
Microsoft Online Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! -
www.microsoft.com/security