Programmatically select the printer tray in Microsoft Access

Loic Joachim 6 Reputation points
2021-10-03T21:14:51.673+00:00

I have been trying to programmatically select the printer tray to print on in Microsoft Access however no matter what I try it always seems to print on the default tray.

  • I have tried every iteration of the printer settings when it comes to tray selection
  • I have tried many ways to do it in code such as changing the printer.paperbin to every tray number between 1-200
  • Using the following project (printer.accdb) I can select the tray but it always prints to the default regardless

If I go to the report in Access and select "Page Setup" I can select the tray there and that does work. However unless I create a copy of the report for every tray and manually do the page setup through the GUI it does not seem possible to solve this issue at the moment. Also this option is not feasible for me because of the sheer number of printers and trays I need to work with.

My printers are Konica Minolta bizhub C558's.

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
818 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. C Data 6 Reputation points
    2021-10-05T21:10:26.98+00:00

    I've seen code to change/select a printer that is not the OS default - - but not the tray. So I don't know. Can you add that printer a 2nd time with the desired tray as the printer's default tray? .... and then in Access select the 2nd printer.

    Here is some info I harvested that may help:

    PrinterExists: A function that checks if there is a printer installed with the given name.
    IsDefaultPrinter: A function that checks if the given printer corresponds to the default Windows printer.
    SetDefaultPrinter: A functions that set the given printer to be the default Windows printer.
    GetInstalledPrinters: loops through all the installed printers of the computer and writes their names in the “Printers” worksheet. Moreover, it checks if each printer is the default one or not.
    SetAsTheDefaultPrinter: if it corresponds to an installed printer, to be the default Windows printer. The user must select a range within the given range of (valid) printers, and, then, run the macro.

    Code samples are here
    https://myengineeringworld.net/2018/07/get-set-default-printer-vba.html

    0 comments
  2. Loic Joachim 6 Reputation points
    2021-10-07T00:59:47.773+00:00

    I just finished testing this, I setup different printers on the print server with each one set to a different tray from the same printer for its default. Then I tried to use printer.accdb to select each of these printers but it continues to still just pick the first value from the list that is provided by the printer (in my case "Auto"), ignoring the settings I set up in Windows before that.

    So I tried setting the default for each printer through Access's "Print Preview" window as I noticed it wasn't pulling the defaults I had previously set through the Windows Print interface. But as soon as I tried to select the printer through code it always stayed the same. It seems like through code I am not able to change the printer or tray that is being used at all. It always just picks the default.

    Are you able to replicate this behaviour when you test? It seems strange that the functionality is so completely broken in every way.

  3. C Data 6 Reputation points
    2021-10-07T12:22:45.673+00:00

    I do not have the infrastructure to test...

    there may be an ability to set up Access's default printer to differ from the OS - I'm not aware of any way to do so permanently, but here's some code from Ken Sheridan: the module of a dialogue form used to temporarily change the printer used by Access by selecting one from a list box of all installed printers:

    Private Sub cmdCancel_Click() 

    Set Application.Printer = Nothing
DoCmd.Close acForm, Me.Name

    End Sub

    Private Sub cmdOK_Click() 

    Dim ctrl As Control
Set ctrl = Me.lstPrinters

If ctrl = "<Windows Default Printer>" Then
    Set Application.Printer = Nothing
Else
    Set Application.Printer = _
        Application.Printers.Item(ctrl.Value)
End If

DoCmd.Close acForm, Me.Name

    End Sub

    Private Sub Form_Open(Cancel As Integer) 

    Dim prnt As Printer
Dim ctrl As Control

Set ctrl = Me.lstPrinters

ctrl.RowSourceType = "Value List"

ctrl.AddItem "<Windows Default Printer>"

For Each prnt In Printers
    ctrl.AddItem prnt.DeviceName
Next prnt

ctrl = "<Windows Default Printer>"

    End Sub

    Ken Sheridan, Stafford, England

    0 comments