Automatically print report from a specific printer

Hi Experts,
I have users that assigned to their own defaul printer (every user has different printer IP), however, I have a report that I would like all the users can print from one specific printer (not the one that they assigned to) and not display the printer dialog for them to select that specific printer, when they click ok button on the form to print the report, the report will automatically print from that specific printer.  ( I will set the report as DoCmd.OpenReport stdocname, acNormal).  Does anyone have any idea or any code I can use?  ( I used  DoCmd.RunCommand acCmdPrint but this is for pop up the printer dialog for the user to select the printer, which I don't want to do that because I in that cases, I have to install that specific printer on each user's computer)

Thanks
jodstrr2Asked:
Who is Participating?
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.

Helen FeddemaCommented:
This code will do it:

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
   Debug.Print "Current default printer: " & prtDefault.DeviceName
   
   '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
      
ErrorHandlerExit:
   Exit Sub

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

End Sub

================================

Public Function ListPrinters()
'Lists Access printer names as used in VBA code
'to the Immediate window
  
   Dim prt As Access.Printer
   
   For Each prt In Application.Printers
      Debug.Print prt.DeviceName
   Next prt
   
End Function

Open in new window


The second procedure lists the printer names as used in code.
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:
where I can put this code in?  Modules? or on the form which will print the report?
0
jodstrr2Author Commented:
here is my code from the form

Private Sub Command18_Click()
On Error GoTo Err_Command18_Click

    Dim stdocname As String
    Dim stCriteria As String

    Form_frmPrintedFormsAll.HoldAnyData = Me.NameEntered
    Form_frmPrintedFormsAll.Note = Me.AddNote
    Form_frmPrintedFormsAll.RecordNo = Me.RecordNo
   
    If IsNull(NameEntered) Or IsNull(RecordNo) Then
       MsgBox "Please enter all informations before proceeding."
       Exit Sub
    Else
       stCriteria = "CHRecord = '" & Me.RecordNo & "'"
       stdocname = "rptRequest"
       DoCmd.OpenReport stdocname, acPreview
       'DoCmd.RunCommand acCmdPrint

       'Form_frmParmFileRequest.Visible = False
    End If
   
Exit_Command18_Click:
    DoCmd.Close acForm, "frmParmForm"
    Exit Sub

Err_Command18_Click:
    MsgBox Err.DESCRIPTION
    Resume Exit_Command18_Click
   
End Sub
0
The Ultimate Tool Kit for Technolgy Solution Provi

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 for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Richard DanekeTrainerCommented:
If you use Page Setup for a Form or Report in Access, you can specify a specific printer.  Without specifying, the print goes to the default printer for the workstation.

However, when specified, the print goes to the specific printer  

You may not need to write code.
0
jodstrr2Author Commented:
Hi Dodahs,
If I use the page setup for the report then I have to install the specific printer on every user's computer and we have more than 100 users that's the reason we don't want to install the printer on every user's computer instead have the code on the form to do that if it's possible.
0
dinsjCommented:
If you dont want to get into scripting to verify users you could use a bit of freeware call event comb.  very simple to use, just add the name of the server that the print queues are on and set the event ids for print events and this will output to an easy to read text file.
0
Richard DanekeTrainerCommented:
Identify the value for your specific printer.

Once you have that, use Helen's subroutine code to print your report to a specifit printer

Save her first code as a module in Access.

Call the subroutine with the name of the printer, a comma, and the name of the report:
   Call PrintToSpecificPrinter("MyPrinter", "MyReport")
as part of the OnClick code for the button.
0
jodstrr2Author Commented:
ok, DoDahd,
I just copy Helen's first code to a module, but I'm not quite understand this
"Call the subroutine with the name of the printer, a comma, and the name of the report:
   Call PrintToSpecificPrinter("MyPrinter", "MyReport")"

here is my code now but I got an error message ""Argument Not Optional".  I knew I did something wrong but just don't know where.

Private Sub Command18_Click()
On Error GoTo Err_Command18_Click

    Dim stdocname As String
    Dim stCriteria As String

    Form_frmPrintedFormsAll.HoldAnyData = Me.NameEntered
    Form_frmPrintedFormsAll.Note = Me.AddNote
    Form_frmPrintedFormsAll.RecordNo = Me.RecordNo
   
    If IsNull(NameEntered) Or IsNull(RecordNo) Then
       MsgBox "Please enter all informations before proceeding."
       Exit Sub
    Else
       stCriteria = "CHRecord = '" & Me.RecordNo & "'"
       stdocname = "rptRequest"
       DoCmd.OpenReport stdocname, acNormal
       PrintToSpecificPrinter
    End If
   
