REPOST: Maintaining Page Numbers with multiple reports.

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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.



.

Relevant Pages

  • Re: Alternate lines gray
    ... Now Code the Page Header part of your report like this ... Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer) ... I usually have an option control on a form that has two ...
    (microsoft.public.access.reports)
  • Re: Hyperlink question
    ... like the report to show the "File/Directory not found" on top of the report, ... Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) ... If Len(Dir(HyperlinkPart(Me.Scanned_JE_Link, acAddress))) = 0 Then ...
    (microsoft.public.access.forms)
  • Re: Alternating color per line in detail section
    ... Now Code the Page Header part of your report like this ... Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer) ... I usually have an option control on a form that has two ...
    (microsoft.public.access.reports)
  • Re: Alternating color per line in detail section
    ... Now Code the Page Header part of your report like this ... Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer) ... I usually have an option control on a form that has two ...
    (microsoft.public.access.reports)
  • Re: Error: Microsoft jet database engine does not recognize " as a va
    ... If you are attempting to create a crosstab report with dynamic columns, ... > Dim rstReport As DAO.Recordset ... > Dim intX As Integer ... > Private Sub Detail_Format ...
    (microsoft.public.access.reports)