Printing a report to two trays (1 Viewer)

I am trying to place code into my on click event of my print report button that will print a report to letterhead and plain paper. Ther are two drawers on the printer one is for letter and the other is for letterhead. Listed below is my code for accomplishing this task. One problem I have is that Access is looking for an end sub right away. It's almost like it does not recognize the function. Any help would be greatly appreciated.



Private Sub Print_Invoice_OnClick()


Function twotrayprinting(WAMInvoice) As Integer
'
'Note: The report gets run twice, once for each tray.
'
'Returns: TRUE = Success; FALSE=Error
'
Const MAX_PAGES = 999

'Open the report in DESIGN view
On Error GoTo TTP_Error
DoCmd.Echo False
DoCmd.OpenReport WAMInvoice, A_DESIGN

'Switch to upper tray and print first copy
SetReportTray Reports(WAMInvoice), R_UPPR_Tray
DoCmd.PrintOut A_PAGES, MAX_PAGES


'Switch to lower tray and print second copy
SetReportTray Reports(WAMInvoice), R_Lower_tray
DoCmd.PrintOut A_PAGES, MAX_PAGES

'Close the report
DoCmd.SetWarnings False
DoCmd.Close A_REPORT, WAMInvoice
DoCmd.SetWarnings True
DoCmd.Echo True
'Twotrayprinting = true

TTP_exit:
Exit Function

TTP_Error:
'Twotrayprinting=false
DoCmd.Echo False 'restore screen echo
Resume TTP_exit



End Function
End Sub

You are trying to create two functions in one, I've never used the function exampled here, but I suspect it belongs it a stand alone module and you have to Call it

Function twotrayprinting(WAMInvoice) As Integer
Const MAX_PAGES = 999
On Error GoTo TTP_Error
DoCmd.Echo False
DoCmd.OpenReport WAMInvoice, A_DESIGN
SetReportTray Reports(WAMInvoice), R_UPPR_Tray
DoCmd.PrintOut A_PAGES, MAX_PAGES
SetReportTray Reports(WAMInvoice), R_Lower_tray
DoCmd.PrintOut A_PAGES, MAX_PAGES
DoCmd.SetWarnings False
DoCmd.Close A_REPORT, WAMInvoice
DoCmd.SetWarnings True
DoCmd.Echo True
TTP_exit:
Exit Function

TTP_Error:
DoCmd.Echo False 'restore screen echo
Resume TTP_exit
End Function

Private Sub Print_Invoice_OnClick()
Call twotrayprinting
End Sub

Rich,

Thanks for your reply!!!
I will try what you suggested. In the mean time, are there other ways of accomplishing the same task?

I have used this same code and it worked for Windows XP. We have just switched over to Windows Vista and Office 2007. After the switchover, we cannot get our reports to print to tray 2 using the code. Any help would be greatly appreciated.

G

Printing in Vista over a network takes a couple of extra steps. It would be highly advisable to upgrade your machines to XP from Vista so that everything works like the way it used to.

Eer is a simpler way just to copy the report call it invoiceCopy
and set the printer up and the tray up different from the first report then have the print button do both prints ???

(PS just an idea- so don't shoot me down in flames if its a daft one)

g

Thanks for the reply. I performed more testing on a Windows XP/Office 2003 and am now having the same problem. (The first page will not print from tray 2 - it all prints from tray 1) I originally set up my program to run on Access 2002. Everything worked fine until I upgraded my computers. Below is the code that I use:

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

Sub setPaperSource4a(rptName As String)
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 "Reprint cert report official", acViewDesign, , "Invoice_no = " & INVOICE_NO
Set rpt = Reports("[Reprint cert report official]")
DevModeExtra = rpt.PrtDevMode
DevString.RGB = DevModeExtra
LSet DM = DevString
DM.dmDefaultSource = 2 '1 = Upper Tray, 2 = Lower Tray, 5 = _
Envelope Feeder
LSet DevString = DM
Mid$(DevModeExtra, 1, 68) = DevString.RGB
rpt.PrtDevMode = DevModeExtra
DoCmd.Save acReport, "Reprint cert report official"
DoCmd.SelectObject acReport, "Reprint cert report official", True
DoCmd.PrintOut acPages, 1, 1, Copies, 1
DoCmd.Close acReport, "reprint cert report official"

' Set Paper Tray for page 2
DoCmd.OpenReport "Reprint cert report official", acViewDesign
Set rpt = Reports("[Reprint cert report official]")
DevModeExtra = rpt.PrtDevMode
DevString.RGB = DevModeExtra
LSet DM = DevString
DM.dmDefaultSource = 1 '1 = Upper Tray, 2 = Lower Tray, 5 = _
Envelope Feeder
LSet DevString = DM
Mid$(DevModeExtra, 1, 68) = DevString.RGB
rpt.PrtDevMode = DevModeExtra
DoCmd.Save acReport, "Reprint cert report official"
DoCmd.SelectObject acReport, "Reprint cert report official", True
DoCmd.PrintOut acPages, 2, 20, Copies, 1
DoCmd.Close acReport, "reprint cert report official"

DoCmd.SetWarnings True
End Sub

Like I said before, this code used to work before upgrading our computers. Any help would be greatly appreciated.

I know this thread is almost 12 years old but in case it helps anyone, here I am posting an intended solution, assuming I'm understanding the issue correctly ...

My client needed a particular multi-copy form to print out of a special tray on her multi-tray printer. I couldn't, from within MS Access VBA, control the tray selection. That's not the only report for which she uses this printer; for her other reports, normal paper is needed.

So, her clever server technician set up a redundant printer driver. It points to the same printer but the default tray thereon is the special tray. I am indeed able to change the default printer from within VBA, using "Set Application.Printer=" ... so I change it to the name used for the redundant printer driver. That sends the report to the special printer driver with special tray, and when the report is done printing, I switch the default printer back to what the value was previously. It been working well.

I hope someone finds this useful.