REPOST: Maintaining Page Numbers with multiple reports.

Having been growled at for being unaware of protocol I have taken
advice from an MVP before sending this. If this offends protocol I
apologise in advance.

This is a repost of a thread with subject:

Maintaining Page Numbers with multiple reports

The problem is to have number continuity with a series of reports and
at the same time prepare a contents list.

I have the first of five reports printing as planned. The problem is
that the second report is numbered as expected but the page number
does not increment and the tblContents is not populated. I have copied
the post from the previous thread under my sig. The VBA follows:


Option Compare Database
Option Explicit
Dim intLastPage As Integer
' =========== This is the Second Report Sponsor Listing next is
PostCode

Private Sub Report_Open(Cancel As Integer)
' On the FIRST report that is run
' re-set the table field to Zero.

' CurrentDb.Execute "Update tblPage Set tblPage.intPageNumber =
0;", dbFailOnError

' On ALL the reports, including the first one

DoCmd.Maximize
intLastPage = DLookup("intPageNumber", "tblPage")

End Sub
' ==============

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

[Page] = [Page] + intLastPage

End Sub
' =============

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

' I've combined your Report Footer Format and Print events here.
' Your previous intPageNumber is not needed here.
' We'll use [Page] instead.

CurrentDb.Execute "Update tblPage Set tblPage.intPageNumber = " &
[Page] & ";", dbFailOnError

CurrentDb.Execute "Update tblcontents Set tblContents.PostCode = " &
[Page] + 1 & ";", dbFailOnError

End Sub
' ================

Thanks,

Robin Chapple

====Previous Thread Follows=====

On Sun, 31 Jul 2005 20:31:49 +1000, Robin Chapple wrote:

Robin,
See my comments marked with *** in line below.

*** snipped ***

I have returned to this project following some domestic trauma.

The first report has 30 pages. It correctly sets intLastPage to 30 and
puts 31 into the contents table for the first page number of the next
report.

I have now added the code to the second report which has just three
pages.

The first page in page number 31 as expected,
The second page is page 62 and
The third page is page 93

which looks like the second report second page number is second report
first page plus the "intLastPage" and so on.

Here is the VBA:

Option Compare Database
Option Explicit
Dim intPageCount As Integer

*** You have combined 2 different posts from 2 different threads in 2
different news groups.
intPageCount is not needed as it serves the same purpose as
intLastPage. See *** below

Dim intLastPage As Integer

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

[Page] = [Page] + intLastPage

*** This is not the correct event for this.
It must go in the Report Header Format event, not the Page Header.
You want to increment just the first page of each report by 30, not
each page by 30. The report itself takes care of incrementing the
second, third, etc. pages.
The Page Header event is run on each page, therefore you were getting
30 added to each page.
The code in the Report Header is run just once per report.
The first page of the second report is 31 (1+30), the next page is 32
(31 + 1), the third page is 33 (32 + 1), etc., which is what you want.

End Sub
Private Sub Report_Open(Cancel As Integer)

DoCmd.Maximize
intLastPage = DLookup("intPageNumber", "tblPage")

*** This is OK.
End Sub

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

intPageCount = [Page]
CurrentDb.Execute "Update tblcontents Set tblContents.District
= " & [intPageCount] + 1 & ";", dbFailOnError

*** This is OK, but you can combine this code with the code in the
Report Footer Print event.
End Sub

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

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

*** This is OK, but you can include the above Format event code with
this.
End Sub

===============

Thanks,

Robin Chapple

Here is what the entire code will look like.

I've changed the DoCmd.RunSQL to CurrentDb.Execute statements.
Using the Execute statement instead of RunSQL there is no need for
SetWarnings False and SetWarnings True.

Watch out for improper e-mail line wrapping on the longer lines.

Option Compare Database
Option Explicit
Dim intLastPage As Integer
===========

Private Sub Report_Open(Cancel As Integer)
' On the FIRST report that is run
' re-set the table field to Zero.

CurrentDb.Execute "Update tblPage Set tblPage.intPageNumber = 0;",
dbFailOnError

' On ALL the reports, including the first one

DoCmd.Maximize
intLastPage = DLookup("intPageNumber", "tblPage")
End Sub
==============

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As
Integer)
[Page] = [Page] + intLastPage
End Sub
=============

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

' I've combined your Report Footer Format and Print events here.
' Your previous intPageNumber is not needed here.
' We'll use [Page] instead.

CurrentDb.Execute. "Update tblPage Set tblPage.intPageNumber = " &
[Page] & ";", dbFailOnError

CurrentDb.Execute "Update tblcontents Set tblContents.District
= " & [Page] + 1 & ";", dbFailOnError

End Sub
================

I hope this explains why you were getting such large page numbers, and
gets you on-track again.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

Having been growled at for being unaware of protocol I have taken

And there was an apology. Get over it :~)

that the second report is numbered as expected but the page
does not increment and the tblContents is not populated. I

You are using the print event of the REPORT footer. You want
the PAGE footer if you are going to update something every
page, and the REPORT footer for something you update at the
end of the report.

