MS Office User

Creating a Macro to Save a Report as PDF in Access 2010

by on Jun.29, 2012, under Access

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.

Step-by-Step

  1. Start Microsoft Access 2010 from the Windows Start menu.
  2. Once Microsoft Access 2010 has been opened, click the “Sample templates” button in the in the Access Backstage.
  3. Figure 1 - Select the Sample Templates in Access

    Figure 1 – Select the Sample Templates in Access

  4. 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.
  5. Figure 2 - Select and Create the Northwind Template

    Figure 2 – Select and Create the Northwind Template

  6. 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.
  7. Figure 3 - Enable the Database Application

    Figure 3 – Enable the Database Application

  8. 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.
  9. Figure 4 - Close the Login Form and Open the Navigation Pane

    Figure 4 – Close the Login Form and Open the Navigation Pane

  10. 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.
  11. Figure 5 - Switch the Navigation Pane to Object Type Grouping

    Figure 5 – Switch the Navigation Pane to Object Type Grouping

  12. 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.
  13. Figure 6 - Open the Report in Design View Mode

    Figure 6 – Open the Report in Design View Mode

  14. 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.
  15. Figure 7 - Add a Button Control to the Report

    Figure 7 – Add a Button Control to the Report

  16. 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.
  17. Figure 8 - View the All Tab on the Property Sheet

    Figure 8 – View the All Tab on the Property Sheet

  18. 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”.
  19. Figure 9 - Set the Properties for the Button Control

    Figure 9 – Set the Properties for the Button Control

  20. 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.
  21. Figure 10 - Open the Macro Builder for the On Click Event

    Figure 10 – Open the Macro Builder for the On Click Event

  22. 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.
  23. Figure 11 - Create the Macro and Set the Parameters

    Figure 11 – Create the Macro and Set the Parameters

  24. 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.
  25. Figure 12 - View in Report View Mode and Click the Button

    Figure 12 – View in Report View Mode and Click the Button

  26. 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.
  27. Figure 13 - The Report Saved as a PDF File

    Figure 13 – The Report Saved as a PDF File

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


Random Posts:

  1. The Access 2010 Programmer’s Reference gets a 5-star review!
  2. Creating Primary Keys and Foreign Keys in Microsoft Access
  3. Submitting Access Database Templates to Office Online
  4. Connecting Microsoft Outlook 2007 to Gmail without SSL
  5. The new msOfficeUser.com site is up and running!
  6. Top 10 Reasons to get Microsoft Office 2010!
  7. Using the Graphics Manager to work with Images in Publisher 2007
  8. Creating Screen Clippings (screenshots) using OneNote 2010
  9. Creating a Macro to Save a Report as PDF in Access 2010
  10. Can’t Delete File in Windows Vista or Windows 7 – Problem Solved!

:, , , , , , , , , , , , , , , , ,

Leave a Reply

You must be logged in to post a comment.



Archives