Page numbering

Hi all

I have some code I found on the forum for creating Page numbering to run
sequentially over two different reports ie report 1 starts at page 1 and
report 2 starts at the end of report 1 + 1. This works when I code reports to
print, report 2 starts at Page 6 as desired. But when I code to preview the
reports only (how they normally work) the page numbering is not correct in
that report 2 starts at page 2 (instead of 6).

Do I need something in between the docmd.openreport preview commands calling
the two reports, maybe some sort of time delay to wait for report 1 to finish
processing? Or is it because I haven't moved through the pages when its
previewed so it hasn't triggered/looped through its formatting section fully?

Thanks for any help...

Code on each report is:

Dim intLastPage As Integer

Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As Integer)

DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblPage Set tblPage.intPagenumber=" & [Page] & ";"
DoCmd.SetWarnings True

End Sub

Private Sub Report_Open(Cancel As Integer)

' only on report 1 reset PageNo to start at 0
DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblPage Set tblPage.intPagenumber=0;"
DoCmd.SetWarnings True

intLastPage = DLookup("[intPageNumber]", "[tblPage]")

End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

[Page] = [Page] + intLastPage

End Sub

john
"hughess7" wrote in message
...
Hi all

I have some code I found on the forum for creating Page numbering to run
sequentially over two different reports ie report 1 starts at page 1 and
report 2 starts at the end of report 1 + 1. This works when I code reports
to
print, report 2 starts at Page 6 as desired. But when I code to preview
the
reports only (how they normally work) the page numbering is not correct in
that report 2 starts at page 2 (instead of 6).

Do I need something in between the docmd.openreport preview commands
calling
the two reports, maybe some sort of time delay to wait for report 1 to
finish
processing? Or is it because I haven't moved through the pages when its
previewed so it hasn't triggered/looped through its formatting section
fully?

Thanks for any help...

Code on each report is:

Dim intLastPage As Integer

Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As
Integer)

DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblPage Set tblPage.intPagenumber=" & [Page] & ";"
DoCmd.SetWarnings True

End Sub

Private Sub Report_Open(Cancel As Integer)

' only on report 1 reset PageNo to start at 0
DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblPage Set tblPage.intPagenumber=0;"
DoCmd.SetWarnings True

intLastPage = DLookup("[intPageNumber]", "[tblPage]")

End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

[Page] = [Page] + intLastPage

End Sub

john
"hughess7" wrote in message
...
Hi all

I have some code I found on the forum for creating Page numbering to run
sequentially over two different reports ie report 1 starts at page 1 and
report 2 starts at the end of report 1 + 1. This works when I code reports
to
print, report 2 starts at Page 6 as desired. But when I code to preview
the
reports only (how they normally work) the page numbering is not correct in
that report 2 starts at page 2 (instead of 6).

Do I need something in between the docmd.openreport preview commands
calling
the two reports, maybe some sort of time delay to wait for report 1 to
finish
processing? Or is it because I haven't moved through the pages when its
previewed so it hasn't triggered/looped through its formatting section
fully?

Thanks for any help...

Code on each report is:

Dim intLastPage As Integer

Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As
Integer)

DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblPage Set tblPage.intPagenumber=" & [Page] & ";"
DoCmd.SetWarnings True

End Sub

Private Sub Report_Open(Cancel As Integer)

' only on report 1 reset PageNo to start at 0
DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblPage Set tblPage.intPagenumber=0;"
DoCmd.SetWarnings True

intLastPage = DLookup("[intPageNumber]", "[tblPage]")

End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

[Page] = [Page] + intLastPage

End Sub

You should be able to do this by opening the second report from within the
first report's module, and referencing the Pages property of the first report
rather than its Page property. For this you'll need text box in the first
report's page footer which references the Pages property, e.g. with a
ControlSource =Pages, but this control can be hidden if necessary. The page
number does not need to be stored in a table.

Then in the Print event procedure of the first report's report header section
put:

If Me.OpenArgs = "Follow on" Then
DoCmd.OpenReport "Report2", _
View:=acViewPreview, _
OpenArgs:=(Me.Pages)
End If

and in the Format event procedure of the second report's report header
section put:

If Not IsNull(Me.OpenArgs) Then
Me.Page = Me.OpenArgs + 1
End If

The open the first report with:

DoCmd.OpenReport "Report1",View:=acViewPreview,OpenArgs:="Follo w on"

By passing 'Follow on' as the OpenArgs property to the first report this
ensures that it only opens the second report and passes the number of pages
to it if the first report has been opened in this way. Opening it without
passing the string would cause it to open on its own. If you want the second
report to follow on from the first every time the first report is opened then
you don't need to pass the string as the OpenArgs property of course; you can
simply make the code in the first report's header's Print event procedure
unconditional.

