[Last Call] Learn how to a build a cloud-first strategyRegister Now


Create, name and save PDFs documents from a MSAccess form

Posted on 2016-08-08
Medium Priority
Last Modified: 2016-08-19
Hi Experts

I am hoping to create certificates directly from my database and have them named and saved as PDFs.

I've attached an example database to help explain what I am hoping to achieve. What I need help with is some code attached to a button on a form  (frmCreateCerts), that will create certificates for all records appearing in the qryCreateCerts query.

When the button is clicked I need the code to do the following actions...
   - create and save certificates as PDFs for all records in the qryCreateCerts query by inserting the [Full Name] into the certificate (frmCertificate)
   - save them into C:\Certificates\ folder on my computer
   - name each one using this format: [Full Name]_[Course]_[Today's Date].pdf, e.g. Stephen Lowe_Digital Practice_2016-08-09.pdf
   - once PDFs have been created, update the fields [CertIssued] = TRUE and [IssueDate] = Today's Date, in the tblRegistration table

I really don't have the knowledge to manipulate code snippets I've found and so would be very appreciative if anyone could please help me achieve this.

Question by:darls15

Expert Comment

by:Antonio Salva Ripoll
ID: 41748267
Hi darls15.

Instead of give you a full working solution, I'll explain you how to do it. (If you give a man a fish, this man will eat one day; if you teach him to fish, he will eat every day (Ancient chinese proverv))

To create pdf files is better to use reports than forms. Reports are intended to be printed, same as pdf files, while forms are intended to be the interactive part with the user.

So, the first you must do is create the Certificate report.

In the report properties, one of them is the DataSource, this property connects the report with the data you want print, in your case, the query.

Now, you can adjust the fields to meet your needs and with the Access Assistant, you can create the action to open the report.

Best regards.
LVL 58
ID: 41748485

 I would consider posting this as a Gigs project if you don't feel your up to the task:


 If you do want to tackle it yourself, then take it one step at a time and ask a series of questions on each aspect.  

 Antonio has already gotten you started in the right direction.  After getting the report set, then get it into a PDF format.

 Once that's done, then tackle the file naming, and finally updating the DB to indicate that the certificate has been posted.

 Ask one question on each task so your not over whelmed in trying to sort through it all at once.  While it seems simple, what you asked really covers quite a bit of ground and how you do it depends on the version of Access your using, where it will be used, etc.

LVL 31

Accepted Solution

Helen Feddema earned 2000 total points
ID: 41748647
Use a report instead of a form.  Here is some code:

Public Sub ExportReportToPDF()
'Created by Helen Feddema 12-Jul-2013
'Last modified by Helen Feddema 12-Jul-2013

On Error GoTo ErrorHandler

   Dim appOutlook As New Outlook.Application
   Dim strReport As String
   Dim strSavePath As String
   Dim strReportFile As String
   Dim itm As Outlook.MailItem
   Dim strEmail As String
   strReport = "rptCurrentData"
   strSavePath = "G:\Documents\PDFs\"
   strEmail = "hfeddema@hvc.rr.com"
   strReportFile = strSavePath & "Current Data.pdf"
   'Create PDF file
   DoCmd.OutputTo objecttype:=acOutputReport, _
      objectname:=strReport, _
      outputformat:=acFormatPDF, _
      outputfile:=strReportFile, _
   'Create email
   strEmail = "Email address"
   Set itm = appOutlook.CreateItem(olMailItem)
   itm.Subject = "Message Subject"
   itm.Body = "Message body"
   itm.To = strEmail
   itm.Attachments.Add Source:=strReportFile, _
   Set appOutlook = Nothing
   Exit Sub

   MsgBox "Error No: " & Err.Number & "; Description: "
   Resume ErrorHandlerExit

End Sub

Open in new window

The above code also emails the PDF;  you may not need that part.  If you want to produce the PDFs for all the members of a group (defined in a query), here is some code for that:

Public Sub SendInterventionEmails()
'Created by Helen Feddema 10-Jan-2010
'Last modified by Helen Feddema 10-Jan-2010

On Error GoTo ErrorHandler

   Dim appOutlook As New Outlook.Application
   Dim itm As Outlook.MailItem
   Dim rstIntervention As DAO.Recordset
   Dim lngCount As Long
   Dim lngID As Long
   Dim rpt As Access.Report
   Dim strFileName As String
   Dim strPrompt As String
   Dim strQuery As String
   Dim strRecordSource As String
   Dim strReport As String
   Dim strSQL As String
   Dim strTitle As String
   Dim strCurrentPath As String
   Dim strFileNameAndPath As String
   Dim strEmailSource As String
   strEmailSource = "qryInterventionEmail"
   strRecordSource = "qryMissingAssignments"
   strQuery = "qryMissingAssignmentsSingleStudent"
   Set dbs = CurrentDb
   Set rstIntervention = dbs.OpenRecordset(strEmailSource)
   strCurrentPath = Application.CurrentProject.Path & "\"
   'Use path selected with SelectFolder procedure
   'strCurrentPath = SelectFolder()

   With rstIntervention
      Do While Not .EOF
         lngID = ![StID]
         Debug.Print "Processing Student ID " & lngID
         strFileName = "Intervention Report for " & ![StFirst] _
            & " " & ![StLast] & ".pdf"
         strFileNameAndPath = strCurrentPath & strFileName
         'Create filtered query
         strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
            & "[StID] = " & Chr(39) & lngID & Chr(39) & ";"
         Debug.Print "SQL for " & strQuery & ": " & strSQL
         lngCount = CreateAndTestQuery(strQuery, strSQL)
         Debug.Print "No. of items found: " & lngCount
         If lngCount = 0 Then
            GoTo NextStudent
         End If
         'Open report with filtered query record source
         strReport = "rptMissingAssignmentsNew"
         DoCmd.OpenReport ReportName:=strReport, _
            View:=acViewPreview, _
         Set rpt = Reports(strReport)
         DoCmd.OutputTo objecttype:=acOutputReport, _
            objectname:=strReport, _
            outputformat:=acFormatPDF, _
         'Create email
         Set itm = appOutlook.CreateItem(olMailItem)
         itm.Subject = "MISSING WORK"
         itm.Body = "The attached file lists your missing assignments"
         itm.To = ![Email]
         itm.Attachments.Add Source:=strFileNameAndPath, _
         'For editing before sending
         'For sending automatically
         DoCmd.Close objecttype:=acReport, _
            objectname:=strReport, _

   End With
   Exit Sub

   MsgBox "Error No: " & Err.Number _
      & " in SendInterventionEmails procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Public Function CreateAndTestQuery(strTestQuery As String, _
   strTestSQL As String) As Long
