How to Output a dynamically filtered Access report to PDF

by Philipp Stiefel, originally published November 8th, 2017

last revision on 2024-05-19

In Microsoft Access 2010 and newer (Access 2007 with an additional Add-In) there was the very useful output format option acFormatPDF added to the DoCmd.OutputTo-Method. This method allows you to easily export Access reports to PDF files from VBA code without any 3rd-Party components.

There is one aspect of using this very convenient method that I frequently see people struggle with. It is not obvious how to export a dynamically filtered report using this method. Other than the DoCmd.OpenReport-Method, the DoCmd.OutputTo-Method does not support to supply any criteria to the report the filter the data. So, it seems to be a problem to export a report that displays different data sets depending on user input or other factors.

The obvious workarounds that come to mind are usually one of the following:

• Create different reports with the criteria built in
• Create a parameter query referencing form fields or global functions as record source for the report.
• Build the SQL for the report at runtime and assign it to the report.

All of these do work. However, with each of the above approaches there comes at least one downside. (Matching the above order):

• It requires to create multiple very similar objects (reports). - Not very flexible and a maintenance burden.
• It requires additional functions or a form and is rather inflexible.
• It requires to build the whole query at runtime and assign it to the report in its Report_Open-Event. - Valid approach, but somewhat complex, error prone, and messy due to the dependence on an external (to the report) source for the SQL string.

While all these approaches work, I would rather not recommend to use any of them.

Luckily there is a very simple solution to the problem that is just not so obvious.

If the report you are exporting is closed when you invoke DoCmd.OutputTo, the OutputTo-Method will open the report and export it. - Plain and simple. - That is the root cause of this whole issue.

However, if the report you want to export is open already in preview (acViewPreview) when you invoke OutputTo, the DoCmd.OutputTo-Method will just export the report as it is!

Considering this, it is very simple to solve the problem with exporting dynamically filtered reports. You open the report using DoCmd.OpenReport in preview (View=acViewPreview) and supply your dynamic filter to the methods WhereCondition argument. To prevent the report appearing on the screen in preview, you use the WindowMode acHidden, to open the report invisible to the user.

You then simply invoke DoCmd.OutputTo with Format=acFormatPDF and all the other arguments as before to export the filtered report to the PDF file.

There is only one thing that you must not forget to take care of. After opening the report in preview view, it will stay open, albeit invisible, until you explicitly close it. If you “open” it again using DoCmd.OpenReport, the report will become visible but it will not re-query the data and will it still use the original criteria for filtering the data. In this case the report might display the wrong data set. - Simple solution: Always close the hidden report after the PDF file was saved.

Taking all this into account, our procedure to export a filtered report to PDF could look like this:

In a real-world implementation, based on this sample, you can pass in all 3 variables as arguments into the function and thus create a simple and flexible ExportToPDF routine for your Access application.

So, here we are. An elegant, yet very easy to implement, solution and it requires only 2 additional lines of code.

Add-On-Video: Export Filtered Access Report to PDF

Here is an “add-on” to this article. I recorded a demonstration of the solution described here on video and published it to YouTube.

Performance Considerations

The solution explained above is suitable for most scenarios. However, if you have complex reports based on complex queries, or you must export a huge number of PDF documents, you might notice that an export process based on this solution will take some time.

Reader Klaus Oberdalhoff reported a case where both of the above came together. He had to export a very complex invoice report and each export batch had to generate hundreds of PDF files. This export took ~3 hours using the technique above. To improve performance, he then employed the third of the not-recommend approaches with rebuilding the Recordsource to include the current criteria in the Report_Open event. By doing this, he reduced the time required for the PDF export quite significantly to only 30 minutes.

What is causing this performance issue?

My simple suggestion above will query the data and render the report once when it is opened invisible in preview, and then again when it is exported to PDF.

Creating the PDF appears to use less resources and time than rendering the visual preview. So, the additional work of opening the preview is not just doubling the time needed but increases it manyfold. – This will vary depending on the complexity of the report.

How can we improve the performance?

I named two alternatives above:

• Create a parameter query referencing form fields or global functions as record source for the report.
• Build the SQL for the report at runtime and assign it to the report.

As said above, they both work generally, and they would be both suited to solve the performance issue. Still, I refrain from recommending them for the reasons stated above.

There is another option, which I also strongly dislike, but which, in my opinion, is the least of all these evils in this scenario: TempVars.

To use TempVars as query criterions you must include them in the design of the query. So, if we want to use the same criterions as in the example above, we need to modify the Recordsource query of the report to something like this:

To use this report with the TempVar criterions to create the PDF files from the report, we now must set the TempVars to the criteria values before we call DoCmd.OutputTo. The following VBA code is an example implementing this:

So, the code doesn’t look to bad either. Nonetheless, it now has this indirect dependency on the TempVars, which I rather prefer to avoid.

Conclusion

For most use cases I still recommend the simpler, first approach with opening the report with the required criteria hidden and then starting the PDF export. This has only a one-way dependency from the VBA procedure to the report. – A clean overall design.

However, if you are in a situation where performance is significantly degraded by opening and rendering the report twice, you should use the alternative approach with TempVars. It introduces dependencies between the report, the code and the TempVars collection. This is a messy and entangled design. Still, the massive performance gains that can be achieved by this approach can justify this suboptimal solution design.

Share this article: