[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 514
  • Last Modified:

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

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.
1 Solution
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:

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 (Microsoft MVP/ EE MVE)PresidentCommented:
<<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.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

GIStewartAuthor 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.
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:

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now