Exit_Command18_Click:
    DoCmd.Close acForm, "frmParmForm"
    Exit Sub

Err_Command18_Click:
    MsgBox Err.DESCRIPTION
    Resume Exit_Command18_Click
   
End Sub
0
jodstrr2Author Commented:
Hi dinsj,
I'm not quite understand how to use your suggest.
0
dinsjCommented:
you can download it from here:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=18465

once in the software you can add a server or multiple servers that have the required print queues then add the print queue event id or just certain type of events

you can use it for more then just print event capturing
0
Richard DanekeTrainerCommented:
jodstrr2

To use the code within your code, in place of
       DoCmd.OpenReport stdocname, acNormal
       PrintToSpecificPrinter

use
       stPrintSpecific =  "printer name"
       Call PrintToSpecificPrinterst(stPrintSpecific , stdocname)

after you replace your special printer name for "printer name"
0
jodstrr2Author Commented:
Hi Dodahd,
Do I need to change anything from Helen's first code or just save as is in the module?  
"Debug.Print "Current default printer: " & prtDefault.DeviceName"  do I need to change the "Device Name" to the printer name?  thanks
0
Richard DanekeTrainerCommented:
jodstrr2:

You can edit her code, but you need to understand the code:

Public Sub PrintToSpecificPrinter(strPrinter As String, strReport As String)

  'the use of names in the parentheses set variable that can be passed to the  subroutine
  'this is why the call statement
          ' PrintToSpecificPrinterst(strPrinter , strReport)
  'works to identify the printer and a Report
  'please note the change in the call names for clarity

'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   this saves the workstation default to be restored later
   Set prtDefault = Application.Printer
'the Debut.Print statement shows the current workstation in the VBA Immediates window
'therefore, you would not need this statement in the production code
'but, may want to watch it work in testing while in VBA
   Debug.Print "Current default printer: " & prtDefault.DeviceName
   
   'Select a specific printer as new default printer  
   'the value for the printer is passed in your call statement to select the specific printer
   Application.Printer = Printers(strPrinter)
   
   'Print the report
  ' the value for the report is passed in your call statement
   DoCmd.OpenReport strReport
   
   'Set printer back to former default printer
   ' after printing the report, this restores the default printer for this workstation
   Application.Printer = prtDefault


NOW:  updating code in your OnClick Event use:

       Dim strReport as String
       strReport = strdocname
       Dim strPrinter as String
       strPrinter =  "printer name"
       Call PrintToSpecificPrinterst(strPrinter, strReport)

The DIM statements can be placed anywhere.  Many people like to place them at the beginning of the code.
0
jodstrr2Author Commented:
Hi Dodahd,
Here is the my whole code,
In the Module is :
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
   'Dim strPrinter As String
   'Save current default printer
   Set prtDefault = Application.Printer
   Debug.Print "Current default printer: " & prtDefault.DeviceName
   '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
     
ErrorHandlerExit:
   Exit Sub

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

End Sub


In my OnClick Event is :
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
    strPrinter = strdocname
    strPrinter = "AA_ABCE_CL4650_122"
   
    Form_frmPrintedFormsAll.HoldAnyData = Me.NameEntered
    Form_frmPrintedFormsAll.Note = Me.AddNote
    Form_frmPrintedFormsAll.RecordNo = Me.RecordNo
   
    If IsNull(NameEntered) Or IsNull(RecordNo) Then
       MsgBox "Please enter all informations before proceeding."
       Exit Sub
    Else
       stCriteria = "CHRecord = '" & Me.RecordNo & "'"
       stdocname = "rptFileRequest"
       Call PrintToSpecificPrinter(strPrinter, strReport)


       'DoCmd.OpenReport stdocname, acNormal
       'PrintToSpecificPrinter
       'Form_frmParmFileRequest.Visible = False
    End If

   
Exit_Command18_Click:
    DoCmd.Close acForm, "frmParmFileRequest"
    Exit Sub

Err_Command18_Click:
    MsgBox Err.DESCRIPTION
    Resume Exit_Command18_Click
   
End Sub

But I got an error message when I tried to print
Error No: 2497; Description: The action or method requires a report name argument.

What did I do wrong?

Thanks
0
jodstrr2Author Commented:
I figure it out the problem.
0
jodstrr2Author Commented:
Hi Dodahd,
I figure out the problem, it's
 stdocname = "rptFileRequest" should be strReport = "rptFileRequest"

Thanks
0
jodstrr2Author Commented:
Thanks so much for Helen and Dodahd's huge help.  Thanks again
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.