Access Not Printing to the Printer Chosen

Question

I'm having an issue with an access form/report as it's not printing to the printer I've chosen from a combobox (that lists all the available printers).

I do know that I did create the report through the designer with the printer zebra-01 set and I think this is what might be causing the problem.

I have the following code to print labels from the report:

    'Option Compare Database

Private Sub btnPrint_Click()
    'Validate Input Given. If the input is less than or equal to 0 discard the print command.
    'Two If statements, one for validating the input type and if the input is a positive number.
    
    If IsNull(Me.txtNumberOfLabels) Or Not IsNumeric(Me.txtNumberOfLabels.Value) Then
        MsgBox "O valor introduzido não é um valor numérico.", _
        vbOKOnly, "Erro"
        DoCmd.GoToControl "txtNumberOfLabels"
        Exit Sub
    End If
    
    If Me.txtNumberOfLabels.Value <= 0 Then
        MsgBox "O número de etiquetas a imprimir deve ser superior a 0", _
        vbOKOnly, "Erro"
        DoCmd.GoToControl "txtNumberOfLabels"
        Exit Sub
    End If
    
    Dim availablePrinters As Printer
    Dim selectedPrinter As String
    
    DoCmd.GoToControl "cbPrintersList"
    
    selectedPrinter = Me.cbPrintersList.Text
    
    For Each availablePrinters In Application.Printers
        If availablePrinters.DeviceName = selectedPrinter Then
            Set Application.Printer = availablePrinters
            Exit For
        End If
    Next availablePrinters
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Dim lastLabelRecordIndex_Part1 As String
    Dim lastLabelRecordIndex_Part2 As String
    Dim lastLabelRecordIndex_Part3 As String
    Dim oldLastLabelRecordIndex_Part1 As String
    Dim oldLastLabelRecordIndex_Part2 As String
    Dim oldLastLabelRecordIndex_Part3 As String
    
    Dim strSQL As String
    
    Set db = CurrentDb
    strSQL = "SELECT MAX(Pre_SSCC) As MaxRecord FROM SSCC_Gen"
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
    oldLastLabelRecordIndex_Part1 = CStr(Left(rs("MaxRecord"), 8))
    oldLastLabelRecordIndex_Part2 = CStr(Mid(rs("MaxRecord"), 9, 4))
    oldLastLabelRecordIndex_Part3 = CStr(Right(rs("MaxRecord"), 5))
    
    rs.Close
    db.Close

    Dim labelRecordIndex As Long
    
    DoCmd.SetWarnings False
    
    For labelRecordIndex = CLng(oldLastLabelRecordIndex_Part3) To CLng(oldLastLabelRecordIndex_Part3) + Me.txtNumberOfLabels.Value - 1
        DoCmd.RunSQL "INSERT INTO SSCC_GenCount (Data) VALUES (#" & Format(Now(), "dd/mm/yyyy") & "#)"
    Next labelRecordIndex
    
    DoCmd.SetWarnings True
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
    lastLabelRecordIndex_Part1 = CStr(Left(rs("MaxRecord"), 8))
    lastLabelRecordIndex_Part2 = CStr(Mid(rs("MaxRecord"), 9, 4))
    lastLabelRecordIndex_Part3 = CStr(Right(rs("MaxRecord"), 5))
    rs.Close
    db.Close
    
    Dim oldLastLabelRecordIndex As String
    Dim lastLabelRecordIndex As String
    
    oldLastLabelRecordIndex = oldLastLabelRecordIndex_Part1 & oldLastLabelRecordIndex_Part2 & CStr(oldLastLabelRecordIndex_Part3 + 1)
    lastLabelRecordIndex = lastLabelRecordIndex_Part1 & lastLabelRecordIndex_Part2 & lastLabelRecordIndex_Part3
    
    DoCmd.SetWarnings False
    
    DoCmd.OpenReport Report_Labels_SSCC_Gen.Name, acViewPreview, , "Pre_SSCC BETWEEN '" & CStr(oldLastLabelRecordIndex) & "' AND '" & CStr(lastLabelRecordIndex) & "'", acHidden
    Set Report_Labels_SSCC_Gen.Printer = Application.Printers(Me.cbPrintersList.ListIndex)
    'MsgBox Report_Labels_SSCC_Gen.Printer.DeviceName
    DoCmd.OpenReport Report_Labels_SSCC_Gen.Name, , , "Pre_SSCC BETWEEN '" & CStr(oldLastLabelRecordIndex) & "' AND '" & CStr(lastLabelRecordIndex) & "'", acHidden
    DoCmd.Close acReport, Report_Labels_SSCC_Gen.Name, acSaveNo
    
    DoCmd.SetWarnings True
