CODEWITHSUNDEEP
ms-accessprintingvbams-access-2007
JCBWS
JCBWS

Reputation: 48

Access 2007 Multiple Report Configurations in VBA

I'm currently working on compiling some code so that I can do the following with two different reports:

During the loop, the job parameter is increased by increments of one (same as the counter) and the report will be printed.

In case I've confused you already, here's some sample code to provide a visual explanation of what I'm currently doing:

Dim NetJobVar As String
Dim counter As Long
NetJobVar = Me.ToJob - Me.FromJob
counter = 0

DoCmd.SetWarnings False
'Query to add first job parameter to local table
Me.JobStatus.Visible = True

Do
    Me.JobStatus.Caption = "Printing " & Me.FromJob + counter & "..."
    'Insert report code here
    'Query to add 1 onto Job stored in local table
    counter = counter + 1
Loop Until counter = NetJobVar + 1

Me.JobStatus.Visible = False

I've gotten everything planned out except for this... I would like to make two different printer configuration windows appear (like when you force the report to print in VBA/Modules) PRIOR to when the report is actually printed in the Do...Loop statement. Otherwise, that could result in countless prompts and very unhappy end users...

Reason why I need two configuration prompts is because we print our Jobs to two different trays (for two different colours of paper).

I've stumbled across a few things... here and here which have helped, but not to the full extent of what I'm trying to accomplish.

Let me know what you guys think!

Upvotes: 0

Views: 863

Answers (1)

Parfait
Parfait

Reputation: 107587

Consider bypassing any needs for a print configuration dialog. Instead, use VBA's Printer object to automatically choose Printer settings including Orientation, PaperSize, and PaperBin (for trays), among others.

Somehow integrate the below script into your loop. I include exhaustive list of PaperBin constants for you to decide which batch of reports are sent to which trays for the colored paper. If you print same report to different trays, simply add lines to choose new paper bin and print report again.

Dim rpt As Access.Report

'Open report with any filters
DoCmd.OpenReport "JobsReport", acPreview, , "Job=" & Me.FromJob + counter
Set rpt = Reports("JobsReport")

'Set the default printer's orientation to portrait/landscape
rpt.Printer.Orientation = acPRORPortrait

'Set the default printer's paper size to letter/legal/tabloid/A3/A4
rpt.Printer.PaperSize = acPRPSLetter

'FIRST TRAY ----------------------------------------------
'Set the default printer's paper bin
rpt.Printer.PaperBin = acPRBNUpper 

'Print Report
DoCmd.OpenReport "JobsReport"

'SECOND TRAY  ----------------------------------------------
'Set the default printer's paper bin
rpt.Printer.PaperBin = acPRBNLower 

'Print Report
DoCmd.OpenReport "JobsReport"

'Constants for PaperBin
'acPRBNUpper = 1  ' Use paper from the upper bin
'acPRBNLower = 2  ' Use paper from the lower bin
'acPRBNMiddle = 3  ' Use paper from the middle bin
'acPRBNManual = 4  ' Wait for manual insertion of each sheet of paper
'acPRBNEnvelope = 5  'Use envelopes from the envelope feeder
'acPRBNEnvManual = 6  ' Use envelopes from the envelope feeder, but wait for manual insertion
'acPRBNAuto = 7  '(Default) Use paper from the current default bin
'acPRBNTractor = 8  ' Use paper from the tractor feeder
'acPRBNSmallFmt = 9  ' Use paper from the small paper feeder
'acPRBNLargeFmt = 10  ' Use paper from the large paper bin
'acPRBNLargeCapacity = 11  ' Use paper from the large capacity feeder
'acPRBNCassette = 14  'Use paper from the attached cassette cartridge
'acPRBNFormSource = 15  ' Use paper from the forms bin

Set rpt = Nothing

Upvotes: 3

Related Questions