VBA Noob ? - Go to Specific Record

G

Gwen H

I'm learning VBA, and I have written a function that essentially opens a
series of reports, prints out each page of the report to the Acrobat
Distiller, and then closes the report (code snippet included at bottom of
this post). How can I use VBA to go to a specific record in the report? Each
report consists of multiple pages. Each page is essentially sales data for
one commercial banker. So, each page needs to be printed to a separate PDF
file. As you can tell from the code below, I'm currently using DoCmd.Printout
to specify the page, but I'd rather use VBA to find the page for a specific
banker, then print just that page to the Acrobat Distiller. The reason for
this is I need to make sure the right page gets printed to the right file. I
don't need any bankers looking at another banker's sales data.

Thanks in advance,
GwenH

'Print the Commercial Banker-Large Market Worksheets.
DoCmd.OpenReport "Commercial Banker Incentive Worksheet-Large Market",
acViewPreview
MsgBox "Save As ""CB Large Market Month 2006""", vbOKOnly, "Lenise
Braziel's Copy"
DoCmd.PrintOut , , , acHigh, 1, True
MsgBox "Now copy that file to Martha Miller's Folder - Click OK When
Finished.", vbOKOnly, "Martha Miller's Copy"
MsgBox "Save As ""Balcer Worksheet Month 2006""", vbOKOnly, "Alene
Balcer's Worksheet"
DoCmd.PrintOut acPages, 1, 1, acHigh, 1, True
MsgBox "Save As ""David Worksheet Month 2006""", vbOKOnly, "Don David's
Worksheet"
DoCmd.PrintOut acPages, 2, 2, acHigh, 1, True
MsgBox "Now Copy Don's Worksheet to Alene's Folder - Click OK When
Finished.""", vbOKOnly, "Alene's Copy of Don's Worksheet"
MsgBox "Save As ""Burks Worksheet Month 2006""", vbOKOnly, "Steve Burks'
Worksheet"
DoCmd.PrintOut acPages, 3, 3, acHigh, 1, True
DoCmd.Close acReport, "Commercial Banker Incentive Worksheet-Large Market"
 
G

Gary Miller

Gwen,

The 'OpenReport' command has a parameter for a WHERE clause
that will let you specify a criteria for the report. Help
should give you the details.
 
G

Gwen H

Yes, I investigated that already. I need to locate the page for one banker,
print that page to the Acrobat Distiller, then find the page for another
banker, print that page to the Acrobat Distiller, and etc. I don't want to
have to open and close the report each time I need to find the page for a
different banker.
 
G

Gary Miller

Gwen,

It seems to me that you need to create some type of
selection list for what bankers are getting reports. One of
the problems with hard coding your bankers into your code as
you have done so far is that at some point those bankers are
going to change and then you would have to change your VBA
code. Also you need to consider the possibility that your
data may even change enough to shift pages of the report.

One approach would be to populate a listbox with your
bankers and use the multi-select feature to pick the ones
you want to send the reports to. You could then do a loop in
code picking out all of the 'ItemsSelected' in the listbox
to loop through printing the report for each banker in the
list. You could add fields for each to specify save
directories and report names as well and then just refer to
those in your code. Something like...

Dim varItem as Variant

For each varItem in Me!cboBankers.ItemSelected
DoCmd.OpenReport "Commercial Banker Incentive
Worksheet-Large Market",,,"[BankerID] = " &
Me!cboBankers.Column(0, varItem)
' Do whatever is needed to be done before the next one
prints
Next item

This code looks for a BankerID as the first column in the
listbox. Note that I did not use the ViewPreview as you
shouldn't need to open it for preview. You could put that
back in if you need.

If you are sending one to all bankers, you could skip the
listbox and just do a loop through your banker table.
 
G

Gwen H

Your suggestion would not work well for this situation. First, I have 40+
reports that I generate each month, all of which must be exported to PDF
files a page at a time. Second, I am currently using a VBA function to do
this; when I leave the bank, no one else will be using the VBA function.
Third, I don't mind changing the code when a banker leaves or a new one is
hired; it doesn't happen that often. For me, using the VBA function alone is
the simplest and least labor-intensive approach.

So, is there a way to do the following in VBA?

