Solved Selecting A Printer When Printing A Report (1 Viewer)

[ChrisMore]

Hello!

I'm hoping for some advice and guidance but first a little bit of background on my set up:

The office has two PCs and two printers, the default printer is called "+25" and the other printer is "Zebra Printer". However the Zebra printer is also called "\\4H1Z742\Zebra Printer" because it's on a network and not hardwired to one of the PCs.

I am trying to set up two reports to print to these printers. The pallet labels report is printed on +25 and the carton labels report is on the Zebra printer. My pallet label code works fine to print from the +25 default printer but when I then attempt to run the carton label code it first prints to the +25 and then will print on the Zebra printer every time until the +25 printer is next used. I can't figure out why the carton label report wants to return to the default printer every time the pallet label report is printed. The code is supposed to set the printer to the Zebra printer but doesn't respond on the first print after printing the pallet label report.

Pallet label code

Private Sub Command10_Click()

Dim prn As Printer

Set prn = Application.Printer

Application.Printer = Application.Printers("+25")
 
    DoCmd.OpenReport "Rockwool_Pallet_Labels_Report_and_Subreport", acNormal
    
End Sub

Carton label code

Private Sub Command2_Click()

On Error GoTo ErrorHandler

Dim rpt As Report
Dim prtr As Printer
 
   Set prtr = Application.Printer
   Application.Printer = Application.Printers("\\4H1Z742\Zebra Printer")
 
 
    prtr.TopMargin = 0.5
    prtr.BottomMargin = 0.5
    prtr.LeftMargin = 0.5
    prtr.RightMargin = 0.5
    prtr.PaperSize = acPRPSUser
    prtr.Orientation = acPRORLandscape

 'open your report in hidden view to apply print settings
    DoCmd.OpenReport "Carton_Labels_Report", acViewPreview, , , acHidden
    Set rpt = Reports![Carton_Labels_Report]
   'Set the Printer property of the report to the
   'Application.Printer object
   Set rpt.Printer = prtr
' now open the report in normal view to print
     DoCmd.OpenReport "Carton_Labels_Report", acViewNormal
    DoCmd.Close acReport, "Carton_Labels_Report", acSaveNo
    
Exit Sub
    
ErrorHandler:

 Set prtr = Application.Printer
   Application.Printer = Application.Printers("Zebra Printer")
 
 
    prtr.TopMargin = 0.5
    prtr.BottomMargin = 0.5
    prtr.LeftMargin = 0.5
    prtr.RightMargin = 0.5
    prtr.PaperSize = acPRPSUser
    prtr.Orientation = acPRORLandscape
 
  'open your report in hidden view to apply print settings
    DoCmd.OpenReport "Carton_Labels_Report", acViewPreview, , , acHidden
    Set rpt = Reports![Carton_Labels_Report]
   'Set the Printer property of the report to the
   'Application.Printer object
   Set rpt.Printer = prtr
' now open the report in normal view to print
     DoCmd.OpenReport "Carton_Labels_Report", acViewNormal
    DoCmd.Close acReport, "Carton_Labels_Report", acSaveNo
    
End Sub

Any help will be much appreciated.

 

[arnelgp]

i found this from one of the thread,
not sure if this will work:

Public Function PrintReport(ByVal rpt As String, ByVal fltr As String, ByVal strPrinter As String, Optional ByVal InColor As Boolean = False)
  Dim prt As Printer
    For Each prt In Application.Printers
        If strPrinter = prt.DeviceName Then
            GoTo SetPrinter
        End If
    Next
 
SetPrinter:
    'Printer name is case sensitive - update name if case incorrect
    If StrComp(strPrinter, prt.DeviceName, vbBinaryCompare) <> 0 Then
        strPrinter = prt.DeviceName
    End If
    Application.Printer = Application.Printers(strPrinter)
    Set prt = Application.Printers(strPrinter)
    If InColor Then
        prt.ColorMode = acPRCMColor
    Else
        prt.ColorMode = acPRCMMonochrome
    End If
 
    DoCmd.OpenReport rpt, acViewPreview, , fltr
    Set Reports(rpt).Printer = prt
 
End Function

Call PrintReport("theReportName","(1=1)", "\\4H1Z742\Zebra Printer", False)

 

[ChrisMore]

i found this from one of the thread,
not sure if this will work:

Public Function PrintReport(ByVal rpt As String, ByVal fltr As String, ByVal strPrinter As String, Optional ByVal InColor As Boolean = False)
  Dim prt As Printer
    For Each prt In Application.Printers
        If strPrinter = prt.DeviceName Then
            GoTo SetPrinter
        End If
    Next

SetPrinter:
    'Printer name is case sensitive - update name if case incorrect
    If StrComp(strPrinter, prt.DeviceName, vbBinaryCompare) <> 0 Then
        strPrinter = prt.DeviceName
    End If
    Application.Printer = Application.Printers(strPrinter)
    Set prt = Application.Printers(strPrinter)
    If InColor Then
        prt.ColorMode = acPRCMColor
    Else
        prt.ColorMode = acPRCMMonochrome
    End If

    DoCmd.OpenReport rpt, acViewPreview, , fltr
    Set Reports(rpt).Printer = prt

End Function

Call PrintReport("theReportName","(1=1)", "\\4H1Z742\Zebra Printer", False)

Thanks arnelgp,

Where do I add this part of the code into your suggestion?

      prtr.TopMargin = 0.5
    prtr.BottomMargin = 0.5
    prtr.LeftMargin = 0.5
    prtr.RightMargin = 0.5
    prtr.PaperSize = acPRPSUser
    prtr.Orientation = acPRORLandscape

 

[ChrisMore]

I found a solution here: https://stackoverflow.com/questions...ter-in-vba-for-access-also-change-page-layout

Carton label code works perfectly for what I need, pallet label code doesn't need to change.

Private Sub Command2_Click()
Dim DefaultPrinter As String
Dim ReportPrinter As String
Dim prt As Access.Printer
Dim reportName As String

reportName = "Carton_Labels_Report"
ReportPrinter = cbxPrinterList

'Get the current default printer so we can restore it after printing
DefaultPrinter = Application.Printer.DeviceName
'No need to change the printer if they chose the current default
If ReportPrinter <> DefaultPrinter Then
    For Each prt In Application.Printers
        If prt.DeviceName = ReportPrinter Then
            Set Application.Printer = prt
                prt.TopMargin = 0.5
                prt.BottomMargin = 0.5
                prt.LeftMargin = 0.5
                prt.RightMargin = 0.5
                prt.PaperSize = acPRPSUser
                prt.Orientation = acPRORLandscape
            Exit For
        End If
    Next prt
End If

DoCmd.OpenReport reportName, acViewNormal
If ReportPrinter <> DefaultPrinter Then
    Set Application.Printer = Application.Printers(DefaultPrinter)
End If
End Sub

Private Sub Form_Open(Cancel As Integer)
Dim prt As Access.Printer

'Populate the Printer Combo Box
For Each prt In Application.Printers
    Me.cbxPrinterList.AddItem prt.DeviceName
Next prt
Set prt = Nothing
End Sub