Create, name and save PDFs documents from a MSAccess form
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.
Thanks
darls15
Create_Certificates.accdb
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.
Thanks
darls15
Create_Certificates.accdb
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
darls15,
I would consider posting this as a Gigs project if you don't feel your up to the task:
https://www.experts-exchange.com/gigs/
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.
Jim.
I would consider posting this as a Gigs project if you don't feel your up to the task:
https://www.experts-exchange.com/gigs/
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.
Jim.
Use a report instead of a form. Here is some code:
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 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, _
autostart:=True
'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, _
Type:=olByValue
itm.Display
ErrorHandlerExit:
Set appOutlook = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: "
Resume ErrorHandlerExit
End Sub
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, _
windowmode:=acWindowNormal
Set rpt = Reports(strReport)
DoCmd.OutputTo objecttype:=acOutputReport, _
objectname:=strReport, _
outputformat:=acFormatPDF, _
outputfile:=strFileNameAndPath
'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, _
Type:=olByValue
'For editing before sending
itm.Display
'For sending automatically
'itm.Send
DoCmd.Close objecttype:=acReport, _
objectname:=strReport, _
Save:=acSaveNo
NextStudent:
.MoveNext
Loop
End With
ErrorHandlerExit:
Exit Sub
ErrorHandler:
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
.MoveFirst
.MoveLast
CreateAndTestQuery = .RecordCount
End With
ErrorHandlerExit:
Exit Function
ErrorHandler:
If Err.Number = 3021 Then
CreateAndTestQuery = 0
Resume ErrorHandlerExit
Else
MsgBox "Error No: " & Err.Number _
& " in CreateAndTestQuery procedure; " _
& "Description: " & Err.Description
End If
End Function
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
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!
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!
Apologies for taking so long in getting back here. This worked great, thanks Helen. Your help is much appreciated.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access
From novice to tech pro — start learning today.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
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.