Open the report
Go to John Doe's page (the pages are not going to become longer, BTW)
Print John Doe's one page report to the Acrobat Distiller
Go to Jane Doe's page
Print Jane Doe's one page repot to the Acrobat Distiller
(Repeat the preceding two lines of code as needed)
Close the report

Thanks!

Gary Miller said:
Gwen,

It seems to me that you need to create some type of
selection list for what bankers are getting reports. One of
the problems with hard coding your bankers into your code as
you have done so far is that at some point those bankers are
going to change and then you would have to change your VBA
code. Also you need to consider the possibility that your
data may even change enough to shift pages of the report.

One approach would be to populate a listbox with your
bankers and use the multi-select feature to pick the ones
you want to send the reports to. You could then do a loop in
code picking out all of the 'ItemsSelected' in the listbox
to loop through printing the report for each banker in the
list. You could add fields for each to specify save
directories and report names as well and then just refer to
those in your code. Something like...

Dim varItem as Variant

For each varItem in Me!cboBankers.ItemSelected
DoCmd.OpenReport "Commercial Banker Incentive
Worksheet-Large Market",,,"[BankerID] = " &
Me!cboBankers.Column(0, varItem)
' Do whatever is needed to be done before the next one
prints
Next item

This code looks for a BankerID as the first column in the
listbox. Note that I did not use the ViewPreview as you
shouldn't need to open it for preview. You could put that
back in if you need.

If you are sending one to all bankers, you could skip the
listbox and just do a loop through your banker table.

--
Gary Miller
Sisters, OR



Gwen H said:
Yes, I investigated that already. I need to locate the
page for one banker,
print that page to the Acrobat Distiller, then find the
page for another
banker, print that page to the Acrobat Distiller, and etc.
I don't want to
have to open and close the report each time I need to find
the page for a
different banker.
Click to expand...
Click to expand...
 
G

Gary Miller

Gwen,

I have done automated PDF generation from Access in the past
for a client. After a bit of research we decided that a
third party addin, ACG's PDF Pro Mail Library 10.0, was
worth the pretty reasonable investment for how well it could
automate the process, especially the naming of the PDF files
and batch situations. Here is a link for that...

http://ourworld.compuserve.com/homepages/attac-cg/ACGPDF.htm

Also Tony Toews has a page that lists quite a few resources
on PDF printing from VBA and Access. Here is that link....

http://www.granite.ab.ca/access/pdffiles.htm

Those should give you a bit to work with.

--
Gary Miller
Sisters, OR



Gwen H said:
Your suggestion would not work well for this situation.
First, I have 40+
reports that I generate each month, all of which must be
exported to PDF
files a page at a time. Second, I am currently using a VBA
function to do
this; when I leave the bank, no one else will be using the
VBA function.
Third, I don't mind changing the code when a banker leaves
or a new one is
hired; it doesn't happen that often. For me, using the VBA
function alone is
the simplest and least labor-intensive approach.

So, is there a way to do the following in VBA?

Open the report
Go to John Doe's page (the pages are not going to become
longer, BTW)
Print John Doe's one page report to the Acrobat Distiller
Go to Jane Doe's page
Print Jane Doe's one page repot to the Acrobat Distiller
(Repeat the preceding two lines of code as needed)
Close the report

Thanks!

Gary Miller said:
Gwen,

It seems to me that you need to create some type of
selection list for what bankers are getting reports. One
of
the problems with hard coding your bankers into your code
as
you have done so far is that at some point those bankers
are
going to change and then you would have to change your
VBA
code. Also you need to consider the possibility that your
data may even change enough to shift pages of the report.

One approach would be to populate a listbox with your
bankers and use the multi-select feature to pick the ones
you want to send the reports to. You could then do a loop
in
code picking out all of the 'ItemsSelected' in the
listbox
to loop through printing the report for each banker in
the
list. You could add fields for each to specify save
directories and report names as well and then just refer
to
those in your code. Something like...

Dim varItem as Variant

For each varItem in Me!cboBankers.ItemSelected
DoCmd.OpenReport "Commercial Banker Incentive
Worksheet-Large Market",,,"[BankerID] = " &
Me!cboBankers.Column(0, varItem)
' Do whatever is needed to be done before the next
one
prints
Next item

This code looks for a BankerID as the first column in
the
listbox. Note that I did not use the ViewPreview as you
shouldn't need to open it for preview. You could put that
back in if you need.

If you are sending one to all bankers, you could skip the
listbox and just do a loop through your banker table.

--
Gary Miller
Sisters, OR



message
Yes, I investigated that already. I need to locate the
page for one banker,
print that page to the Acrobat Distiller, then find the
page for another
banker, print that page to the Acrobat Distiller, and
etc.
I don't want to
have to open and close the report each time I need to
find
the page for a
different banker.

:

Gwen,

The 'OpenReport' command has a parameter for a WHERE
clause
that will let you specify a criteria for the report.
Help
should give you the details.

--
Gary Miller
Sisters, OR



message
I'm learning VBA, and I have written a function that
essentially opens a
series of reports, prints out each page of the
report
to
the Acrobat
Distiller, and then closes the report (code snippet
included at bottom of
this post). How can I use VBA to go to a specific
record
in the report? Each
report consists of multiple pages. Each page is
essentially sales data for
one commercial banker. So, each page needs to be
printed
to a separate PDF
file. As you can tell from the code below, I'm
currently
using DoCmd.Printout
to specify the page, but I'd rather use VBA to find
the
page for a specific
banker, then print just that page to the Acrobat
Distiller. The reason for
this is I need to make sure the right page gets
printed
to
the right file. I
don't need any bankers looking at another banker's
sales
data.

Thanks in advance,
GwenH

'Print the Commercial Banker-Large Market
Worksheets.
DoCmd.OpenReport "Commercial Banker Incentive
Worksheet-Large Market",
acViewPreview
MsgBox "Save As ""CB Large Market Month 2006""",
vbOKOnly, "Lenise
Braziel's Copy"
DoCmd.PrintOut , , , acHigh, 1, True
MsgBox "Now copy that file to Martha Miller's
Folder -
Click OK When
Finished.", vbOKOnly, "Martha Miller's Copy"
MsgBox "Save As ""Balcer Worksheet Month 2006""",
vbOKOnly, "Alene
Balcer's Worksheet"
DoCmd.PrintOut acPages, 1, 1, acHigh, 1, True
MsgBox "Save As ""David Worksheet Month 2006""",
vbOKOnly, "Don David's
Worksheet"
DoCmd.PrintOut acPages, 2, 2, acHigh, 1, True
MsgBox "Now Copy Don's Worksheet to Alene's
Folder -
Click OK When
Finished.""", vbOKOnly, "Alene's Copy of Don's
Worksheet"
MsgBox "Save As ""Burks Worksheet Month 2006""",
vbOKOnly, "Steve Burks'
Worksheet"
DoCmd.PrintOut acPages, 3, 3, acHigh, 1, True
DoCmd.Close acReport, "Commercial Banker
Incentive
Worksheet-Large Market"
Click to expand...
Click to expand...
Click to expand...
 
G

Gwen H

I can't purchase the add-in you suggested; corporate security policies don't
allow me to do that. I've already written a VBA function that prints
everything to PDF just fine. I'm only trying to modify it so that it searches
for the page for a specific banker *before* it prints that page to PDF.

Again, the printing to PDF is not the problem. I've got that part working.

Thanks,
GwenH

Gary Miller said:
Gwen,

I have done automated PDF generation from Access in the past
for a client. After a bit of research we decided that a
third party addin, ACG's PDF Pro Mail Library 10.0, was
worth the pretty reasonable investment for how well it could
automate the process, especially the naming of the PDF files
and batch situations. Here is a link for that...

http://ourworld.compuserve.com/homepages/attac-cg/ACGPDF.htm

Also Tony Toews has a page that lists quite a few resources
on PDF printing from VBA and Access. Here is that link....

http://www.granite.ab.ca/access/pdffiles.htm

Those should give you a bit to work with.

--
Gary Miller
Sisters, OR



Gwen H said:
Your suggestion would not work well for this situation.
First, I have 40+
reports that I generate each month, all of which must be
exported to PDF
files a page at a time. Second, I am currently using a VBA
function to do
this; when I leave the bank, no one else will be using the
VBA function.
Third, I don't mind changing the code when a banker leaves
or a new one is
hired; it doesn't happen that often. For me, using the VBA
function alone is
the simplest and least labor-intensive approach.

So, is there a way to do the following in VBA?

Open the report
Go to John Doe's page (the pages are not going to become
longer, BTW)
Print John Doe's one page report to the Acrobat Distiller
Go to Jane Doe's page
Print Jane Doe's one page repot to the Acrobat Distiller
(Repeat the preceding two lines of code as needed)
Close the report

Thanks!

Gary Miller said:
Gwen,

It seems to me that you need to create some type of
selection list for what bankers are getting reports. One
of
the problems with hard coding your bankers into your code
as
you have done so far is that at some point those bankers
are
going to change and then you would have to change your
VBA
code. Also you need to consider the possibility that your
data may even change enough to shift pages of the report.

One approach would be to populate a listbox with your
bankers and use the multi-select feature to pick the ones
you want to send the reports to. You could then do a loop
in
code picking out all of the 'ItemsSelected' in the
listbox
to loop through printing the report for each banker in
the
list. You could add fields for each to specify save
directories and report names as well and then just refer
to
those in your code. Something like...

Dim varItem as Variant

For each varItem in Me!cboBankers.ItemSelected
DoCmd.OpenReport "Commercial Banker Incentive
Worksheet-Large Market",,,"[BankerID] = " &
Me!cboBankers.Column(0, varItem)
' Do whatever is needed to be done before the next
one
prints
Next item

This code looks for a BankerID as the first column in
the
listbox. Note that I did not use the ViewPreview as you
shouldn't need to open it for preview. You could put that
back in if you need.

If you are sending one to all bankers, you could skip the
listbox and just do a loop through your banker table.

--
Gary Miller
Sisters, OR



message
Yes, I investigated that already. I need to locate the
page for one banker,
print that page to the Acrobat Distiller, then find the
page for another
banker, print that page to the Acrobat Distiller, and
etc.
I don't want to
have to open and close the report each time I need to
find
the page for a
different banker.

:

Gwen,

The 'OpenReport' command has a parameter for a WHERE
clause
that will let you specify a criteria for the report.
Help
should give you the details.

--
Gary Miller
Sisters, OR



message
I'm learning VBA, and I have written a function that
essentially opens a
series of reports, prints out each page of the
report
to
the Acrobat
Distiller, and then closes the report (code snippet
included at bottom of
this post). How can I use VBA to go to a specific
record
in the report? Each
report consists of multiple pages. Each page is
essentially sales data for
one commercial banker. So, each page needs to be
printed
to a separate PDF
file. As you can tell from the code below, I'm
currently
using DoCmd.Printout
to specify the page, but I'd rather use VBA to find
the
page for a specific
banker, then print just that page to the Acrobat
Distiller. The reason for
this is I need to make sure the right page gets
printed
to
the right file. I
don't need any bankers looking at another banker's
sales
data.

Thanks in advance,
GwenH

'Print the Commercial Banker-Large Market
Worksheets.
DoCmd.OpenReport "Commercial Banker Incentive
Worksheet-Large Market",
acViewPreview
MsgBox "Save As ""CB Large Market Month 2006""",
vbOKOnly, "Lenise
Braziel's Copy"
DoCmd.PrintOut , , , acHigh, 1, True
MsgBox "Now copy that file to Martha Miller's
Folder -
Click OK When
Finished.", vbOKOnly, "Martha Miller's Copy"
MsgBox "Save As ""Balcer Worksheet Month 2006""",
vbOKOnly, "Alene
Balcer's Worksheet"
DoCmd.PrintOut acPages, 1, 1, acHigh, 1, True
MsgBox "Save As ""David Worksheet Month 2006""",
vbOKOnly, "Don David's
Worksheet"
DoCmd.PrintOut acPages, 2, 2, acHigh, 1, True
MsgBox "Now Copy Don's Worksheet to Alene's
Folder -
Click OK When
Finished.""", vbOKOnly, "Alene's Copy of Don's
Worksheet"
MsgBox "Save As ""Burks Worksheet Month 2006""",
vbOKOnly, "Steve Burks'
Worksheet"
DoCmd.PrintOut acPages, 3, 3, acHigh, 1, True
DoCmd.Close acReport, "Commercial Banker
Incentive
Worksheet-Large Market"
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Print, Page Down, Print, Page Down ??? 3
What the H#!! is wrong with this? 9
Recordset variable with SQL 2
run VBA from Word and copy all Worksheets of an Excel file to Word 2
VBA Help 0
Macro VBA coding, Copying content from one workbook to a new one 0
Copy to new sheet replacing existing data 2
Autofilter & copy results to new worksheet 0

Top