Ken Sheridan
Stafford, England

hughess7 wrote:
Hi all

I have some code I found on the forum for creating Page numbering to run
sequentially over two different reports ie report 1 starts at page 1 and
report 2 starts at the end of report 1 + 1. This works when I code reports to
print, report 2 starts at Page 6 as desired. But when I code to preview the
reports only (how they normally work) the page numbering is not correct in
that report 2 starts at page 2 (instead of 6).

Do I need something in between the docmd.openreport preview commands calling
the two reports, maybe some sort of time delay to wait for report 1 to finish
processing? Or is it because I haven't moved through the pages when its
previewed so it hasn't triggered/looped through its formatting section fully?

Thanks for any help...

Code on each report is:

Dim intLastPage As Integer

Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As Integer)

DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblPage Set tblPage.intPagenumber=" & [Page] & ";"
DoCmd.SetWarnings True

End Sub

Private Sub Report_Open(Cancel As Integer)

' only on report 1 reset PageNo to start at 0
DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblPage Set tblPage.intPagenumber=0;"
DoCmd.SetWarnings True

intLastPage = DLookup("[intPageNumber]", "[tblPage]")

End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

[Page] = [Page] + intLastPage

End Sub

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201005/1

Hi Ken

Excellent thanks!!!

It didn't work with the openarg conditions but I always want report 1 to
open anyway with report 2, so I removed the conditions and this works great
:-). I assume I can't use 'Page 1 of x' on the reports, or can I use another
variable and add the Pages together???

Sue

"KenSheridan via AccessMonster.com" u51882@uwe wrote in message
news:a87e4e9fd9616@uwe...
You should be able to do this by opening the second report from within the
first report's module, and referencing the Pages property of the first
report
rather than its Page property. For this you'll need text box in the first
report's page footer which references the Pages property, e.g. with a
ControlSource =Pages, but this control can be hidden if necessary. The
page
number does not need to be stored in a table.

Then in the Print event procedure of the first report's report header
section
put:

If Me.OpenArgs = "Follow on" Then
DoCmd.OpenReport "Report2", _
View:=acViewPreview, _
OpenArgs:=(Me.Pages)
End If

and in the Format event procedure of the second report's report header
section put:

If Not IsNull(Me.OpenArgs) Then
Me.Page = Me.OpenArgs + 1
End If

The open the first report with:

DoCmd.OpenReport "Report1",View:=acViewPreview,OpenArgs:="Follo w on"

By passing 'Follow on' as the OpenArgs property to the first report this
ensures that it only opens the second report and passes the number of
pages
to it if the first report has been opened in this way. Opening it without
passing the string would cause it to open on its own. If you want the
second
report to follow on from the first every time the first report is opened
then
you don't need to pass the string as the OpenArgs property of course; you
can
simply make the code in the first report's header's Print event procedure
unconditional.

Ken Sheridan
Stafford, England

hughess7 wrote:
Hi all

I have some code I found on the forum for creating Page numbering to run
sequentially over two different reports ie report 1 starts at page 1 and
report 2 starts at the end of report 1 + 1. This works when I code reports
to
print, report 2 starts at Page 6 as desired. But when I code to preview
the
reports only (how they normally work) the page numbering is not correct in
that report 2 starts at page 2 (instead of 6).

Do I need something in between the docmd.openreport preview commands
calling
the two reports, maybe some sort of time delay to wait for report 1 to
finish
processing? Or is it because I haven't moved through the pages when its
previewed so it hasn't triggered/looped through its formatting section
fully?

Thanks for any help...

Code on each report is:

Dim intLastPage As Integer

Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As
Integer)

DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblPage Set tblPage.intPagenumber=" & [Page] & ";"
DoCmd.SetWarnings True

End Sub

Private Sub Report_Open(Cancel As Integer)

' only on report 1 reset PageNo to start at 0
DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblPage Set tblPage.intPagenumber=0;"
DoCmd.SetWarnings True

intLastPage = DLookup("[intPageNumber]", "[tblPage]")

End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

[Page] = [Page] + intLastPage

End Sub

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201005/1

Got this to work now I think by creating text boxes to store the Pages from
each report and then adding these together, thanks.

"Sue" wrote in message
...
Hi Ken

Excellent thanks!!!

It didn't work with the openarg conditions but I always want report 1 to
open anyway with report 2, so I removed the conditions and this works
great :-). I assume I can't use 'Page 1 of x' on the reports, or can I use
another variable and add the Pages together???

Sue

