Print, Page Down, Print, Page Down ???

[Gwen H]

I've posted a question about this issue before, but received limited
response, so I'm trying again. I have a fully functional VBA module that
prints a series of reports to PDF. I would like to add another feature to it.
The module prints about 10 different reports to about 40 different PDF files.
One report, the code for which I am pasting below, consists of three pages.
Each page is the sales incentive worksheet for one banker. So, page one is
Alene's worksheet, page two is Don's worksheet, and page three is John's
worksheet. Each banker's name is a field at the top of each page of the
report.

Currently the VBA module I've written opens the report, prints page 1, then
prints page 2, then prints page 3. What code do I need to add so that it
opens the report, finds Alene's sheet, prints Alene's sheet to PDF, then
finds Don's sheet, prints it to PDF, then finds John's sheet, prints it to
PDF, and then closes the report? What I am trying to ensure does not happen
is that Alene's sheet doesn't get printed to Don's file, and so on. The
current VBA module is only looking at page numbers, not at whose name is at
the top of the report. If we were to delete Don from the data on which the
report is based, then John's worksheet becomes page 2 and gets printed to
Don's file.

Am I making sense? Here's the code I have now:

'Print the Commercial Banker-Large Market Worksheets.
DoCmd.OpenReport "Commercial Banker Incentive Worksheet-Large Market",
acViewPreview
MsgBox "Save As ""Alene Worksheet Month 2006""", vbOKOnly, "Alene's
Worksheet"
DoCmd.PrintOut acPages, 1, 1, acHigh, 1, True
MsgBox "Save As ""Don Worksheet Month 2006""", vbOKOnly, "Don's Worksheet"
DoCmd.PrintOut acPages, 2, 2, acHigh, 1, True
MsgBox "Save As ""John's Worksheet Month 2006""", vbOKOnly, "John's
Worksheet"
DoCmd.PrintOut acPages, 3, 3, acHigh, 1, True
DoCmd.Close acReport, "Commercial Banker Incentive Worksheet-Large Market"

And here's how I want to modify the code:

'Print the Commercial Banker-Large Market Worksheets.
DoCmd.OpenReport "Commercial Banker Incentive Worksheet-Large Market",
acViewPreview
MsgBox "Save As ""Alene Worksheet Month 2006""", vbOKOnly, "Alene's
Worksheet"
GoTo Banker_Name "Alene"
DoCmd.PrintOut currentPage only
MsgBox "Save As ""Don Worksheet Month 2006""", vbOKOnly, "Don's Worksheet"
GoTo Banker_Name "Don"
DoCmd.PrintOut currentPage only
MsgBox "Save As ""John's Worksheet Month 2006""", vbOKOnly, "John's
Worksheet"
GoTo Banker_Name "John"
DoCmd.PrintOut currentPage only
DoCmd.Close acReport, "Commercial Banker Incentive Worksheet-Large Market"

Any help you can give would be greatly appreciated.

GwenH

 

[Brian]

A couple of suggestions here. I'm afraid they're not very sophisticated, but
they might work if none of the MVP's post anything better. Rather than
relying on what appears on a given page, you can:

1. Filter the report before running it to just one banker (using a combo box
to pick the banker) so that only one runs each time, and you can use the
contents of the combo box to identify whose file it is, or
2. Let VBA do the work for you. Set up a loop in VBA that runs the report
once for each banker. You will also want to add a field to the table
containing the bankers' names to hold the file name and/or path of the
report's output.

Either way, I would certainly stay away from hard-coding the names into the
code. For example, if you use #2 above, you could replace this:

MsgBox "Save As ""Don Worksheet Month 2006""", vbOKOnly, "Don's Worksheet"

with this:
'Dim BnkrName as String
'open a recordset on the table containing the list of bankers & start
looping through it, assigning the banker's name in each iteration to the
BnkrName variable so that the code runs once for each banker

MsgBox "Save As " & BnkrName & " Worksheet Month 2006""", vbOKOnly, BnkrName
& "'s Worksheet"
'the rest of your printing code goes here, making sure to filter the report
to the ID of the current banker in your open recordset so that the correct
data is printed for each

 

[Gwen H]

I really appreciate your time in responding to my question. However:

1. I have 10 reports that each have about 4 or 5 different bankers. I don't
know if I want to run reports 40+ times; that would take too long. I'd rather
just open the report and leave it open until I finish saving/printing each
page to PDF.

2. I'm learning VBA; I don't have a clue how to do loops with anything other
than a counter variable. The path for these reports changes each month, so I
can't store the filename and path in a table.

And if I use a form instead of hard-coding the banker names into the VBA
code, then I just have to hard-code the banker names on the form.

GwenH

 

[Douglas J. Steele]

I seem to recall Arvin Meyer telling me about an application he's written
that sounds similar to this. I'll try pinging him off-forum, and see whether
he can offer you some suggestions. However, I suspect it's going to require
semi-advanced VBA.

Arvin: this is in microsoft.public.access.modulesdaovba