How to print more than one copy of a report from Ms Access

Hi Experts,
How do I print more that one copy of a report from Ms Access that the code I use is to call the modlue.  
here is my code on the click event (I also have a modlue)

Private Sub Command18_Click()
On Error GoTo Err_Command18_Click
    Dim stdocname As String
    Dim stCriteria As String
    Dim strReport As String
    Dim strPrinter As String
    strReport = stdocname
    strPrinter = "AA_ABCE_CL4600_123"
   
    Form_frmPrintedFormsAll.HoldAnyData = Me.NameEntered
    Form_frmPrintedFormsAll.Note = Me.AddNote
    Form_frmPrintedFormsAll.RecordNo = Me.RecordNo
    Form_frmPrintedFormsAll.Dept = Me.Dept    
    If IsNull(NameEntered) Or IsNull(RecordNo) Then
       MsgBox "Please enter all informations before proceeding."
       Exit Sub
    Else
       stCriteria = "CHRecord = '" & Me.RecordNo & "'"
       strReport = "rptFileRequest"
       Call PrintToSpecificPrinter(strPrinter, strReport)
    End If
   
Exit_Command18_Click:
    DoCmd.Close acForm, "frmParmFileRequest"
    Exit Sub

Err_Command18_Click:
    MsgBox Err.DESCRIPTION
    Resume Exit_Command18_Click
   
End Sub

This "Call PrintToSpecificPrinter(strPrinter, strReport)" will only give me one copy of the report, what should I put in the code if I need 2 copies of the same report.

Thanks
jodstrr2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ess KayEntrapenuerCommented:
whats stopping you from pressing print twice?
0
Ess KayEntrapenuerCommented:
http://www.techrepublic.com/blog/msoffice/print-multiple-copies-of-an-access-report/1035


When you print a report manually, you have the opportunity to specify the number of copies in the Print dialog box. Automating the process is simple too — just execute the PrintOut method as follows:
[b]
DoCmd.PrintOut printrange, pagefrom, pageto, printquality, copies, collatecopies[/b]

As you can see, the fifth argument is copies; specify the number of copies using this argument. For instance, the following statement opens and prints four copies of a report named Invoices:

DoCmd.OpenReport “Invoices”, acViewPreview

DoCmd.PrintOut , , , , 4

If the report is already open, make sure it’s current before printing, as follows:

DoCmd.SelectObject acReport, “Invoices”, True

DoCmd.PrintOut , , , , 4


If you want to print only one copy, you can do so immediately from the OpenReport method by changing acViewPreview to acViewNormal, as follows:

DoCmd.OpenReport “Invoices”, acViewNormal

This statement will send the Invoices report directly to the printer.

If users need more flexibility, create a print form that captures page numbers and copy count and pass those values to the report via the OpenReport method.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jodstrr2Author Commented:
I tried this:
      Call PrintToSpecificPrinter(strPrinter, strReport)
       DoCmd.SelectObject strReport, False
       DoCmd.PrintOut , , , , 2

But I got the error message "type mismatch" and only gave me one copy.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

jodstrr2Author Commented:
This " Call PrintToSpecificPrinter(strPrinter, strReport)" is call from a module
0
Ess KayEntrapenuerCommented:
try this

     DoCmd.SelectObject strReport, "MYREPORT", False
       DoCmd.PrintOut , , , , 2
0
jodstrr2Author Commented:
I tried it, it still gave the same error message "type mismatch" and only one copy printed out
0
Ess KayEntrapenuerCommented:
below is the definition of the statement. Make sure you change "MYREPORT" to the actual programmed report name

also see if it will work if you change the ending to true


The SelectObject method carries out the SelectObject action in Visual Basic.

Syntax
expression .SelectObject(ObjectType, ObjectName, InNavigationPane)


expression A variable that represents a DoCmd object.





The following example selects the form Customers in the Database window:

VBA
DoCmd.SelectObject acForm, "Customers", True
0
jodstrr2Author Commented:
I did tried both, if This " Call PrintToSpecificPrinter(strPrinter, strReport)" is not call from a module then I can put something like this with no problems
       stdocname = "rptFileRequest"
        DoCmd.OpenReport stdocname, acPreview
        DoCmd.SelectObject acReport, stdocname, False
        DoCmd.PrintOut acPrintAll, , , , 2
        DoCmd.Close acReport, stdocname
but since the " Call PrintToSpecificPrinter(strPrinter, strReport)" is from a module, it doen't work if I use
      Call PrintToSpecificPrinter(strPrinter, strReport)"
       DoCmd.SelectObject strReport, "rptFileRequest", False
       DoCmd.PrintOut , , , , 2
or
       Call PrintToSpecificPrinter(strPrinter, strReport)"
       DoCmd.SelectObject strReport, "rptFileRequest", True
       DoCmd.PrintOut , , , , 2
0
jodstrr2Author Commented:
I got this module from Heleh Feddema
Public Sub PrintToSpecificPrinter(strPrinter As String, strReport As String)
'Created by Helen Feddema 12-Feb-2010
'Last modified by Helen Feddema 12-Feb-2010

On Error GoTo ErrorHandler

   Dim prtCurrent As Printer
   Dim prtDefault As Printer
   
   'Save current default printer
   Set prtDefault = Application.Printer
   
   'Select a specific printer as new default printer
   Application.Printer = Printers(strPrinter)
   
   'Print the report
   DoCmd.OpenReport strReport
 
   'Set printer back to former default printer
   Application.Printer = prtDefault
   Debug.Print "Current default printer: " & prtDefault.DeviceName
   
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & _
      Err.DESCRIPTION
   Resume ErrorHandlerExit

