Reset Page of Pages in Multipage reports

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
--
Glen
Simple Scientist aka Microbiologist
Brisbane
Australia

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

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




--
Glen
Simple Scientist aka Microbiologist
Brisbane
Australia


"Rob Parker" wrote:

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

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

Rob - Getting closer - but not there quite yet !

I have the field BREF, which is the Report Number in the header - reports
are sorted by this field - but there are no GROUP headers or Footers or any
sorting or grouping on this report.

I have a text box in header (no to visible) with control =[Pages]

I have a Test box in header Name = TotalPages with blank control

my code is below

But all I am getting on each report in the TotalPages field is " Pages of"
no numbers, so I can only quess there is something wrong with my logic in the
code....

What do you think ?

'************ 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
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

--
Glen IN BRISBANE
Simple Scientist aka Microbiologist
Brisbane
Australia


"Rob Parker" wrote:

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

Hi Glen,

I'm not sure exactly what your problem is. The fact that you're getting the
"Pages of " text means that the code is running, but I'm at a loss as to why
the arrays GrpArrayPage and GrpArrayPages have no values - they should have.

There is nothing obviously amiss with the code. And there is no requirement
for there to be any grouping in the report - it will simply treat the field
you give it as the field on which to group. I'm assuming that you've got
the Force New page setting of your Detail section set to "After Section" to
ensure that each record will appear start on a new page.

If you'd like me to take a closer look at this, you can send a
(stripped-down) copy of your database (just the table/query required for the
report, and the report) to me at RME
(after removing the obvious - I don't want my email address posted to a
newsgroup). If you're using Access 2007, please convert to an earlier
version before sending.

Rob


Glen in Australia wrote:
Rob - Getting closer - but not there quite yet !

I have the field BREF, which is the Report Number in the header -
reports are sorted by this field - but there are no GROUP headers or
Footers or any sorting or grouping on this report.

I have a text box in header (no to visible) with control =[Pages]

I have a Test box in header Name = TotalPages with blank control

my code is below

But all I am getting on each report in the TotalPages field is "
Pages of" no numbers, so I can only quess there is something wrong
with my logic in the code....

What do you think ?

'************ 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
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,

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

Follow-up: in a private email message to me, Glen advised that the problem
was a data-related issue in the report causing the report to crash before
completion. The solution and notes on its implementation I gave in this
thread work as expected.

Rob

Rob Parker wrote:
Hi Glen,

I'm not sure exactly what your problem is. The fact that you're
getting the "Pages of " text means that the code is running, but I'm
at a loss as to why the arrays GrpArrayPage and GrpArrayPages have no
values - they should have.
There is nothing obviously amiss with the code. And there is no
requirement for there to be any grouping in the report - it will
simply treat the field you give it as the field on which to group. I'm
assuming that you've got the Force New page setting of your
Detail section set to "After Section" to ensure that each record will
appear start on a new page.
If you'd like me to take a closer look at this, you can send a
(stripped-down) copy of your database (just the table/query required
for the report, and the report) to me at
RME (after removing the obvious - I
don't want my email address posted to a newsgroup). If you're using
Access 2007, please convert to an earlier version before sending.

Rob


Glen in Australia wrote:
Rob - Getting closer - but not there quite yet !

I have the field BREF, which is the Report Number in the header -
reports are sorted by this field - but there are no GROUP headers or
Footers or any sorting or grouping on this report.

I have a text box in header (no to visible) with control =[Pages]

I have a Test box in header Name = TotalPages with blank control

my code is below

But all I am getting on each report in the TotalPages field is "
Pages of" no numbers, so I can only quess there is something wrong
with my logic in the code....

What do you think ?

'************ 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
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,

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