With Office 2007 now available with its optional Save As PDF functionality now available, it is surprising that this doesn't come with an addition to the wizard to create a PDF file of a report/form/query fairly quickly.
You can add this functionality to your own applications quite easily. Add a command button, give it a sensible name then go to the VBA editor and enter the following code:
Replace
- acOutputReport with the constant referring to the object type (type DoCmd.OutputTo then you will get a list of them in a drop down, but its the same used elsewhere in Access for other object types)
- rptReportName with the name of the database object (report, form, query etc) as it appears in the database window (up to and including 2003) or navigation pane (2007).
- "c:\output.pdf" with a fixed path to output the file. Note that doing this repeatedly will overwrite previous output without prompting for a location. Error trapping for read only files or network locations without write permission will need to be added.
- Remove everything after "acFormatPDF" to prompt for output location if you want to allow the user to choose their own output location.
To filter down a report to only display certain records, after all you don't want one that will report everything, put a field on the report =Forms!frmSomeform!txtSomeTextbox
to filter on the value of a control on a visible form, or to prompt for data (but you also need extra error handling in case the user clicks cancel):
=[Enter Student Number]
and you will be prompted for it on opening the report. As DoCmd.OutputTo doesn't support a WhereCondition parameter, this is the only way I have found to reliably filter a form or report to only display specific records.
Please note that this assumes that:
i) the PDF writer addin is installed correctly; it does not check whether the functionality is available.
ii) It doesn't check for the correct version of Access being present (ie 12.x).
To view the resulting file immediately afterwards, add this to the end:
Noting of course if you allow people to choose their own output location, you will need to pick it up in a variable and use that for input. They also need some form of PDF reading software (Adobe Reader, FoxIT reader, full Adobe PDF writer software etc) installed on their PC and to be set as the default handler for PDF files.
Hope that this is useful for somebody.
John
You can add this functionality to your own applications quite easily. Add a command button, give it a sensible name then go to the VBA editor and enter the following code:
Code:
DoCmd.OutputTo acOutputReport, "rptReportName", acFormatPDF, "c:\output.pdf"Replace
- acOutputReport with the constant referring to the object type (type DoCmd.OutputTo then you will get a list of them in a drop down, but its the same used elsewhere in Access for other object types)
- rptReportName with the name of the database object (report, form, query etc) as it appears in the database window (up to and including 2003) or navigation pane (2007).
- "c:\output.pdf" with a fixed path to output the file. Note that doing this repeatedly will overwrite previous output without prompting for a location. Error trapping for read only files or network locations without write permission will need to be added.
- Remove everything after "acFormatPDF" to prompt for output location if you want to allow the user to choose their own output location.
To filter down a report to only display certain records, after all you don't want one that will report everything, put a field on the report =Forms!frmSomeform!txtSomeTextbox
to filter on the value of a control on a visible form, or to prompt for data (but you also need extra error handling in case the user clicks cancel):
=[Enter Student Number]
and you will be prompted for it on opening the report. As DoCmd.OutputTo doesn't support a WhereCondition parameter, this is the only way I have found to reliably filter a form or report to only display specific records.
Please note that this assumes that:
i) the PDF writer addin is installed correctly; it does not check whether the functionality is available.
ii) It doesn't check for the correct version of Access being present (ie 12.x).
To view the resulting file immediately afterwards, add this to the end:
Code:
Application.FollowHyperlink "c:\output.pdf"Noting of course if you allow people to choose their own output location, you will need to pick it up in a variable and use that for input. They also need some form of PDF reading software (Adobe Reader, FoxIT reader, full Adobe PDF writer software etc) installed on their PC and to be set as the default handler for PDF files.
Hope that this is useful for somebody.
John