Use the PAGE footer if you are adding 1 on each page.
Don't use the PAGE footer if you are using the [page] value
and letting it auto-increment.

Also, you need to check the PrintCount/FormatCount to make
sure you don't add multiple times. Sometimes sections get
formatted multiple times before they are printed, or printed
multiple times.

(davd)



"Robin Chapple" wrote in message
...
Having been growled at for being unaware of protocol I have taken
advice from an MVP before sending this. If this offends protocol I
apologise in advance.

This is a repost of a thread with subject:

Maintaining Page Numbers with multiple reports

The problem is to have number continuity with a series of reports and
at the same time prepare a contents list.

I have the first of five reports printing as planned. The problem is
that the second report is numbered as expected but the page number
does not increment and the tblContents is not populated. I have copied
the post from the previous thread under my sig. The VBA follows:


Option Compare Database
Option Explicit
Dim intLastPage As Integer
' =========== This is the Second Report Sponsor Listing next is
PostCode

Private Sub Report_Open(Cancel As Integer)
' On the FIRST report that is run
' re-set the table field to Zero.

' CurrentDb.Execute "Update tblPage Set tblPage.intPageNumber =
0;", dbFailOnError

' On ALL the reports, including the first one

DoCmd.Maximize
intLastPage = DLookup("intPageNumber", "tblPage")

End Sub
' ==============

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

[Page] = [Page] + intLastPage

End Sub
' =============

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

' I've combined your Report Footer Format and Print events here.
' Your previous intPageNumber is not needed here.
' We'll use [Page] instead.

CurrentDb.Execute "Update tblPage Set tblPage.intPageNumber = " &
[Page] & ";", dbFailOnError

CurrentDb.Execute "Update tblcontents Set tblContents.PostCode = " &
[Page] + 1 & ";", dbFailOnError

End Sub
' ================

Thanks,

Robin Chapple

====Previous Thread Follows=====

On Sun, 31 Jul 2005 20:31:49 +1000, Robin Chapple wrote:

Robin,
See my comments marked with *** in line below.

*** snipped ***

I have returned to this project following some domestic trauma.

The first report has 30 pages. It correctly sets intLastPage to 30 and
puts 31 into the contents table for the first page number of the next
report.

I have now added the code to the second report which has just three
pages.

The first page in page number 31 as expected,
The second page is page 62 and
The third page is page 93

which looks like the second report second page number is second report
first page plus the "intLastPage" and so on.

Here is the VBA:

Option Compare Database
Option Explicit
Dim intPageCount As Integer

*** You have combined 2 different posts from 2 different threads in 2
different news groups.
intPageCount is not needed as it serves the same purpose as
intLastPage. See *** below

Dim intLastPage As Integer

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

[Page] = [Page] + intLastPage

*** This is not the correct event for this.
It must go in the Report Header Format event, not the Page Header.
You want to increment just the first page of each report by 30, not
each page by 30. The report itself takes care of incrementing the
second, third, etc. pages.
The Page Header event is run on each page, therefore you were getting
30 added to each page.
The code in the Report Header is run just once per report.
The first page of the second report is 31 (1+30), the next page is 32
(31 + 1), the third page is 33 (32 + 1), etc., which is what you want.

End Sub
Private Sub Report_Open(Cancel As Integer)

DoCmd.Maximize
intLastPage = DLookup("intPageNumber", "tblPage")

*** This is OK.
End Sub

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

intPageCount = [Page]
CurrentDb.Execute "Update tblcontents Set tblContents.District
= " & [intPageCount] + 1 & ";", dbFailOnError

*** This is OK, but you can combine this code with the code in the
Report Footer Print event.
End Sub

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

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

*** This is OK, but you can include the above Format event code with
this.
End Sub

===============

Thanks,

Robin Chapple

Here is what the entire code will look like.

I've changed the DoCmd.RunSQL to CurrentDb.Execute statements.
Using the Execute statement instead of RunSQL there is no need for
SetWarnings False and SetWarnings True.

Watch out for improper e-mail line wrapping on the longer lines.

Option Compare Database
Option Explicit
Dim intLastPage As Integer
===========

Private Sub Report_Open(Cancel As Integer)
' On the FIRST report that is run
' re-set the table field to Zero.

CurrentDb.Execute "Update tblPage Set tblPage.intPageNumber = 0;",
dbFailOnError

' On ALL the reports, including the first one

DoCmd.Maximize
intLastPage = DLookup("intPageNumber", "tblPage")
End Sub
==============

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As
Integer)
[Page] = [Page] + intLastPage
End Sub
=============

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

' I've combined your Report Footer Format and Print events here.
' Your previous intPageNumber is not needed here.
' We'll use [Page] instead.

CurrentDb.Execute. "Update tblPage Set tblPage.intPageNumber = " &
[Page] & ";", dbFailOnError

CurrentDb.Execute "Update tblcontents Set tblContents.District
= " & [Page] + 1 & ";", dbFailOnError

End Sub
================

I hope this explains why you were getting such large page numbers, and
gets you on-track again.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.