I originally started this project from necessity. In another business I had a requirement to maintain information in PDF forms provided by a third party, but my choices were either to manually fill each one individually with a pen or to fill each one in a browser while copy/pasting all the information from another application (Microsoft Access). Neither of those solutions seemed very productive or had any way of catching input errors.
I started out looking at what was available from Adobe that I could use by incorporating it into a VBA (Visual Basic for Applications) code in an Access application that housed the information I needed to place into various PDF fillable forms. But it didn’t take long to figure out that the documentation was fragmented across a multitude of reference pages with lots of unrelated references.
I looked through the reference information until I kept coming across references where you would need to purchase and install a full license of their product plus a host of documentation in an SDK.
I started searching for other methods and found the solution. Below are the logic steps that are required to get this working in a finished application using native VBA code in Access.
- Unlock Printable PDF Form – The majority of fillable PDF forms have a password protecting the PDF form. It also prevents you from being able to look at the code in the form to be able to read the form field names and types. You can get the PDF unlocked from various online services. Some will allow you to unlock up to 3 PDF forms per day. You must save your unlocked PDF form for later.
- Run Fillable PDF form through PDFtk Server – This is a command line utility that reads through the PDF code and create a list file containing all the writable field names and their data type. More on this later. You can download it here.
- Save form field information in a file – This is performed by the application mentioned above and is only required one for each unique PDF form. You will import the field names into a table in your Access application.
- Run Code to Insert Data into PDF form – At this point you can run your code that inserts your data into each of the fields in a copy of your unlocked PDF form.
- Print PDF Form – Finally you can print, save or do both to your new data filled PDF form.
Below is some sample code that puts it all together.
Private Sub Print4473(myLOGID As String)
Dim formname As String, OriginatingFile As String, DestinationFile As String, myFileName As String, mySQL As String, DI_FormsVal As String
Dim SIP As String
formname = "4473_5300-9_Transaction_Record_" & Me.DI_Name.Value & "_" & myLOGID & "_"
OriginatingFile = Application.CurrentProject.Path & "\BIN\4473-5300_9.pdf"
myFileName = formname & Month(Date) & "_" & Day(Date) & "_" & Year(Date) & "_" & Hour(Time) & "_" & Minute(Time) & ".pdf"
DestinationFile = Application.CurrentProject.Path & "\EXPORT\" & myFileName
DoCmd.OpenForm "4473_frm", , "", "", , acDialog
mySQL = "UPDATE Log_tbl SET Log_tbl.DI_Forms = '" & formname & "' "
mySQL = mySQL & "WHERE (((Log_tbl.LOG_ID) = " & myLOGID & "));"
CurrentDb.Execute mySQL
mySQL = "INSERT INTO Attachement_tbl ( Log_ID, attachement_Date, attachement_Path, attachement_Name ) "
mySQL = mySQL & "SELECT " & Me.Combo31.Value & " AS Log_ID1, #" & Me.DI_Date.Value & "# AS attachement_Date1, '" & Application.CurrentProject.Path & "\EXPORT\" & "' AS attachement_Path1, "
mySQL = mySQL & "'" & myFileName & "' AS attachement_Name1;"
CurrentDb.Execute mySQL
SIP = MsgBox("Do you want to open this form directly?", vbInformation + vbYesNo, "Open 4473 PDF")
If SIP = "6" Then GoTo Open4473 Else GoTo ExitSub
GoTo ExitSub
Open4473:
openAllFiles (DestinationFile)
GoTo ExitSub
ExitSub:
Exit Sub
End SubBelow is a sample of the field names that you import from the PDFtk Server:
This is the information of each of the fillable fields in the PDF. You need this so you know which field you are mapping to your data.
Field info
---
FieldType: Text
FieldName: topmostSubform[0].Page1[0].TextField1[0]
FieldNameAlt: Transferor's/Seller's Transaction Serial Number (If any)
FieldFlags: 8392704
FieldJustification: Left
---
FieldType: Text
FieldName: topmostSubform[0].Page1[0].TextField19[0]
FieldNameAlt: 1. Manufacturer and Importer (If any) (If the manufacturer and importer are different, include both.) - 1.
FieldFlags: 8388608
FieldJustification: Left
---
FieldType: Text
FieldName: topmostSubform[0].Page1[0].TextField19[1]
FieldNameAlt: 1. Manufacturer and Importer (If any) (If the manufacturer and importer are different, include both.) - 2.
FieldFlags: 8388608
FieldJustification: Left
---And this is a sample of the code that you will export from a report listing all the fields and the values that will be inserted to each field. The end of each line would have the values that will be entered into each field.
The field in your report that looks at the field names would look like this:
=”<< /T (” & [Field_Name] & “) /V (” & [FieldValue] & “)>>”
Query ID Field Name Field Value
41 topmostSubform[0].Page1[0].TextField19[1]
41 topmostSubform[0].Page1[0].TextField19[2]
41 topmostSubform[0].Page1[0].TextField19[3] You would print the report to a text file and then run the command line to insert the values into those fields in the unlocked fillable PDF.