We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Macro to loop through Access report with different criteria, and output in pdf

GIStewart
GIStewart asked
on
Medium Priority
529 Views
Last Modified: 2012-05-11
I have built a Report based on certain fields in a Query in Access.  The fields in the Report are Region, Country, EmployeeID, and some data about each employee.  I am looking to tailor the report for various recipients and would use different criteria on the Query if I had to do this manually.  But I am trying to have a report for each Region that would contain all Countries of the Region, and also a report for each Country.  I have the CutePDF writer program and I would like to ouput each version of the report as a pdf document with the ability to save each one with a name that I specify as it is produced.   Thank you.
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Create form, where will be all your criterias. You can also generate file name in this form.
I don't know how to set output file name for CutePDF in VBA, but there is Lebans solution for Access 2003:
http://www.lebans.com/reporttopdf.htm
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Ok,

Here's an idea.
What if you did up each of the possible report customizations as a separate object, and then embedded each of them as a subreport.
I have a report where I have seven subreports, where each 0.0007" high and 0.0007" apart with CanGrow = True on the main report and however large they need to be only at design and run time.  Code then manipulates with of these will be visible and how tall they will actually be.
If they aren't needed in a particular context, they only chew up 0.0014" of whitespace.
(Access 2007/2010 GPF if I try to shrink them on the fly--I used to do that in 2003, but I run a multi-version environment)
The subreports would contain all the customization, and the main report may not actually display any data at all.
It would hold the query and code to decide which subreport(s) should display.

I use doPDF 6.4 with Access 2003.  It will pick up the report's caption as a default for the filename, and I manipulate all that via VBA.
First, we need to get a workable report.
Does this sound like a good plan of attack?

I need as good a sample as you can post to work with
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<I have the CutePDF writer program and I would like to ouput each version of the report as a pdf document with the ability to save each one with a name that I specify as it is produced.   Thank you. >>

  As has been suggested, you'll need to do this via a form.  Collect your criteria on the form, then you'll need to write some code to call the report in a loop.

  On each OpenReport action, you'll include a filter or a WHERE condition to restirct the report to a specific region.
  You can then convert to PDF and control the file name.

  You'll end up with one PDF per region then.

  When designing the report, it will be default include all regions.  Don't worry about that, just get the structure setup the way you want it.

JimD.

Author

Commented:
Here is a somewhat simplified version of the data (it's in a table here, but it's in a query in reality) and the report.
EE-sample-Apr28.mdb
CERTIFIED EXPERT

Commented:
This is an example of reports generation. They are printed to default printer.
I've added table for regions.
In Access 2007 and later you can output to pdf without additional code.
For your Access 2003 try to use Lebans code.
Here are many examples how to use it:
https://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Reports/Q_24760122.html?sfQueryTermInfo=1+10+2003+30+access+pdf+report
EE-sample-Apr28.mdb
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Ok,

Play with the altered sample.
There's a form to drive it, and the report filters by country or by region.

CutePDF I don't know from
DoPDF 6.4 picks up the caption of the report for the filename.

Let me know about the report part first
CERTIFIED EXPERT
Most Valuable Expert 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.