"KenSheridan via AccessMonster.com" u51882@uwe wrote in message
news:a87e4e9fd9616@uwe...
You should be able to do this by opening the second report from within
the
first report's module, and referencing the Pages property of the first
report
rather than its Page property. For this you'll need text box in the
first
report's page footer which references the Pages property, e.g. with a
ControlSource =Pages, but this control can be hidden if necessary. The
page
number does not need to be stored in a table.

Then in the Print event procedure of the first report's report header
section
put:

If Me.OpenArgs = "Follow on" Then
DoCmd.OpenReport "Report2", _
View:=acViewPreview, _
OpenArgs:=(Me.Pages)
End If

and in the Format event procedure of the second report's report header
section put:

If Not IsNull(Me.OpenArgs) Then
Me.Page = Me.OpenArgs + 1
End If

The open the first report with:

DoCmd.OpenReport "Report1",View:=acViewPreview,OpenArgs:="Follo w on"

By passing 'Follow on' as the OpenArgs property to the first report this
ensures that it only opens the second report and passes the number of
pages
to it if the first report has been opened in this way. Opening it
without
passing the string would cause it to open on its own. If you want the
second
report to follow on from the first every time the first report is opened
then
you don't need to pass the string as the OpenArgs property of course; you
can
simply make the code in the first report's header's Print event procedure
unconditional.

Ken Sheridan
Stafford, England

hughess7 wrote:
Hi all

I have some code I found on the forum for creating Page numbering to run
sequentially over two different reports ie report 1 starts at page 1 and
report 2 starts at the end of report 1 + 1. This works when I code
reports to
print, report 2 starts at Page 6 as desired. But when I code to preview
the
reports only (how they normally work) the page numbering is not correct
in
that report 2 starts at page 2 (instead of 6).

Do I need something in between the docmd.openreport preview commands
calling
the two reports, maybe some sort of time delay to wait for report 1 to
finish
processing? Or is it because I haven't moved through the pages when its
previewed so it hasn't triggered/looped through its formatting section
fully?

Thanks for any help...

Code on each report is:

Dim intLastPage As Integer

Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As
Integer)

DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblPage Set tblPage.intPagenumber=" & [Page] & ";"
DoCmd.SetWarnings True

End Sub

Private Sub Report_Open(Cancel As Integer)

' only on report 1 reset PageNo to start at 0
DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblPage Set tblPage.intPagenumber=0;"
DoCmd.SetWarnings True

intLastPage = DLookup("[intPageNumber]", "[tblPage]")

End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As
Integer)

[Page] = [Page] + intLastPage

End Sub

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201005/1

Aha! Bummer... in the office today and tried printing these two reports
(rather than previewing at home) and unfortunately this method develops the
same problem as having them as the one report instead of split. I get the
error message 'cannot open any many databases', when you press ok you get
the message 'An error occurred when sending data to the OLE server. You may
have tried to send too much data etc etc'. Which was why I split them in the
first place...

If I remove the page numbering code I can open the reports individually and
print both, but of course with
no page numbering (or starting at page one again on 2nd report).

"KenSheridan via AccessMonster.com" u51882@uwe wrote in message
news:a87e4e9fd9616@uwe...
You should be able to do this by opening the second report from within the
first report's module, and referencing the Pages property of the first
report
rather than its Page property. For this you'll need text box in the first
report's page footer which references the Pages property, e.g. with a
ControlSource =Pages, but this control can be hidden if necessary. The
page
number does not need to be stored in a table.

Then in the Print event procedure of the first report's report header
section
put:

If Me.OpenArgs = "Follow on" Then
DoCmd.OpenReport "Report2", _
View:=acViewPreview, _
OpenArgs:=(Me.Pages)
End If

and in the Format event procedure of the second report's report header
section put:

If Not IsNull(Me.OpenArgs) Then
Me.Page = Me.OpenArgs + 1
End If

The open the first report with:

DoCmd.OpenReport "Report1",View:=acViewPreview,OpenArgs:="Follo w on"

By passing 'Follow on' as the OpenArgs property to the first report this
ensures that it only opens the second report and passes the number of
pages
to it if the first report has been opened in this way. Opening it without
passing the string would cause it to open on its own. If you want the
second
report to follow on from the first every time the first report is opened
then
you don't need to pass the string as the OpenArgs property of course; you
can
simply make the code in the first report's header's Print event procedure
unconditional.

Ken Sheridan
Stafford, England

hughess7 wrote:
Hi all

I have some code I found on the forum for creating Page numbering to run
sequentially over two different reports ie report 1 starts at page 1 and
report 2 starts at the end of report 1 + 1. This works when I code reports
to
print, report 2 starts at Page 6 as desired. But when I code to preview
the
reports only (how they normally work) the page numbering is not correct in
that report 2 starts at page 2 (instead of 6).

