Select printer paper tray using VBA

[wackywoo105]

I have a Brother MFC-J6920DW Printer. It has 2 main paper trays. I am trying to select each one for various reports. No matter what I do it always just takes paper from tray/bin 1.

Set Application.Printer = Application.Printers("Brother MFC-J6920DW Printer")
MsgBox Application.Printer.DeviceName
Application.Printer.PaperBin = 15
MsgBox Application.Printer.PaperBin
DoCmd.OpenReport "MyReport", printorpreview, , _
    "[RepNo] = " & Me![RepNo]

Can anyone help me with what I am doing wrong? I have used most of the paperbin values from this list but it still always pulls from tray 1.

https://msdn.microsoft.com/en-us/li...nting.compatibility.vb6.printer.paperbin.aspx

I read a query from someone using a HP printer who had to use figures such as 257 etc. If the 1-14 range doesn’t work with my brother printer how do I find out which numbers will?

I am considering duplicating the printer in control panel, having different settings assigned to each duplicate and calling the one I need. I would however rather not do this if at all possible.

 

[wackywoo105]

I spoke to brother tech support. They suggested it is not possible as the printer is GDI rather than postscript. I have no idea why this would affect paper tray selection. I though it was how printouts were sent to the printer. Surely if I can choose different trays using printer properties this feature can be accessed in VBA.

 

[AccessBlaster]

Maybe within the printer's configuration it's set (forced) to print from tray 1. There might be an auto setting or something else, that could effect your issue. Just guessing.

 

[JHB]

Here is a link to a page in which someone made a small program, (a little more as half way down the page), maybe you can get some ideas from it then it seems to work by my Brother printer.
https://bytes.com/topic/access/answers/794305-paper-bin

 

[wackywoo105]

Well that's frustrating. The example printer.mdb does show my available paper bins. The problem is that selecting any of them still results in the use of tray 1.

 

[JHB]

What if you first show the report in Print Preview and then select the "Page Setup" from the ribbon, are you then able to printout from another tray?
Is it here you've tried to change it, (1-14)?

Application.Printer.PaperBin = 15

If you post a stripped down version of your database with some sample data, I could try if I'm able to print your report out from another tray. Zip it + a description how to open your report.

 

[wackywoo105]

That bit of code is where I tried to change it. MS site only gives numbers 1-14. I have a HP printer with only 1 tray that for some reason is set to 15, so I guess other number are available.

I can manually select tray 2 with a preview report and it prints from tray 2.

When I open the printer in control panel it is set to auto. If I go into properties through access it is set to tray 1, so it seems tray setting stick.

I will have a go at sending my code over.

I am also going to see if I can use the example code to write something that cycles through the different paperbin numbers and check if/when tray 2 has been set and if so at what number.

 

[wackywoo105]

I used printer.mdb and manually set the paper tray to tray 2. I then queried the printer number and it came up as 9.

Adding a select case for "Tray2" and setting it to 9 still doesn't bring about use of tray 2 though.

 

[wackywoo105]

Ok I can select tray 2 using code. I have tried various iterations of the below code. I now know that print settings have to be saved to the report itself. I have done this manually and got it to work. I am now trying with VBA.

Dim iSelectedPrn As String
Dim strReport As String
Dim strCrit As String
Dim rpt As Report
    
strReport = "MyReport"
strCrit = "[RepNo] = " & Me![RepNo]

 iSelectedPrn = "Brother MFC-J6920DW Printer"
    
DoCmd.OpenReport strReport, acViewDesign, , "[RepNo] = " & Me![RepNo], acHidden
  
 Set rpt = Reports("MyReport")
 With rpt
        Set rpt.Printer = Application.Printers(iSelectedPrn)
        .Printer.PaperBin = 2
       '  DoCmd.Close acReport, strReport, acSaveYes
         DoCmd.OpenReport strReport, acViewNormal, , _
        "[RepNo] = " & Me![RepNo]
 End With

The problem I have is this tries to print out every ID in the entire database instead of the one I am on. At least it does so using tray 2. If I enable the

DoCmd.Close acReport, strReport, acSaveYes

line it then prints 1 copy but to tray 1 again.

Does anyone know how to modify this code so only 1 report is printed but still from tray 2?

 

[wackywoo105]

Got there as below. Just tried with the hidden property and it worked.

(And managed to delete my original post in process :/)

 

[Gasman]

DoCmd.OpenReport "ReportName", acViewDesign, , , acHidden

from http://www.pcreview.co.uk/threads/open-report-design-view-as-hidden.2217543/