Access Not Printing to the Printer Chosen

[Simbiose]

Hello everyone.


I hope I'm posting on the right place.

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

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...

After digging deeper and deeper into this, it seems that the possible way to get around this is force the report ]Report_Labels_SSCC_Gen to use the default printer and via design for some reason it's ignoring that definition so I'm decided to write the following procedure:

Sub RptPrntSetDef()    On Error GoTo Error_Handler
    Dim db               As DAO.Database
    Dim DbP              As Object
    Dim DbO              As AccessObject
 
    Set db = CurrentDb
    DoCmd.SetWarnings False
    Debug.Print "RptPrntSetDef Begin"
    Debug.Print "================================================================================"
    'Check Reports
    Set DbP = Application.CurrentProject
    For Each DbO In DbP.AllReports
        DoCmd.OpenReport DbO.Name, acDesign
        If Reports(DbO.Name).Report.UseDefaultPrinter = False Then
            Debug.Print "Editing Report '" & DbO.Name & "'"
            Reports(DbO.Name).Report.UseDefaultPrinter = True
            DoCmd.Close acReport, DbO.Name, acSaveYes
        Else
            DoCmd.Close acReport, DbO.Name, acSaveNo
        End If
    Next DbO
    Debug.Print "================================================================================"
    Debug.Print "RptPrntSetDef End"
 
Error_Handler_Exit:
    On Error Resume Next
    DoCmd.SetWarnings True
    Set DbO = Nothing
    Set DbP = Nothing
    Set db = Nothing
    Exit Sub
 
Error_Handler:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: RptPrntSetDef" & vbCrLf & "Error Description: " & _
    Err.Description, vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit

End Sub

This procedure will then be called on the form's load event, which I already pointed out above.

Now, since I'm using the default printer, I need to have a way to set the paper size for the labels I want to print, so I had to find around the web the code that would give me custom papersizes and I found the following link:

https://binaryworld.net/Main/CodeDet...px?CodeId=3093

I copied the code from that link and pasted it into the report's module and I'm trying to run the SetPrinterCustomPaperSize function right after the opening the report, but for some reason I can't understand, the variable mhDCPrinter is always 0, therefor it will never change any settings for the printer. From my understanding this is telling me that the report isn't actually using the default printer.

This is where I'm at a loss.Can anyone help me?

[Welshgasman]

I can't read all that code with the HTML tags all in it?
Did you look at your post before posting?

See if the example here helps, though I would have thought you would be changing the printer not just using the default printer?

https://docs.microsoft.com/en-us/off...inter-settings

[Simbiose]

I can't read all that code with the HTML tags all in it?
Did you look at your post before posting?

See if the example here helps, though I would have thought you would be changing the printer not just using the default printer?

https://docs.microsoft.com/en-us/off...inter-settings

Sorry about that. I've fixed it just now.
About the link you suggested, I've already been there and it doesn't work for me. For some weird reason gives me an error, something like "Invalid Automation" when I run the code expression .UseDefaultPrinter.
However, if you check RptPrntSetDef() procedure, I'm able the set the usedefaultprinter of the report to true (kind of a bruteforce...).

Also the printer settings that page has available won't help as it gives only the pre-determined paper sizes that come as a default, such as 'letter', 'A4', 'A5' and so on. That's why I'm using the code from the link https://binaryworld.net/Main/CodeDet...px?CodeId=3093.

[Welshgasman]

I cannot see how that binary code works.?
I've just copied it into a nw module and it balks as there is a Me. reference in it.?

I would create a small block of code and get that working then apply it to your DB.
Start off small then build it from there.

[Simbiose]

I cannot see how that binary code works.?
I've just copied it into a nw module and it balks as there is a Me. reference in it.?

I would create a small block of code and get that working then apply it to your DB.
Start off small then build it from there.

Just to be sure, what do you mean by "I've just copied it into a nw module"?

Did you create a new module and paste the code there? If so, it won't work. The code needs to be integrated with a form or report module.

Also, what do you mean by creating a small block of code? I'm not sure how much simpler I can go with the current code I've got :|

At the moment, all I have is the following:
user pick a printer from the list (combobox);
indicates the number of labels to generate and print (type the number into textbox)
press the print button

[Welshgasman]

Yes, I did exactly that.
I do not think it is practical to put that code into every form/report you have?, though now I see where the Me. comes from.

For a start can you list the functions as you call them.? Get some idea as to the process.?
I also got a display that mhDCPrinter was zero, but there is SO MUCH code there, at the moment I cannot see the wood for the trees.

Have you used Function CreatePrinterDC() at all ?

It would help in that link if it explained how it is meant to be used
What to set and what to call to start the process.

You say you do not know how simpler you can make it, but you have a wealth of functions there, that you do not know how they ineract. ?
That is where I would start, line by line with F8 or variations of F8 and another key.

[Welshgasman]

Expression refers to an object like Me ?

Try Me.UseDefaultPrinter

[Simbiose]

Ok, I see what you mean and I did some thinking about it too.
This is the list of functions:

• isWin9X
• GetPrinterOrientation
• SetPrinterCustomPaperSize
• SetPrinterOrientation
• ByteToString
• PtrCtoVbString
• CreatePrinterDC
• GetDEVMODE
• LoadPrinterData
• PrivateSetPrinterCustomPaperSize
• StripNulls

Out of the list above only following are public and, therefor, can be directly called from the form:

