Print a report in a specified printer tray (1 Viewer)

lekkala

Registered User.
Local time
Yesterday, 19:09
Joined
Jan 3, 2018
Messages
17
I want to print a report to a specific tray using VBA. Tried using acPRBNLower enumeration datatype to select bottom tray Drawer 2 of my Canon printer, but not working. Can I please get help with the code?
 

bastanu

AWF VIP
Local time
Yesterday, 17:09
Joined
Apr 13, 2010
Messages
1,401
Could you please show us what you tried, makes it much easier .....
 

lekkala

Registered User.
Local time
Yesterday, 19:09
Joined
Jan 3, 2018
Messages
17
1622564675751.png


I got an error Object variable or With block variable not set.
 

bastanu

AWF VIP
Local time
Yesterday, 17:09
Joined
Apr 13, 2010
Messages
1,401
Could you please post the actual code, not a screen shot as we would have to type it all over again? On which line you got the error? If you open the report using acViewNormal you've already sent it to the printer, so changing trays after that is pointless.
Screenshot 2021-06-01 094340.png
 

bastanu

AWF VIP
Local time
Yesterday, 17:09
Joined
Apr 13, 2010
Messages
1,401
Could you please try this? Add the code to a new standard module then call the function fnPrintReport_with_PaperSource("Datasheet_Smooth",2)

Code:
Option Compare Database
Option Explicit

Type zwtDevModeStr
   RGB As String * 94
End Type

Type zwtDeviceMode
   dmDeviceName As String * 16
   dmSpecVersion As Integer
   dmDriverVersion As Integer
   dmSize As Integer
   dmDriverExtra As Integer
   dmFields As Long
   dmOrientation As Integer
   dmPaperSize As Integer
   dmPaperlength As Integer
   dmPaperWidth As Integer
   dmScale As Integer
   dmCopies As Integer
   dmDefaultSource As Integer
   dmPrintQuality As Integer
   dmColor As Integer
   dmDuplex As Integer
   dmResolution As Integer
   dmTTOption As Integer
   dmCollate As Integer
   dmFormName As String * 16
   dmPad As Long
   dmBits As Long
   dmPW As Long
   dmDFI As Long
   dmDRr As Long
End Type

Function fnPrintReport_with_PaperSource(rptName As String,iTray as integer)
   Dim rpt As Report
   Dim dm As zwtDeviceMode
   Dim DevString As zwtDevModeStr
   Dim DevModeExtra As String
      
   DoCmd.SetWarnings False
   ' Set Paper Tray for page 1
   DoCmd.OpenReport rptName, acViewDesign
   Set rpt = Reports(rptName)
   DevModeExtra = rpt.PrtDevMode
   DevString.RGB = DevModeExtra
   LSet dm = DevString
   dm.dmDefaultSource = iTray  '1 = Upper Tray, 2 = Lower Tray, 5 = Envelope Feeder
   LSet DevString = dm
   Mid$(DevModeExtra, 1, 68) = DevString.RGB
   rpt.PrtDevMode = DevModeExtra
   DoCmd.Save acReport, rpt.Name
   DoCmd.OpenReport rptName, acViewNormal ' print it out   
 
   DoCmd.SetWarnings True
  
End Function
 

lekkala

Registered User.
Local time
Yesterday, 19:09
Joined
Jan 3, 2018
Messages
17
Its working now. Thank you so much, really appreciate your help!!!
 

bastanu

AWF VIP
Local time
Yesterday, 17:09
Joined
Apr 13, 2010
Messages
1,401
You're very welcome, good luck with your project!
Cheers,
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:09
Joined
May 7, 2009
Messages
19,169
actually your original code will work if you did not Comment out most of your code.
 

Users who are viewing this thread

Top Bottom