Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2012-03-12
16
606 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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
 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

809 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