End Sub

And this is the code to populate the combobox with the list of available printers, as soon as the form comes up:

Private Sub Form_Load()
    Dim printerIndex As Integer
    
    For printerIndex = 0 To Application.Printers.Count - 1
        Me.cbPrintersList.AddItem (Application.Printers(printerIndex).DeviceName)
    Next printerIndex
    
    DoCmd.GoToControl "cbPrintersList"
End Sub

Now, according to dozens of articles I've read the whole day, the following bit of code should set the printer I want to print to, but it still keeps sending to the zebra-01 printer:

    DoCmd.OpenReport Report_Labels_SSCC_Gen.Name, acViewPreview, , "Pre_SSCC BETWEEN '" & CStr(oldLastLabelRecordIndex) & "' AND '" & CStr(lastLabelRecordIndex) & "'", acHidden
    Set Report_Labels_SSCC_Gen.Printer = Application.Printers(Me.cbPrintersList.ListIndex)
    'MsgBox Report_Labels_SSCC_Gen.Printer.DeviceName
    DoCmd.OpenReport Report_Labels_SSCC_Gen.Name, , , "Pre_SSCC BETWEEN '" & CStr(oldLastLabelRecordIndex) & "' AND '" & CStr(lastLabelRecordIndex) & "'", acHidden
    DoCmd.Close acReport, Report_Labels_SSCC_Gen.Name, acSaveNo

Can anyone explain to me what am I missing or doing wrong?

As a reference, here's what's happening before and after the printer is set to the report.printer property:

Before setting the printer

After setting the printer

You can clearly see from the debug that the report has its printer property set to the printer I've chosen from the combobox.

However, for some reason I cannot understand, right after that line of code, when running the OpenReport to print the labels, it prints to zebra-01 printer instead...

What if If availablePrinters.DeviceName = selectedPrinter Then is never true (eg, because the printer Me.cbPrintersList.Text does not exist in availablePrinters? Then you will never Set Application.Printer and it will print on the wrong printer. • Go through your code step by step (using F8) at the code I mentioned and analyze this part and the variables values in each step of the loop. Check if Set Application.Printer runs at all. — Pᴇʜ
Well that wouldn't make sense, because on the form's load event the available printers are loaded into the combobox. The user is presented with that list and selects from the combobox to which printer to print to, so at a given time that If statement will be true. The only moments for that If statement to never be true, would be if either the combobox wasn't populated or if the user didn't find the required printer and in any case there would be no point to even print. — Simbiose
Also, I forgot to mention, if you pay atention to the MsgBox Report_Labels_SSCC_Gen.Printer.DeviceName that's commented, at this given moment, the printer that's set for the report is actually the printer selected from the combobox which is different from zebra-01 (in this specific case we want to print for the Zebra - Azambuja Sul plataforma printer, which on debug is the name that appears for the Report_Labels_SSCC_Gen.Printer.DeviceName property. — Simbiose
@Pᴇʜ I did debug the whole code, that bit of code you mention had no errors and is running through the whole list of printers until it finds the one I choose, which in this case it's printer nr.12 or list index = 11 of a total of 22 printers. — Simbiose
Where your 'MsgBox Report_Labels_SSCC_Gen.Printer.DeviceName is can you check what MsgBox Application.Printer.DeviceName shows at this position there? — Pᴇʜ

Erik A Erik A · Accepted Answer · 2020-10-14T16:36:45

Personally, I use the following code to print reports to a specified printer:

DoCmd.OpenReport "SomeReport", acViewPreview
Set Reports("SomeReport").Printer = Application.Printers("SomePrinter")
DoCmd.SelectObject acReport, "SomeReport"
DoCmd.PrintOut
DoCmd.Close

This only works with reports that are not printing to the default printer, so first open up the report in design view, go to page setup, and choose Use specific printer, then pick a printer (any printer), to make sure the report won't be printed on the system default printer.

If you want to specify paper size and bin, you can do so after setting the report printer:

DoCmd.OpenReport "SomeReport", acViewPreview
Set Reports("SomeReport").Printer = Application.Printers("SomePrinter")
    Reports("SomeReport").Printer.PaperSize = 1
    Reports("SomeReport").Printer.PaperBin = 2
DoCmd.SelectObject acReport, "SomeReport"
DoCmd.PrintOut
DoCmd.Close

Getting the right numbers for the paper and bin numbers is a bit complicated, however. The process is outlined here: https://docs.microsoft.com/en-us/office/vba/access/concepts/printing/programmatically-retrieve-printer-capabilities

Access Not Printing to the Printer Chosen

Before setting the printer

After setting the printer

1 Answers