I picked this up from one of the MS websites. It is quite
a bit to digest. I have not tested this.
Often times it is necessary to print page numbers for
groups of records, and not just the overall pages for a
report. Access has no built in way to do this, and some
workarounds can be very cumbersome. Here's a simple code
sample that does the job well, using arrays to handle the
group page numbers; an often overlooked method of handling
multidimensional data. Open any report in design view and
open its Code-Behind-Reports module. Enter the code as
listed.Note: the line Me!Salesperson should be changed to
the control name of the group you wish to track pages for.
Me!ctlGrpPages is the name of a control you should place in
the page footer. You can also change this line to whatever
form you wish your page numbers to take.The code works by
filling two arrays with page numbers. GrpArrayPage() holds
the group page number, and GrpArrayPages() hold the total
number of pages for the group.When Access formats a report,
it often times must make two passes through the report for
information such as Page 1 of 10; since Access cannot know
how many pages the report will contain until the entire
report is formatted. You can use the fact that access makes
two passes to create your group page numbers. Because the
code listed uses the Pages Property (Total number of
pages), you automatically force access to format the report
twice
ReDim Preserve GrpArrayPage(Me.Page + 1
ReDim Preserve GrpArrayPages(Me.Page + 1)
ince arrays take up memory, and we have no way of knowing
how many pages will be in the report, we can dynamically
resize the arrays with each pass. Using the Preserve
keyword allows you to resize the array without losing any data
if Me.Pages = 0 Then
...
Else
Me!ctlGrpPages = "Group Page " &GrpArrayPage(Me.Page) & "
of " & GrpArrayPages(Me.Page)
End If
How the code works:Until access has finished formatting the
report for the first pass, the value of the Pages property
will be 0; so you can check this value to determine if this
is the first or second pass. The first pass is explained
below, and the second pass just sets the control in the
page footer to the value in the arrays.On the first pass
through the formatting of the report, the code uses the
group name and page property to build the group page
numbers. Using the current page number as the index number
for the array, makes the code a bit simpler.
GrpArrayPage(Me.Page) = GrpArrayPage(Me.Page - 1) + 1
This code sets the value of GrpArrayPage(me.page) to the
value of the previos page,
GrpArrayPage(Me.Page - 1), then adds one to that to get the
next page number.
GrpPages = GrpArrayPage(Me.Page)
For i = Me.Page - ((GrpPages) - 1) To Me.Page
GrpArrayPages(i) = GrpPages
Next I
GrpPages, a variable used to temporarily store the page
number is then set to the value of the current page. Then
the loop sets the value of GrpArrayPages to the value of
GrpPages for all of the Array items. To look at this a bit
simpler; assume we are at page three of the group, the
values would be:
GrpArrayPage( ) GrpArrayPages( )
1 3
2 3
3 3
When you move to page four you get
GrpArrayPage( ) GrpArrayPages( )
1 4
2 4
3 4
4 4
and so on.
If the code detects that the group name has changed "
GrpNameCurrent <> GrpNamePrevious" then it resets the group
page values to 1.Arrays are often overlooked when
developing code and people often create temporary or
permanent tables to handle temporary data. Not only does
this make your code more diffiuclt to manage and debug, it
makes it less portable. Arrays can often substitute as a
very efficient replacement for these temporary tables
Complete Code Listing:
************ Code Start ************
' This code was originally written by James H Brooks.' It
is not to be altered or distributed,' except as part of an
application.' You are free to use it in any application,'
provided the copyright notice is left unchanged.'' Code
Courtesy of' James H Brooks
Option Compare Database
Option Explicit
Dim GrpArrayPage(), GrpArrayPages()
Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant
Dim GrpPage As Integer, GrpPages As Integer
Private Sub PageFooter_Format(Cancel As Integer,
FormatCount As Integer)
Dim i As Integer
If Me.Pages = 0 Then
ReDim Preserve GrpArrayPage(Me.Page + 1)
ReDim Preserve GrpArrayPages(Me.Page + 1)
GrpNameCurrent = Me!Salesperson
If GrpNameCurrent = GrpNamePrevious Then
GrpArrayPage(Me.Page) = GrpArrayPage(Me.Page - 1) + 1
GrpPages = GrpArrayPage(Me.Page)
For i = Me.Page - ((GrpPages) - 1) To Me.Page
GrpArrayPages(i) = GrpPages
Next i
Else
GrpPage = 1
GrpArrayPage(Me.Page) = GrpPage
GrpArrayPages(Me.Page) = GrpPage
End If
Else
Me!ctlGrpPages = "Group Page " & GrpArrayPage(Me.Page) & "
of " & GrpArrayPages(Me.Page)
End If
GrpNamePrevious = GrpNameCurrent
End Sub
'************ Code End **************** Note: the line
Me!Salesperson should be changed to the control name of the
group you wish to track pages for. Me!ctlGrpPages is the
name of a control you should place in the page footer. You
can also change this line to whatever form you wish your
page numbers to take.
Jim
-----Original Message-----
Hi there,
i have a report grouped by Consignee. The consignee
header property `Force new page` is set to before 'before
section' such that each new consignee gets their own page.
I want the page numbering to reset for each Consignee. how
can i do this?
