Print a report in a specified printer tray

[lekkala]

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]

Could you please show us what you tried, makes it much easier .....

 

[lekkala]

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

 

[bastanu]

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.

 

[bastanu]

Could you please try this? Add the code to a new standard module then call the function fnPrintReport_with_PaperSource("Datasheet_Smooth",2)

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]

Its working now. Thank you so much, really appreciate your help!!!

 

[bastanu]

You're very welcome, good luck with your project!
Cheers,

 

[arnelgp]

actually your original code will work if you did not Comment out most of your code.