We help IT Professionals succeed at work.

Automatically fill in data from ms access to a pdf form

Medium Priority
72 Views
Last Modified: 2020-02-20
Hello experts. I created a test pdf form in Adobe Acrobat Pro with three simple text field properties. Test1, Test2, and Test3. I have a table in ms access for office 365 call tbl_test that contains the identical field names as my pdf names. Test1, Test2 and Test3.

All I am trying to do is populate the pdf form field names with what every I put in my ms access field names. Everything I have tried to google or go on youtube (preferably since I am not a programmer) to view shows how to export from say an entire excel into a pdf to where I am looking for say some sort of mapping. Hope I am making sense and any help for a newbie would be great.
Comment
Watch Question

President / Owner CARDA Consultants Inc.
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:

Author

Commented:
So I received an error which I figured out. In the code there is a line to create the pdf file which is:

'Create XFDF file
Open strPath & "\" & strXFDF For Output As #intFile


What would be the line of code to write to my pdf file whici is located in a test folder. Example:

C:\test\mytest.pdf
Daniel PineaultPresident / Owner CARDA Consultants Inc.
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:

If you look at the original code


Private Sub cmdPDF_Click()
'theDBguy@gmail.com
'www.accessmvp.com/thedbguy
'04/10/2012 - Original version (v1)
'06/13/2012 - Update (v1.1)
'           - XML parsing will fail if the field contains either an ampersand (&) or a less than symbol (<)
'           - One approach is to replace "&" with "&amp;" and "<" with "&lt;"


'This demo creates a XFDF file to merge with a fillable PDF form.
'Using this method avoids the need to use an Acrobat DLL to manipulate the PDF file.
'This method relies on the capabilities of the installed PDF reader.

'Declare the PDF file to be filled and assume it's in the same directory as the database
    Const strPDF              As String = "theDBguy.pdf"

    'Declare the XFDF file to use
    Const strXFDF             As String = "theDBguy.xfdf"

    Dim strPath               As String
    Dim intFile               As Integer

    strPath = CurrentProject.Path
    intFile = FreeFile

    'Create XFDF file
    Open strPath & "\" & strXFDF For Output As #intFile

    Print #intFile, "<?xml version=""1.0"" encoding=""UTF-8""?>"
    Print #intFile, "<xfdf xmlns=""http://ns.adobe.com/xfdf/"" xml:space=""preserve"">"
    Print #intFile, "<f href=""" & strPDF & """/>"
    Print #intFile, "<fields>"
    Print #intFile, "<field name=""fname"">"
    Print #intFile, "<value>" & Me.txtFName & "</value>"
    Print #intFile, "</field>"
    Print #intFile, "<field name=""lname"">"
    Print #intFile, "<value>" & Me.txtLName & "</value>"
    Print #intFile, "</field>"
    Print #intFile, "<field name=""notes"">"
    Print #intFile, "<value>" & Replace(Replace(Me.txtNotes, "&", "&amp;"), "<", "&lt;") & "</value>"
    Print #intFile, "</field>"
    Print #intFile, "</fields>"
    Print #intFile, "</xfdf>"

    Close #intFile

    'Open the PDF file
    ShellEx strPath & "\" & strXFDF
End Sub


WHERE strPath = CurrentProject.Path


So you could try using


strPath = " C:\test\ "


and then change the constants from 


    Const strPDF              As String = "theDBguy.pdf"
    Const strXFDF             As String = "theDBguy.xfdf"


to


    Const strPDF              As String = " mytest .pdf"
    Const strXFDF             As String = " mytest .xfdf"

Author

Commented:
Thanks. That is working great. The only issue I am having now is when I try and add the second test field in the code what I am typing in the access form does not carry over into the pdf form. The first record shows up but not the second (or third record for that matter). Here is what I have. Am I missing something?

'Create XFDF file

Open strPath & "\" & strXFDF For Output As #intFile

Print #intFile, "<?xml version=""1.0"" encoding=""UTF-8""?>"
Print #intFile, "<xfdf xmlns=""http://ns.adobe.com/xfdf/"" xml:space=""preserve"">"
Print #intFile, "<f href=""" & strPDF & """/>"
Print #intFile, "<fields>"
Print #intFile, "<field name=""Test1"">"
Print #intFile, "<value>" & Me.txtField1 & "</value>"
Print #intFile, "</field>"


Print #intFile, "<field name=""Test2"">"
Print #intFile, "<value>" & Me.txtField2 & "</value>"
Print #intFile, "</field>"

Print #intFile, "</fields>"
Print #intFile, "</xfdf>"

Close #intFile
Daniel PineaultPresident / Owner CARDA Consultants Inc.
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:

You need to move to that record and the run the code.  The file is linked to the current record.

Author

Commented:
Thank you so much!