Another method for outputting an Access Report to PDF is to create a Macro to do it automatically, whenever the Macro is called. This can be extremely useful when you want to create a button to create, or otherwise automatically generate, a PDF file for a Report in your Access database application. In this example, I’ll be using Microsoft Access 2010 to create a macro to save a Report as a PDF file.
- Start Microsoft Access 2010 from the Windows Start menu.
- Once Microsoft Access 2010 has been opened, click the “Sample templates” button in the in the Access Backstage.
- Once a list of the sample templates is shown in the Access Backstage, click on the “Northwind” template to select it, optionally choose a path for the new application, and then click the “Create” button to create a new database application from the template.
- Once the new database application has been opened, click on the “Enable content” button at the top of the start form. This will enable code and unsafe macros in the database and reopen the database application in trusted mode.
- The “Login Dialog” will be opened, so just close it by clicking the “X” button on the top right of the dialog. Then expand the “NavPane” by clicking on the bar that says “Navigation Pane” on the left side of the Access screen.
- Once the Navigation Pane is expanded, notice that it says “Northwind Traders” at the top of it, which is a custom group for the Navigation Pane. Click on the down arrow to the right of the group name to show the Navigation Pane’s context menu and choose the “Object Type” option. This will show groups based upon the type of each object in the Navigation Pane.
- Next, expand the Reports group in the Navigation Pane to show a list of reports currently in the database application. Right click on the “Customer Address Book” Report and choose the “Design View” option to open the Report in Design View mode in the Access window.
- On the Report Design Tools – Design Ribbon, click on the “Button” control in the Controls group to select it. Then click to the right of the “Customer Address Book” text control in the Report Header section of the Report to drop a new Button control on the report.
- While the new Button control is still selected on the Report, press the “F4” key to open the Access Property Sheet to edit the properties of the control. Click on the “All” tab of the Property Sheet to show all of the Button control’s properties.
- For the “Name” property of the new Button control, type in: btnSaveAsPDF. For the “Caption” property, type in: “Save as PDF”. The Button control’s text will now show as “Save as PDF”.
- Then, click on the “Event” tab of the Property Sheet and click on the “…” button on the right side of the “On Click” event for the Button control. This will open the “Choose Builder” dialog. Select the “Macro Builder” option and then click the “OK” button. This will open the Macro Builder window for the “On Click” event for the Button control.
- In the Macro Builder window, for the “Add New Action” drop down menu, select the “ExportWithFormatting” macro (in Access 2007, you would choose the “OutputTo” macro). For the “Object Type” parameter, choose “Report”. For the “Object Name” parameter, choose “Customer Address Book”. For the “Output Format” parameter, choose the “PDF Format” option.
- Now the Macro to save the report as a PDF file should be ready to go. Click on the “Save” button and then close the Macro window. You will be taken back to the “Customer Address Book” Report. Right click on the document tab for the “Customer Address Book” Report and choose the “Report View” option to open the Report in the normal Report View mode. Notice that the “Save as PDF” button is now visible on the Report.
- Click on the “Save as PDF” button. The “Output To” file dialog will be opened, so select a file name and location for the new PDF file. Then click the “OK” button. The Access Report will be saved as a PDF file at the specified location. If you double click on the new PDF file, it will be opened in your default PDF viewer.
Creating a Macro to save an Access Report as a PDF file is extremely useful and very easy functionality to add to any Access database application. Best of all, this does not require any additional PDF tools or programs to be installed on your machine. This functionality is now included in Microsoft Access 2007 and higher versions!
Submitted By: Geoffrey L. Griffith – June 29th, 2012
- How to Enable the Developer Ribbon in Microsoft Excel 2010
- Using Text Box controls to create borders in Microsoft Publisher 2010
- Creating a Spreadsheet from Template in Microsoft Excel 2007
- Saving a Report as PDF with the Access 2010 GUI
- Creating a new blank database using Microsoft Access 2007
- Creating Primary Keys and Foreign Keys in Microsoft Access
- Submitting Access Database Templates to Office Online
- Showing Hidden and System Objects in the Navigation Pane
- Using the Graphics Manager to work with Images in Publisher 2007
- The new msOfficeUser.com site is up and running!