End Sub

I don't know if I should declear how may copies on the module or shoul put on the OnClick Event.
0
Jeffrey CoachmanMIS LiasonCommented:
<No Points wanted>

jodstrr2,

FWIW, I'm with esskayb2d.

What he posted first should work for you...

Try it simply *FIRST*
(Without any Calls to other modules and variables...)

...make sure it woks first , then get fancier if you like.

FWIW this woks just fine for me:

DoCmd.OpenReport "MyReport", acViewPreview
DoCmd.PrintOut , , , , 2

(Note that if you open the report, it has the focus by default, so there may not be an immediate need for any "Selection" code)

Again, keep it Simple first.

JeffCoachman
0
jodstrr2Author Commented:
Hi boag2000,
The code that I provide above is because I have to send this report to a specific printer(only this report) then when I try to print other reports, the system will be automatically send those reports back to my defaul printer.  That's why I use Helen Feddema's code (set in module first then call from the module, that's why I have "Call PrintToSpecificPrinter(strPrinter, strReport)"on the OnClick event and it work fine if I just need one copy but I just having trobule to tell the system print 2 copies.  

What about this:
DoCmd.OpenReport "Call PrintToSpecificPrinter(strPrinter, strReport)", acViewPreview
DoCmd.PrintOut , , , , 2
0
jodstrr2Author Commented:
Hi boag2000,
I tried this:
DoCmd.OpenReport "Call PrintToSpecificPrinter(strPrinter, strReport)", acViewPreview
DoCmd.PrintOut , , , , 2
also not working, I also tried to put in to the module as below (please referred to the *** line) but still not working.

This is the store in the module:
Public Sub PrintToSpecificPrinter(strPrinter As String, strReport As String)
'Created by Helen Feddema 12-Feb-2010
'Last modified by Helen Feddema 12-Feb-2010

On Error GoTo ErrorHandler

   Dim prtCurrent As Printer
   Dim prtDefault As Printer
   
   'Save current default printer
   Set prtDefault = Application.Printer
   
   'Select a specific printer as new default printer
   Application.Printer = Printers(strPrinter)
   
   'Print the report
   DoCmd.OpenReport strReport
   Docmd.Printout , , , , 2     ****** This is what I added

   'Set printer back to former default printer
   Application.Printer = prtDefault
   Debug.Print "Current default printer: " & prtDefault.DeviceName
   
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & _
      Err.DESCRIPTION
   Resume ErrorHandlerExit

End Sub


Below is the OnClick Event that call the Module (the line the says "Call PrintToSpecificPrinter(strPrinter, strReport)")
Private Sub Command18_Click()
On Error GoTo Err_Command18_Click
    Dim stdocname As String
    Dim stCriteria As String
    Dim strReport As String
    Dim strPrinter As String
    strReport = stdocname
    strPrinter = "AA_ABCE_CL4600_123"
   
    Form_frmPrintedFormsAll.HoldAnyData = Me.NameEntered
    Form_frmPrintedFormsAll.Note = Me.AddNote
    Form_frmPrintedFormsAll.RecordNo = Me.RecordNo
    Form_frmPrintedFormsAll.Dept = Me.Dept    
    If IsNull(NameEntered) Or IsNull(RecordNo) Then
       MsgBox "Please enter all informations before proceeding."
       Exit Sub
    Else
       stCriteria = "CHRecord = '" & Me.RecordNo & "'"
       strReport = "rptFileRequest"
       Call PrintToSpecificPrinter(strPrinter, strReport)
    End If
   
Exit_Command18_Click:
    DoCmd.Close acForm, "frmParmFileRequest"
    Exit Sub
0
jodstrr2Author Commented:
ok, I figure it out how to print 2 copies.  here is my new code on the OnClick Event

On Error GoTo Err_Command18_Click
    Dim stdocname As String
    Dim stCriteria As String
    Dim strReport As String
    Dim strPrinter As String
    strReport = stdocname
    strPrinter = "AA_ABCE_CL4600_123"
   
    Form_frmPrintedFormsAll.HoldAnyData = Me.NameEntered
    Form_frmPrintedFormsAll.Note = Me.AddNote
    Form_frmPrintedFormsAll.RecordNo = Me.RecordNo
    Form_frmPrintedFormsAll.Dept = Me.Dept    
    If IsNull(NameEntered) Or IsNull(RecordNo) Then
       MsgBox "Please enter all informations before proceeding."
       Exit Sub
    Else
       stCriteria = "CHRecord = '" & Me.RecordNo & "'"
       strReport = "rptFileRequest"
       Call PrintToSpecificPrinter(strPrinter, strReport)
       Call PrintToSpecificPrinter(strPrinter, strReport)
    End If
   
Exit_Command18_Click:
    DoCmd.Close acForm, "frmParmFileRequest"
    Exit Sub
0
jodstrr2Author Commented:
For esskayb2d, the code you provide was not really helping in my situation but still a good explanation to how to print more copies.   Thanks

For boag2000, event you don't want any points but you still contributed time on my ticket.  Thanks
0
Jeffrey CoachmanMIS LiasonCommented:
Just note that you don't have to award any points for "time", only award points for posts that led to a solution...

;-)

jeff
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.