Do I need something in between the docmd.openreport preview commands
calling
the two reports, maybe some sort of time delay to wait for report 1 to
finish
processing? Or is it because I haven't moved through the pages when its
previewed so it hasn't triggered/looped through its formatting section
fully?

Thanks for any help...

Code on each report is:

Dim intLastPage As Integer

Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As
Integer)

DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblPage Set tblPage.intPagenumber=" & [Page] & ";"
DoCmd.SetWarnings True

End Sub

Private Sub Report_Open(Cancel As Integer)

' only on report 1 reset PageNo to start at 0
DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblPage Set tblPage.intPagenumber=0;"
DoCmd.SetWarnings True

intLastPage = DLookup("[intPageNumber]", "[tblPage]")

End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

[Page] = [Page] + intLastPage

End Sub

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201005/1

Perhaps you can go back to your original method, but instead of updating the
tblPage table with the Page property incrementally in the first report's page
footer's event, update it with the Pages property in its report header's
Print event procedure. You might still not get the correct starting number
for the second report, however, as it may well have been formatted before the
first report has been through its first complete iteration to establish the
value of the Pages property. If so all I can suggest is getting user
confirmation that the first report has opened before opening the second by
popping up a message box between them:

Const MESSAGETEXT = "Has first report opened successfully?"

DoCmd.OpenReport "Report1", View:=acViewPreview

If MsgBox(MESSAGETEXT, vbQuestion + vbYesNo, "Confirm") = vbYes Then
DoCmd.OpenReport "Report2", View:=acViewPreview
End If

Ken Sheridan
Stafford, England

Sue wrote:
Aha! Bummer... in the office today and tried printing these two reports
(rather than previewing at home) and unfortunately this method develops the
same problem as having them as the one report instead of split. I get the
error message 'cannot open any many databases', when you press ok you get
the message 'An error occurred when sending data to the OLE server. You may
have tried to send too much data etc etc'. Which was why I split them in the
first place...

If I remove the page numbering code I can open the reports individually and
print both, but of course with
no page numbering (or starting at page one again on 2nd report).

You should be able to do this by opening the second report from within the
first report's module, and referencing the Pages property of the first
[quoted text clipped - 92 lines]

End Sub

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201005/1

Thanks Ken, I have been playing with this all morning and finally got it to
produce correct page numbering for both reports (hopefully!). I found that
to get it to work I had to create white space in the report header, if I
closed it up then it didn't work. For reference what I ended up doing is:

- two separate buttons on reports menu for report1 and report2.
- before opening the correct report, in code I open the other report first
hidden from the user, update the pages value into the table tblPages and
close the report. eg If user selects Report1 to preview, report2 opens first
hidden, gets its pages value so that it can calculate the total pages from
both reports, and the same for opening Report2 also.

I had to do this as Report1 would not print if Report2 was open, even if it
was hidden.

Just need to fully test it now from scratch with another report with
different page numbers.

Thanks...

"KenSheridan via AccessMonster.com" u51882@uwe wrote in message
news:a8970155592ee@uwe...
Perhaps you can go back to your original method, but instead of updating
the
tblPage table with the Page property incrementally in the first report's
page
footer's event, update it with the Pages property in its report header's
Print event procedure. You might still not get the correct starting
number
for the second report, however, as it may well have been formatted before
the
first report has been through its first complete iteration to establish
the
value of the Pages property. If so all I can suggest is getting user
confirmation that the first report has opened before opening the second by
popping up a message box between them:

Const MESSAGETEXT = "Has first report opened successfully?"

DoCmd.OpenReport "Report1", View:=acViewPreview

If MsgBox(MESSAGETEXT, vbQuestion + vbYesNo, "Confirm") = vbYes Then
DoCmd.OpenReport "Report2", View:=acViewPreview
End If

Ken Sheridan
Stafford, England

Sue wrote:
Aha! Bummer... in the office today and tried printing these two reports
(rather than previewing at home) and unfortunately this method develops
the
same problem as having them as the one report instead of split. I get the
error message 'cannot open any many databases', when you press ok you get
the message 'An error occurred when sending data to the OLE server. You
may
have tried to send too much data etc etc'. Which was why I split them in
the
first place...

If I remove the page numbering code I can open the reports individually
and
print both, but of course with
no page numbering (or starting at page one again on 2nd report).

You should be able to do this by opening the second report from within
the
first report's module, and referencing the Pages property of the first
[quoted text clipped - 92 lines]

End Sub

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201005/1