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(str ReportName As String)
On Error GoTo PrintReport_Err

'Declare variables
Dim Error_Message As Variant
Dim objPrinter As Object
Dim prtDefaultPrint er As Printer

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

'Get default printer
prtDefaultPrint er = Application.Pri nter

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

'Change printer from default to "REPORT_PRINTER "
For Each objPrinter In Application.Pri nters
If objPrinter.Devi ceName = REPORT_PRINTER Then
Set Application.Pri nter = objPrinter
End If
Next

'Change "REPORT_PRINTER " properties
With Reports(strRepo rtName).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.Pri nter = prtDefaultPrint er

PrintReport_End :
Exit Sub

PrintReport_Err :
Select Case Err.Number
Case NO_PRINTER_INST ALLED
Error_Message = MsgBox(e_Messag e_001, vbOKOnly + vbInformation)
Resume PrintReport_End
Case Else
MsgBox Prompt:=Err.Des cription, Buttons:=vbCrit ical & 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 prtDefaultPrint er As Printer
Set prtDefaultPrint er = Application.Pri nter
Application.Pri nter = Application.Pri nters(REPORT_PR INTER)
DoCmd.OpenRepor t strReportName, acViewNormal
Application.Pri nter = prtDefaultPrint er

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******** *************@i 40g2000cwc.goog legroups.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(str ReportName As String)
On Error GoTo PrintReport_Err

'Declare variables
Dim Error_Message As Variant
Dim objPrinter As Object
Dim prtDefaultPrint er As Printer

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

'Get default printer
prtDefaultPrint er = Application.Pri nter

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

'Change printer from default to "REPORT_PRINTER "
For Each objPrinter In Application.Pri nters
If objPrinter.Devi ceName = REPORT_PRINTER Then
Set Application.Pri nter = objPrinter
End If
Next

'Change "REPORT_PRINTER " properties
With Reports(strRepo rtName).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.Pri nter = prtDefaultPrint er

PrintReport_End :
Exit Sub

PrintReport_Err :
Select Case Err.Number
Case NO_PRINTER_INST ALLED
Error_Message = MsgBox(e_Messag e_001, vbOKOnly + vbInformation)
Resume PrintReport_End
Case Else
MsgBox Prompt:=Err.Des cription, Buttons:=vbCrit ical & vbOKOnly, _
Title:="Error Number " & Err.Number & " Occurred"
Resume PrintReport_End
End Select
End Sub