Change printers with VBA

[PCB]

Hi all!

I have a subroutine that prints reports on a specific printer. The code
works fine as long as all the printers available to the computer are on
the network. However, if the computer also has a local printer, the
report is prints locally. What I'm I doing wrong! The complete
subroutine follows:

Private Sub PrintReport(strReportName As String)
On Error GoTo PrintReport_Err

'Declare variables
Dim Error_Message As Variant
Dim objPrinter As Object
Dim prtDefaultPrinter As Printer

'Declare constants
Const NO_PRINTER_INSTALLED As Long = 2205
Const REPORT_PRINTER As String = "\\NCOTTA14\P5-19884"

'Get default printer
prtDefaultPrinter = Application.Printer

'Open the report in design view
DoCmd.OpenReport strReportName, acViewDesign

'Change printer from default to "REPORT_PRINTER"
For Each objPrinter In Application.Printers
If objPrinter.DeviceName = REPORT_PRINTER Then
Set Application.Printer = objPrinter
End If
Next

'Change "REPORT_PRINTER" properties
With Reports(strReportName).Printer
.Copies = 1
.Orientation = acPRORLandscape
.Duplex = acPRDPVertical
.PaperBin = acPRBNAuto
.PaperSize = acPRPS11x17
.PrintQuality = acPRPQHigh
End With

'Print and close report
DoCmd.PrintOut
DoCmd.Close acReport, strReportName, acSaveNo

'Restore default printer
Application.Printer = prtDefaultPrinter

PrintReport_End:
Exit Sub

PrintReport_Err:
Select Case Err.Number
Case NO_PRINTER_INSTALLED
Error_Message = MsgBox(e_Message_001, vbOKOnly + vbInformation)
Resume PrintReport_End
Case Else
MsgBox Prompt:=Err.Description, Buttons:=vbCritical & vbOKOnly, _
Title:="Error Number " & Err.Number & " Occurred"
Resume PrintReport_End
End Select
End Sub

[Allen Browne]

Peter, I don't understand why the report is being opened in design view, and
printed with the PrintOut method (which loses you the chance to use a
WhereCondition.)

Provided you set the printer *before* you OpenReport, it should work fine.
Try:
Dim prtDefaultPrinter As Printer
Set prtDefaultPrinter = Application.Printer
Application.Printer = Application.Printers(REPORT_PRINTER)
DoCmd.OpenReport strReportName, acViewNormal
Application.Printer = prtDefaultPrinter

If you did want to set the orientation etc, you could OpenReport in
acViewPreview, and set those.

For a more comprehensive example that allows you to remember a default
printer for each report, see:
http://allenbrowne.com/AppPrintMgt.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"PCB" <pe***********@sympatico.ca> wrote in message
news:11*********************@i40g2000cwc.googlegro ups.com...

Hi all!

I have a subroutine that prints reports on a specific printer. The code
works fine as long as all the printers available to the computer are on
the network. However, if the computer also has a local printer, the
report is prints locally. What I'm I doing wrong! The complete
subroutine follows:

Private Sub PrintReport(strReportName As String)
On Error GoTo PrintReport_Err

'Declare variables
Dim Error_Message As Variant
Dim objPrinter As Object
Dim prtDefaultPrinter As Printer

'Declare constants
Const NO_PRINTER_INSTALLED As Long = 2205
Const REPORT_PRINTER As String = "\\NCOTTA14\P5-19884"

'Get default printer
prtDefaultPrinter = Application.Printer

'Open the report in design view
DoCmd.OpenReport strReportName, acViewDesign

'Change printer from default to "REPORT_PRINTER"
For Each objPrinter In Application.Printers
If objPrinter.DeviceName = REPORT_PRINTER Then
Set Application.Printer = objPrinter
End If
Next

'Change "REPORT_PRINTER" properties
With Reports(strReportName).Printer
.Copies = 1
.Orientation = acPRORLandscape
.Duplex = acPRDPVertical
.PaperBin = acPRBNAuto
.PaperSize = acPRPS11x17
.PrintQuality = acPRPQHigh
End With

'Print and close report
DoCmd.PrintOut
DoCmd.Close acReport, strReportName, acSaveNo

'Restore default printer
Application.Printer = prtDefaultPrinter

PrintReport_End:
Exit Sub

PrintReport_Err:
Select Case Err.Number
Case NO_PRINTER_INSTALLED
Error_Message = MsgBox(e_Message_001, vbOKOnly + vbInformation)
Resume PrintReport_End
Case Else
MsgBox Prompt:=Err.Description, Buttons:=vbCritical & vbOKOnly, _
Title:="Error Number " & Err.Number & " Occurred"
Resume PrintReport_End
End Select
End Sub