'Created by Helen Feddema 28-Jul-2002
'Last modified by Helen Feddema 10-Jan-2010

On Error Resume Next
   'Delete old query
   Set dbs = CurrentDb
   dbs.QueryDefs.Delete strTestQuery

On Error GoTo ErrorHandler
   'Create new query
   Set qdf = dbs.CreateQueryDef(strTestQuery, strTestSQL)
   'Test whether there are any records
   Set rst = dbs.OpenRecordset(strTestQuery)
   With rst
      CreateAndTestQuery = .RecordCount
   End With
   Exit Function

   If Err.Number = 3021 Then
      CreateAndTestQuery = 0
      Resume ErrorHandlerExit
   MsgBox "Error No: " & Err.Number _
      & " in CreateAndTestQuery procedure; " _
      & "Description: " & Err.Description
   End If
End Function

Open in new window


Author Comment

ID: 41751246
Antonio and Jim, thanks for the advice and pointing me in the right direction. I'm slowly learning how to read, change and understand VBA through searching, reading and most of all through the help and advice I receive here. I've never had formal training on building databases/writing code but it is an area that I am liking very much. I will now change my approach and start with a report for this task rather than a form. Jim, I've not heard about the "Gigs" site and will check it out for future reference.

Helen, wow, thank you so much for providing me with this code! Emailing the certificates is my next step after getting the PDF creation working! This will give me the opportunity to learn something new without getting so confused that I give up! I don't know how to write code from scratch (apart from the basics), but by the time I implement it into my database I will understand how it works and this is precisely how I am learning as I go.

Anyways, I will get to this task right now and let you know how I go!

Author Closing Comment

ID: 41762144
Apologies for taking so long in getting back here. This worked great, thanks Helen. Your help is much appreciated.

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses
Course of the Month18 days, 10 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question