question

LoicJoachim-9150 avatar image
1 Vote"
LoicJoachim-9150 asked CData-3904 answered

Programmatically select the printer tray in Microsoft Access

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.



office-access-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CData-3904 avatar image
0 Votes"
CData-3904 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LoicJoachim-9150 avatar image
0 Votes"
LoicJoachim-9150 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CData-3904 avatar image
0 Votes"
CData-3904 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.