Solved

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

Posted on 2012-03-12
16
560 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:jodstrr2
  • 9
  • 5
  • 2
16 Comments
 
LVL 15

Expert Comment

by:Ess Kay
ID: 37711725
whats stopping you from pressing print twice?
0
 
LVL 15

Accepted Solution

by:
Ess Kay earned 400 total points
ID: 37711730
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
 

Author Comment

by:jodstrr2
ID: 37711737
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
 

Author Comment

by:jodstrr2
ID: 37711768
This " Call PrintToSpecificPrinter(strPrinter, strReport)" is call from a module
0
 
LVL 15

Expert Comment

by:Ess Kay
ID: 37711771
try this

     DoCmd.SelectObject strReport, "MYREPORT", False
       DoCmd.PrintOut , , , , 2
0
 

Author Comment

by:jodstrr2
ID: 37711788
I tried it, it still gave the same error message "type mismatch" and only one copy printed out
0
 
LVL 15

Expert Comment

by:Ess Kay
ID: 37711810
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
 

Author Comment

by:jodstrr2
ID: 37711838
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:jodstrr2
ID: 37711991
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
 
LVL 15

Expert Comment

by:Ess Kay
ID: 37711997
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 100 total points
ID: 37712440
<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
 

Author Comment

by:jodstrr2
ID: 37712615
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
 

Author Comment

by:jodstrr2
ID: 37714706
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
 

Author Comment

by:jodstrr2
ID: 37714964
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
 

Author Closing Comment

by:jodstrr2
ID: 37715009
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37717879
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now