From Access VBA Programming For Dummies. By Alan Simpson. Programming Microsoft Access with VBA can be a lot easier if you know the keyboard shortcuts for the most common commands and tasks and the most common bits of code that you’ll use in the editor and immediate windows as you build and debug your Access application.
![]()
by Philipp Stiefel, originally published November 8th, 2017
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:
All of these do work. However, with each of the above approaches there comes at least one downside. (Matching the above order):
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 WindowModeacHidden, 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:
PublicSubExportFilteredReportToPDF()DimreportNameAsStringDimfileNameAsStringDimcriteriaAsStringreportName='rptYourReportName'fileName='C:tmpreport_export_file.pdf'criteria='SomeTextField = 'ABC' AND SomeNumberField = 123'DoCmd.OpenReportreportName,acViewPreview,,criteria,acHiddenDoCmd.OutputToacOutputReport,reportName,acFormatPDF,fileNameDoCmd.CloseacReport,reportName,acSaveNoEndSub
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.
Share this article:
© 1999 - 2019 by Philipp Stiefel - Privacy Policiy-->
A Report object refers to a particular Microsoft Access report.
Remarks
A Report object is a member of the Reports collection, which is a collection of all currently open reports. Within the Reports collection, individual reports are indexed beginning with zero. You can refer to an individual Report object in the Reports collection either by referring to the report by name, or by referring to its index within the collection. If the report name includes a space, the name must be surrounded by brackets ([ ]).
![]()
Note
Each Report object has a Controls collection, which contains all controls on the report. You can refer to a control on a report either by implicitly or explicitly referring to the Controls collection. Your code will be faster if you refer to the Controls collection implicitly. The following examples show two of the ways you might refer to a control named NewData on a report called OrderReport.
Example
The following example shows how to use the NoData event of a report to prevent the report from opening when there is no data to be displayed.
The following example shows how to use the Page event to add a watermark to a report before it is printed.
The following example shows how to set the BackColor property of a control based on its value.
The following example shows how to format a report to show progress bars. The example uses a pair of rectangle controls, boxInside and boxOutside, to create a progress bar based on the value of AvgOfRating. The progress bars are visible only when the report is opened in Print Preview mode or it is printed.
EventsMethodsPropertiesSee alsoSupport and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.
![]() Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
December 2022
Categories |