Printing Consectutive Reports 1

[MoDiggity]

I have 3 reports that I need to print before moving to the next record. The Reports are named RMSection1 (3 Pages), RMSection2(4 Pages) , & RMSection3 (1 page).

I found some code from MS but I can't get it to work can anyone help?

The following code example sounds exactly like what I need to do except I have 3 reports.

To collate and print two reports and to test the results, follow these steps:

Open the sample database NWIND.MDB.
Create a new module and type the following lines in the Declarations section:

'****************************************************************
'Declarations section of the module
'****************************************************************

Option Compare Database
Option Explicit

Type the following function:

'**********************************************************************
'NumPages is the number of pages in the largest report. If one report
'has fewer pages, the DoCmd Print statement for the smaller report runs
'correctly and no additional pages are printed.
'**********************************************************************

Function CollateReports(NumPages, Rpt1 As String, Rpt2 As String)

Dim MyPageNum As Integer

' Set the page number loop and alternate printing the report pages.

For MyPageNum = 1 To NumPages

' NumPages is the number of pages to print.

DoCmd SelectObject A_REPORT, Rpt1, True
DoCmd Print A_PAGES, MyPageNum, MyPageNum
DoCmd SelectObject A_REPORT, Rpt2, True
DoCmd Print A_PAGES, MyPageNum, MyPageNum

Next MyPageNum

End Function

To test the results, type the following line in the Immediate Window, and then press ENTER:

Print CollateReports(1, "Sales summaries", "Sales Totals by amount&quot

Note that Microsoft Access prints one page from each report.

End Function

 

[PaulBricker]

Mo, post the code you used and any error messages that you are getting.


Paul

 

[MoDiggity]

Thanks, I'm not a programmer, any help is appreciated

Option Compare Database
'Option Explicit

Private Sub Command369_Click()


'Function CollateReports
Dim NumPages, RMsection1 As String, RMSection2 As String, RMsection13 As String

Dim MyPageNum As Integer

' Set the page number loop and alternate printing the report pages.

For MyPageNum = 1 To NumPages

' NumPages is the number of pages to print.

DoCmd.SelectObject acReport, "RMsection1", True
DoCmd.PrintOut acPages, MyPageNum, MyPageNum
DoCmd.SelectObject acReport, "RMsection2", True
DoCmd.PrintOut acPages, MyPageNum, MyPageNum
DoCmd.SelectObject acReport, "RMsection3", True
DoCmd.PrintOut acPages, MyPageNum, MyPageNum

Next MyPageNum

End Sub

 

[PaulBricker]

Open your database, go to the database window, click on Modules and click New. Then copy and paste this code into the VBA window.

Function CollateReports(NumPages, Rpt1 As String, Rpt2 As String, Rpt3 As String)

Dim MyPageNum As Integer

' Set the page number loop and alternate printing the report pages.

For MyPageNum = 1 To NumPages

' NumPages is the number of pages to print.

DoCmd SelectObject A_REPORT, Rpt1, True
DoCmd Print A_PAGES, MyPageNum, MyPageNum
DoCmd SelectObject A_REPORT, Rpt2, True
DoCmd Print A_PAGES, MyPageNum, MyPageNum
DoCmd SelectObject A_REPORT, Rpt3, True
DoCmd Print A_PAGES, MyPageNum, MyPageNum

Next MyPageNum

End Function

Now in the Click Event for your button put this code. You will have to change the first argument from 4, the value I've used to whatever is the largest page number in your three reports.

Call CollateReports(4,"RMSection1","RMSection2","RMSection3&quot


Paul

 

[MoDiggity]

4 is the highest number of pages.
Although I've saveed it, I get a message that says the macro cannot be found, do I need to add anything?

 

[PaulBricker]

On the line next to the Click Event for your button, you should be showing the value

[Event Procedure]

Make sure that is showing. If that doesn't do it then we need to try and figure exactly when the error is being generated.

Paul

 

[MoDiggity]

I get a syntax error and the first line of the following gets highlighted. This whole section appears red...

DoCmd SelectObject A_REPORT, Rpt1, True
DoCmd Print A_PAGES, MyPageNum, MyPageNum
DoCmd SelectObject A_REPORT, Rpt2, True
DoCmd Print A_PAGES, MyPageNum, MyPageNum
DoCmd SelectObject A_REPORT, Rpt3, True
DoCmd Print A_PAGES, MyPageNum, MyPageNum

 

[PaulBricker]

Good old Microsoft help. Try this. I've added a . after the DoCmd line.
DoCmd. SelectObject A_REPORT, Rpt1, True
DoCmd. Print A_PAGES, MyPageNum, MyPageNum
DoCmd. SelectObject A_REPORT, Rpt2, True
DoCmd. Print A_PAGES, MyPageNum, MyPageNum
DoCmd. SelectObject A_REPORT, Rpt3, True
DoCmd. Print A_PAGES, MyPageNum, MyPageNum


Paul

 

[MoDiggity]

Then I get this error

"Object doesn't support this property or method"

and the following line is highlighted

DoCmd.Print A_PAGES, MyPageNum, MyPageNum

 

[PaulBricker]

Try changing that whole section to what you tried earlier. That looks better to me than what MS has.
Paul

DoCmd.SelectObject acReport, "RMsection1", True
DoCmd.PrintOut acPages, MyPageNum, MyPageNum
DoCmd.SelectObject acReport, "RMsection2", True
DoCmd.PrintOut acPages, MyPageNum, MyPageNum
DoCmd.SelectObject acReport, "RMsection3", True
DoCmd.PrintOut acPages, MyPageNum, MyPageNum

 

[MoDiggity]

It works, sort of, not collating correctly, but I think you've helped enough to get me thru the rest, thank you very much.

Mo D

 

[PaulBricker]

Good luck.

Paul

 

[MoDiggity]

After standing back from it for a few days here's what I d' like to report (npi)...and again ask for some further direction

Although the routine works, it collates in this sequence:
(Recall that Report 1 has 3 pages, Report 2 - 4 pages, Report 3 - 1 page)

Page 1 (page 1 of report 1)
Page 4 (page 1 of report 2)
Page 8 (page 1 of report 3)
Page 2 (page 2 of report 1)
Page 5 (page 2 of report 2)
Page 3 (page 3 of report 1)
Page 6 (page 3 of report 2)
Page 7 (page 4 of report 2)

...being that this turns out to be a 12,000 page report...you can understand why this remains a problem.

I've been hacking away at revising the code to make it function correctly but each time I alter the code I incur some other fault that I cannot understand.
I tried exerimenting with changing
I tried
DoCmd.SelectObject acReport, "RMsection1", True
to
DoCmd.SelectObject acReport, "RMsection1", False
and that triggered a syntax error.
I tried
DoCmd.PrintOut acPages, MyPageNum, MyPageNum
to
DoCmd.PrintAll RMSection1
and that failed

it seems that unless someone can steer me right I can either
revise each report so that Report 2 begins with Page 2, and Report 3 begins with Page 3, and the second and third pages of each report follow the sequence of the printing
or, and preferably (mainly because I've already recreated each page as its own report and they look better) send all 8 reports to print in sequence, before selecting the next record.

I tried
DoCmd.PrintAll RMpage1
DoCmd.PrintAll RMpage2
DoCmd.PrintAll RMpage3 ...etc but that didn't work

hhhheeeeelllllppppppppp!!!!!!!

 

[PaulBricker]

I assume from what you post that you want
Rpt1 Page1
Rpt1 Page2
Rpt1 Page3
Rpt2 Page1
Rpt2 Page2
Rpt2 Page3
Rpt2 Page4
Rpt3 Page1

In the click event for you button try this

DoCmd.SelectObject acReport, "RMsection1", True
DoCmd.PrintOut acPrintAll
DoCmd.SelectObject acReport, "RMsection2", True
DoCmd.PrintOut acPrintAll
DoCmd.SelectObject acReport, "RMsection3", True
DoCmd.PrintOut acPrintAll

Try that and see if it works.

Paul

 

[MoDiggity]

Ok,

In an effort to retain any of my dwindling sanity I will redefine the cureent successes/failures/desires.

I'm also going to repost the current working code as a new thread so that hopefully, some other participants will help bring this to fruition.

The current code that is executing somewhat correctly and the result.

Objective:
1. I now have a total of eight 1 page reports: (RM_Page_1, RM_Page_2,RM_Page_3...etc)
2a. I want to be able to select a button to print all eight reports based on the current record appearing on my form.
2b. I also want to be able to filter the records based on several criteria, and then print all 8 reports (collated) for each record that matches the selected criteria.

Here's the code and what it does/is doing:
I've shortened it to 3 reports for testing)

The "On Click" event of my button code:

*****************************************
Private Sub Command378_Click()
On Error GoTo Err_Command378_Click

Call CollReports2 (5, "RM_Page_1", "RM_Page_2","RM_Page_3&quot
Exit_Command378_Click:
Exit Sub
Err_Command378_Click:
MsgBox Err.Description
Resume Exit_Command378_Click
End Sub
*****************************************

In the above code - I'm not certain of how to modify the (5, or if that number needs to match the number of records I want to print. It is supposed to set the maximum number of pages in each report, which now is 1, but if I choose 1, only 1 record will print...

The Module Code:

******************************************

Function CollReports2(NumPages, RM_Page_1 As String, RM_Page_2 As String, RM_Page_3 As String)

Dim MyPageNum As Integer

' Set the page number loop and alternate printing the report pages.

For MyPageNum = 1 To NumPages

' NumPages is the number of pages to print.


DoCmd.SelectObject acReport, RM_Page_1, True
DoCmd.PrintOut acPages, MyPageNum, MyPageNum
DoCmd.SelectObject acReport, RM_Page_2, True
DoCmd.PrintOut acPages, MyPageNum, MyPageNum
DoCmd.SelectObject acReport, RM_Page_3, True
DoCmd.PrintOut acPages, MyPageNum, MyPageNum

Next MyPageNum

End Function

************************************8

1st question. Although this works, each report requeries the data so that if I try to put a criteria "Like" expression into the query I have to input the parameter 8 times, how can I fix that so that the dialog will only appear once and then complete the print job?

2nd Question. How can I let the user print the 8 reports for the current record appearing on the form?

Thanks to any and all suggestions...