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?
 
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
 
Ess KayEntrapenuerCommented:
whats stopping you from pressing print twice?
0
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.