• isWin9X
• GetPrinterOrientation
• SetPrinterCustomPaperSize
• SetPrinterOrientation

The other functions are private, therefor I cannot use them. I assume these are handled by the printer API itself? This is also what I'd like to know, because as you stated using the CreatePrinterDC() function could possible be the way to go, but I don't see how...

[Simbiose]

Expression refers to an object like Me ?

Try Me.UseDefaultPrinter

Yep, I do know that and it returns the error I posted.

[Welshgasman]

Expression refers to an object like Me ?

Try Me..UseDefaultPrinter

I am trying to use this solution, and it balking when I try and change the printer, saying Error 5, Invalid Procedure Call or Argument

https://social.msdn.microsoft.com/Fo...orum=accessdev

Got it working using combo listindex and not the value

[Welshgasman]

I meant you have to set something to it or use ? in the immediate window or in code.
That last link allowed me to change the printer for a report, print it (I did not really) and then change it back to my default system printer.

[Welshgasman]

No, I meant add some code to see the stack of the modules, so A calls B, which calls D, and then C. that sort of logic.?

Ok, I see what you mean and I did some thinking about it too.
This is the list of functions:

• isWin9X
• GetPrinterOrientation
• SetPrinterCustomPaperSize
• SetPrinterOrientation
• ByteToString
• PtrCtoVbString
• CreatePrinterDC
• GetDEVMODE
• LoadPrinterData
• PrivateSetPrinterCustomPaperSize
• StripNulls

Out of the list above only following are public and, therefor, can be directly called from the form:

• isWin9X
• GetPrinterOrientation
• SetPrinterCustomPaperSize
• SetPrinterOrientation

The other functions are private, therefor I cannot use them. I assume these are handled by the printer API itself? This is also what I'd like to know, because as you stated using the CreatePrinterDC() function could possible be the way to go, but I don't see how...

[davegri]

Here's a bare bones approach with all the non-specific code deleted. This works fine in my test environment.

    Dim availablePrinters As Printer    Dim selectedPrinter As String
    
    DoCmd.GoToControl "cbPrintersList"
    
    selectedPrinter = Me.cbPrintersList
    
    For Each availablePrinters In Application.Printers
        If availablePrinters.DeviceName = selectedPrinter Then
            Set Application.Printer = availablePrinters
            Exit For
        End If
    Next availablePrinters
    Debug.Print selectedPrinter
    DoCmd.OpenReport "rptShoes", acViewDesign
    Set Reports!rptShoes.Printer = Application.Printers(Me.cbPrintersList.ListIndex)
    MsgBox Reports!rptShoes.Printer.DeviceName
    DoCmd.OpenReport "rptShoes", acViewPreview

[Simbiose]

Ok... first of all, I want to thank everyone that tried to help me. I believe I found the problem...
The articles I've read, the forums I've consulted, plus the suggestions here are correct and apparently have always been...
When I created the report via design mode I did it based on the zebra-01 printer, meaning, when I created the report the very first thing I did was selected that specific printer, so, from my understanding, what happened is that I linked that report to that printer and no matter what I did, code-wise, to change the printer, it would never change for the report.
Here's what I've done:

1. Deleted the report
2. Created a blank report (without specifying the printer) and setup the layout for the labels
3. Tested the form for two printers and now it's printing for whichever I choose

Now, however I've got another problem...
From my understanding, since the report isn't linked to any printer (per say), I believe the report's target printer is the windows default printer (which apparently has the A4 page size), so when I print the labels, it's not well formatted for the 8cm x 4.7 cm paper size.
Now, I know that there's no pre-determined paper size for those dimensions, so I'm assuming I need to type in some code to get them and this is where I hit another wall, because I don't have access to the printer's paper size dimension properties (such as width and height).
However, that binary link I've shared on the op might be the way. Yet again, I'm not sure how I'm going to access those properties from there.
I'll update this if I've found the solution or simply can't get past this.

[Welshgasman]

I believe you do have access to paper width and height? I am sure I saw those properties when I was trying it our last night.

Also confirmed here https://www.engram9.info/visual-basi...ollection.html

I would have thought your labels would be associated with a defined page size, like Avery labels.?

Ok... first of all, I want to thank everyone that tried to help me. I believe I found the problem...
The articles I've read, the forums I've consulted, plus the suggestions here are correct and apparently have always been...
When I created the report via design mode I did it based on the zebra-01 printer, meaning, when I created the report the very first thing I did was selected that specific printer, so, from my understanding, what happened is that I linked that report to that printer and no matter what I did, code-wise, to change the printer, it would never change for the report.
Here's what I've done:

1. Deleted the report
2. Created a blank report (without specifying the printer) and setup the layout for the labels
3. Tested the form for two printers and now it's printing for whichever I choose

Now, however I've got another problem...
From my understanding, since the report isn't linked to any printer (per say), I believe the report's target printer is the windows default printer (which apparently has the A4 page size), so when I print the labels, it's not well formatted for the 8cm x 4.7 cm paper size.
Now, I know that there's no pre-determined paper size for those dimensions, so I'm assuming I need to type in some code to get them and this is where I hit another wall, because I don't have access to the printer's paper size dimension properties (such as width and height).
However, that binary link I've shared on the op might be the way. Yet again, I'm not sure how I'm going to access those properties from there.
I'll update this if I've found the solution or simply can't get past this.