Multiple reports with consecutive numbering

[Mark G]

Is there a way to print out multiple reports and have consecutive numbering?
I want to be able to print three or four different reports and have the pages
numbered consecutively or maybe it is sequentially starting at page 1. I can
get the the reports to print in order but just can't get the page numbering
figured out.

Thanks
Mark

 

[Steve Schapel]

Mark,

I think you will need to use VBA to do this. The (untested!) skeleton
of the idea would be like this...

In a standard module, declare a global variable, maybe like this:
Public StartingPageNumber As Integer

On a suitable event, perhaps the Print event of the Report Footer
section of the first report, do code like this:
StartingPageNumber = StartingPageNumber + Me.Pages

On the Format event of the Page Footer section (assuming that's where
you are putting your page numbering) of the second report, put code like
this:
Me.PageNumberLabel.Caption = "Page " & ([Page]+StartingPageNumber)
(where PageNumberLabel is the name of a label control)

And then, extrapolate the same process to the 3rd and 4th reports.

 

[fredg]

Is there a way to print out multiple reports and have consecutive numbering?
I want to be able to print three or four different reports and have the pages
numbered consecutively or maybe it is sequentially starting at page 1. I can
get the the reports to print in order but just can't get the page numbering
figured out.

Thanks
Mark

Make a table to hold the last page number of each report.
Table name "tblPage"
All you need is one field:
"intPageNumber" Number datatype, Integer

Next, enter a 0 (Zero) into the field as a starter number.

Now in each report, Dim a variable in the declarations section:

Option Compare Database
Option Explicit
Dim intLastPage as Integer

Code each report's Open event:
intLastPage = DLookUp("[intPageNumber]","tblPage")

Code each Report's Report Header Format event:
[Page] = [Page] + intLastPage

Code each Report's Report Footer Print event:

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

Each report will pick up the ending page of the previous report and
increment it by 1.
Note: You'll not be able to use the [Pages] property in any of these
reports, i.e. ="Page " & [Page] & " of " & [Pages] as you'll get
something
like "Page 32 of 4".

You must enter a 0 in the table at the start of each batch of reports.
If there is always one same report which is run first in the batch,
just use a RunSQL in the first Report's Open event (before anything
else):

DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblPage Set tblPage.intPageNumber = 0;"
Docmd.SetWarnings True

to reset the field value to zero.

If the Reports are in run in random order, manually (or otherwise)
enter a 0 into that table field before starting.

 

[Mark G]

Thank you Fred, it worked perfectly. Just what I was looking for.

 

[hughess7]

Hi, I have tried this but its not working so I am not sure if I've missed
something...

I put the code in two reports [rpt part 1] and [rpt part 2]. I have a button
which previews both reports calling them one after the other with:
DoCmd.OpenReport stDocName, acPreview

Then the code in both reports as you suggested:

Option Explicit
Dim intLastPage As Integer
Private Sub Report_Open(Cancel As Integer)

'only in pt 1
DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblPage Set tblPage.intPagenumber= 0;"
DoCmd.SetWarnings True

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

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

End Sub

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

[Page] = [Page] + intLastPage

End Sub

In the Page footer I have [Page] - should this be different referring to the
variable somehow? Or is it because I have it in the Page footer and not the
report footer? The tblPage.intPagenumber is 0 and both reports start at Page
1 ???

Thanks for any help...

Is there a way to print out multiple reports and have consecutive numbering?
I want to be able to print three or four different reports and have the pages
numbered consecutively or maybe it is sequentially starting at page 1. I can
get the the reports to print in order but just can't get the page numbering
figured out.

Thanks
Mark

Make a table to hold the last page number of each report.
Table name "tblPage"
All you need is one field:
"intPageNumber" Number datatype, Integer

Next, enter a 0 (Zero) into the field as a starter number.

Now in each report, Dim a variable in the declarations section:

Option Compare Database
Option Explicit
Dim intLastPage as Integer

Code each report's Open event:
intLastPage = DLookUp("[intPageNumber]","tblPage")

Code each Report's Report Header Format event:
[Page] = [Page] + intLastPage

Code each Report's Report Footer Print event:

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

Each report will pick up the ending page of the previous report and
increment it by 1.
Note: You'll not be able to use the [Pages] property in any of these
reports, i.e. ="Page " & [Page] & " of " & [Pages] as you'll get
something
like "Page 32 of 4".

You must enter a 0 in the table at the start of each batch of reports.
If there is always one same report which is run first in the batch,
just use a RunSQL in the first Report's Open event (before anything
else):

DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblPage Set tblPage.intPageNumber = 0;"
Docmd.SetWarnings True

to reset the field value to zero.

If the Reports are in run in random order, manually (or otherwise)
enter a 0 into that table field before starting.

 

[hughess7]

Hi, after further investigation this does work if I move the code from the
report footer to the Page footer code. But it only works when I print, when I
preview it starts the 2nd report off at page 2 (I assume because its not
finished processing the first report maybe?). I tried putting a doevents
between the two preview commands in case it was a matter of timing but no joy
yet...

Hi, I have tried this but its not working so I am not sure if I've missed
something...

I put the code in two reports [rpt part 1] and [rpt part 2]. I have a button
which previews both reports calling them one after the other with:
DoCmd.OpenReport stDocName, acPreview

Then the code in both reports as you suggested:

Option Explicit
Dim intLastPage As Integer
Private Sub Report_Open(Cancel As Integer)

'only in pt 1
DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblPage Set tblPage.intPagenumber= 0;"
DoCmd.SetWarnings True

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

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

End Sub

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

[Page] = [Page] + intLastPage

End Sub

In the Page footer I have [Page] - should this be different referring to the
variable somehow? Or is it because I have it in the Page footer and not the
report footer? The tblPage.intPagenumber is 0 and both reports start at Page
1 ???

Thanks for any help...

Is there a way to print out multiple reports and have consecutive numbering?
I want to be able to print three or four different reports and have the pages
numbered consecutively or maybe it is sequentially starting at page 1. I can
get the the reports to print in order but just can't get the page numbering
figured out.

Thanks
Mark

Make a table to hold the last page number of each report.
Table name "tblPage"
All you need is one field:
"intPageNumber" Number datatype, Integer

Next, enter a 0 (Zero) into the field as a starter number.

Now in each report, Dim a variable in the declarations section:

Option Compare Database
Option Explicit
Dim intLastPage as Integer

Code each report's Open event:
intLastPage = DLookUp("[intPageNumber]","tblPage")

Code each Report's Report Header Format event:
[Page] = [Page] + intLastPage

Code each Report's Report Footer Print event:

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

Each report will pick up the ending page of the previous report and
increment it by 1.
Note: You'll not be able to use the [Pages] property in any of these
reports, i.e. ="Page " & [Page] & " of " & [Pages] as you'll get
something
like "Page 32 of 4".

You must enter a 0 in the table at the start of each batch of reports.
If there is always one same report which is run first in the batch,
just use a RunSQL in the first Report's Open event (before anything
else):

DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblPage Set tblPage.intPageNumber = 0;"
Docmd.SetWarnings True

to reset the field value to zero.

If the Reports are in run in random order, manually (or otherwise)
enter a 0 into that table field before starting.