access 2002 collating reports when I don't want to

dailymeat
dailymeat used Ask the Experts™
on
I have this code:

    DoCmd.OpenReport "Statement", acViewNormal
    DoCmd.PrintOut A_PRINTALL, , , acHigh, 2, 0
    DoCmd.Close A_REPORT, "Statement"

I want the 2 identical statements to print one after the other, is not working
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Then keep it simple and just do this:

DoCmd.OpenReport "Statement"
DoCmd.OpenReport "Statement"

This will simply print the report twice
(no need to open the report and then close it)

JeffCoachman

Author

Commented:
Thank you, problem is that I have 400 statements for different customers I need to do
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
First you stated...
I want the 2 identical statements to print
The code I posted will print the two statements.
Please confirm, ...Yes or No

Now you say:
I have 400 statements

So what is your question here?
I am confused?
Do you have 2 statements for each 400 customers?
Or, ...400 statements for 400 customers?
Or 200 reports that you want printed twice?
Or, ...are you simply asking how to print 400 unique customer reports?
Please clarify...

If you need to print 400 unique Customer statements, ...make "One" report, ...and group it by the customer, ...then in the Customer footer, ...insert a page break
This will print as 400 "Groups", ...but because of the page breaks, ...it will really simulate 400 separate Reports.

JeffCoachman
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I have 400 reports that I want printed twice, one copy to give to customer and one copy for our files.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
....and how do you want them printed?
Cust1Report
Cust2Report
Cust3Report
Cust4Report
Cust1Report
Cust2Report
Cust3Report
Cust4Report


Or...
Cust1Report
Cust1Report
Cust2Report
Cust2Report
Cust3Report
Cust3Report
Cust4Report
Cust4Report

Author

Commented:
Cust1Report
Cust1Report
Cust2Report
Cust2Report
Cust3Report
Cust3Report
Cust4Report
Cust4Report
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
The code would look something like this:

'Declare Variables and Objects
Dim dbs             As DAO.database
Dim rst             As DAO.Recordset
Dim strSQL          As String
Dim lngCustID       As Long
Dim strReportName   As String

'Confirmation
    If MsgBox("Print 2 copies of all customer reports?, ...this may take a while, ...are you sure?", vbYesNo + vbQuestion) = vbNo Then
        Exit Sub
    End If

'Set Variables and Objects
strSQL = "SELECT CustID From tblCustomers"
strReportName = "rptCustomerReport"
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strSQL)
    
    'Loop all customers, ...and print report twice
    Do While Not rst.EOF
      lngCustID = rst!CustID
        DoCmd.OpenReport strReportName, , , "CustID=" & lngCustID, acHidden
        DoCmd.OpenReport strReportName, , , "CustID=" & lngCustID, acHidden
        DoCmd.Close acReport, strReportName
        rst.MoveNext
    Loop
    
    MsgBox "Done", vbInformation

'Recordset Cleanup
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

Open in new window


JeffCoachman

Commented:
I am glad I don't have the job of separating that output.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
@bknouse,
Agreed...
;-)
That is why I wanted to be clear on the print order.

For 400 reports, ...I, personally, would rather print out all 400 (for the customer), ...then the 400 for my records.

But what I posted will give the OP what they asked for.

JeffCoachman

Author

Commented:
Did not worked, they still printed collated.
And for your information, I need them to print "Cust1,Cust1" because we staple them together, take them to our Customer, once they paid, we bring one back for our records.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
I need them to print "Cust1,Cust1
That is what you asked for?
Cust1Report
 Cust1Report
 Cust2Report
 Cust2Report
 Cust3Report
 Cust3Report
 Cust4Report
 Cust4Report

Please make it clear what the issue is here (without using the term "collated"

Author

Commented:
Yes, just like I explained 2 days ago

ID: 421140782d
Cust1Report
Cust1Report
Cust2Report
Cust2Report
Cust3Report
Cust3Report
Cust4Report
Cust4Report
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
So the sample code I posted does not print the report for each customer twice?

Author

Commented:
Not one after other. This is what I need.

Sample: Jeffrey Coachman
               Jeffrey Coachman
               Mario
               Mario
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
what you posted IS one right after another...?
 Jeffrey Coachman
 Jeffrey Coachman
 Mario
 Mario

Which seems to be what we agreed on:
Cust1Report
Cust1Report
Cust2Report
Cust2Report

Perhaps we should explain things in words...
Please confirm:
The code I posted will:
Print the entire report for Customer 1
Then it will print the entire report for Customer one again...
Then It will print the entire report for Customer 2,
Then it will print the entire report for Customer 2 again...

If this is not what you want then you will have to explain in words, ...exactly what you are looking for...
Or, ...Perhaps you could explain what you need by sending me a scaled down version of your database.

I think we are just having a miscommunication
;-)

Jeff

Author

Commented:
I tried your full code and there is a problem, you will find attached the problem
IMG_20170504_0001.pdf
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
The presumption there was that CID was a number (as is fairly common for Primary Keys)

So try it like this instead, ...if it is textual:

DoCmd.OpenReport strReportName, , , "CustID=" & "'" & strCustID & "'", acHidden

Open in new window


Keep me posted

Author

Commented:
See attached

Author

Commented:
See attached
IMG_20170505_0001.pdf
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
You have to substitute your variable names for mine.
My code is just an example of the basic syntax.

In any event, try this sample
Access--EE_Print-2-copies-of-indiv.accdb

Author

Commented:
I substituted the variables and I get this error message that I can not figure out:

"Data type mismatch in criteria expression"

Attaching code.
Untitled.png
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
First can we confirm that the sample I posted works (as posted), and prints the reports in the order that you requested?

Author

Commented:
Yes I can confirm that it works

Author

Commented:
Hey Jeffrey,
I finally fixed my own problem without having to be so complicated:

Problem:
DoCmd.OpenReport "Statement", acViewNormal
    DoCmd.PrintOut A_PRINTALL, , , acHigh, 2, 0
    DoCmd.Close A_REPORT, "Statement"

Fix:
DoCmd.OpenReport "Statement", acPreview
    DoCmd.PrintOut A_PRINTALL, , , acHigh, 2, 0
    DoCmd.Close A_REPORT, "Statement"

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial