Hi Glen,
Sorry for the delay in responding - I didn't return to the
newsgroups later yesterday, and I've not actually used that code
before, so I've just done a bit of testing this morning (I'm also in
Oz), and it took a little while to get it working. At first I
thought it may have been because you are implementing this in the
page header, but that's not the problem.
The first obvious problem is with your declarations: you have moved
the first three Dim statements to within the subroutine code; they
must be above any code, and below the Option Compare Database and
Option Explicit lines - exactly as shown in the listing on the web
page. If you have any existing Option statements and/or code, make
sure that there is only one set of Option statements (at the top of
your code module - the only things which can appear above these
lines are comments) and that the Dim statements for the Grp...
entries are above any other subroutines or functions. So, assuming
there is no other code (or that any other code is below this), you
code needs to start as follows:
'************ 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 PageHeader_Format(Cancel As Integer, FormatCount As
Integer) Dim i As Integer
....Next, your page header section in the report must be named the
same as the name in the Sub declaration. I notice that, by default,
mine (in Access 2003) are named PageHeaderSection. You can either
change the name of the header section in the report, or change the
name of the header section in the Sub declaration - ie. make it
Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount
As Integer). The important thing is that they must match.
And last, but certainly not least - you need a control in your
report which is bound to Pages, to force the code to run two passes
(as discussed in the article); the easiest way is to put a textbox
in your page header, set its control source to =[Pages], and set its
Visible property to No.
And that should do it ...
Rob
Glen in Australia wrote:
Rob thanks for the web Page I have implemented it as a header but
it is still not working - in fact the control (I called TotalPages)
does not appear, yes it is set to Visible.. my code is
Private Sub PageHeader_Format(Cancel As Integer, FormatCount As
Integer)
'************ 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
'
Dim GrpArrayPage(), GrpArrayPages()
Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant
Dim GrpPage As Integer, GrpPages 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!BREF
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!TotalPages = "Page " & GrpArrayPage(Me.Page) & "of " &
GrpArrayPages(Me.Page)
End If
GrpNamePrevious = GrpNameCurrent
'************ Code End *************
End Sub
Hi Glen,
This should help:
http://www.mvps.org/access/reports/rpt0013.htm
Rob
Glen in Australia wrote:
When we produce laboratory reports there can be 100+ pages that
is the [Pages] value is preset.
Of these reports some are multiple page reports whereas most are
1 page only. I need each report to say in footer Page X of Page N
Where for one page report this is Page 1 of 1
And for multipage reports -> Page 1 of 3, Page 2 of 3, Page 3 of
3 I can reset [Page] by using a macro to [Page] Value to Expression
1 and evoking this macro in the onformat property of the group
header and ForceNewPage property to After Section of the group
footer
But I can't reset [Pages] as it is a read only property !
How can I get around this problem
Many thanks
Glen
