Filling Adobe forms from Access using VBA (1 Viewer)

joshuar

New member
Local time
Today, 22:33
Joined
Dec 15, 2020
Messages
7
This thread is a list of a few VBA tricks to getting information from an Access database to a PDF form.

I wish I knew how to do this stuff about a decade ago for a big project I was working on, but the resources on how to do it were pretty limited at the time; I had to settle for Access Report formats with the forms little more than pictures in the background- painful. Resources are still somewhat limited, so below are a few consolidated cliff notes on how to do it. If it wasn’t for a few more recent postings from Karl Heinz Kremer on the subject, I’d likely still be producing forms with Access reports. Big thanks to him.

There are 2 attached documents:
  • LockAfterSigning.js(.txt) is a script that must be located in the javascript folder of Acrobat. This allows VBA to set lock (setLock) fields for a signature block. The javascript used to do this is a privileged method that can’t be called directly from VBA. Remove the .txt extension to use. The file should be placed in a directory similar to below:
    C:\Program Files (x86)\Adobe\Acrobat 11.0\Acrobat\Javascripts\

  • Demo Adobe Forms.accdb has two forms in it:
    • simpleForm produces an example form and each button does one of the functions listed below to alter the form or fill it
    • Make2062 has a button that puts it all together to produce a pretty lengthy inventory list with digital signatures (a DA2062) from dummy data in tbl2062Data.
Adding the Acrobat library to the VBA project: In order to use most of these Acrobat functions, you will have to enable the Acrobat reference library in VBA. In the Microsoft Visual Basic for Applications, 'Go to "Tools - References" and select "Adobe Acrobat 10.0 type library" or "Acrobat"

Opening the form:
With Acrobat, I can open the form, have VBA adjust stuff in it and see the changes as they happen. If the PDF document is open in Adobe Reader, this doesn't happen and it doesn't really change anything in the document. If Adobe Reader is the default application for PDFs, I wait till the VBA is done making the adjustments in the form prior to opening the form. That said, I use the code below to do check if PDFs open with Acrobat by default:

Code:
'Check if Acrobat is default and open form if it is

Private Function checkForAcrobat(ByVal sFile$)
    strFileAssociation = GetFileAssociation(sFile)
    If strFileAssociation Like "*Acrobat.exe" Then
        checkForAcrobat = True
    Else
        checkForAcrobat = False
    End If

End Function

Private Function GetFileAssociation$(ByVal sFile$)
    GetFileAssociation = "File not found !"
    If Dir(sFile) = "" Or sFile = "" Then Exit Function
    GetFileAssociation = "No association found !"
    Dim i&, E$: E = String(260, Chr$(0))
    i = FindExecutable(sFile, vbNullString, E)
    If i > 32 Then GetFileAssociation = Left$(E, InStr(E, Chr$(0)) - 1)
End Function

This code is used before the section of VBA that modifies the form. strFilePath is a string variable with the path to the PDF form:

Code:
   strFilePath = "C:\PathToPDF\MyForm.pdf"
    usesAcrobat = checkForAcrobat(strFilePath)
    If usesAcrobat Then
        VBA.Shell "Explorer.exe " & Chr(34) & strFilePath & Chr(34), vbNormalFocus ' Use Windows Explorer to launch the file
    End If

This code is used after the section to open the form if Acrobat is not the default program
Code:
    If Not (usesAcrobat) Then
        VBA.Shell "Explorer.exe " & Chr(34) & strFilePath & Chr(34), vbNormalFocus ' Use Windows Explorer to launch the file
    End If

Editing the form: Below is the code necessary for VBA to open the form (usually in the background) and start editing it

Code:
    Dim AcroApp As Acrobat.CAcroApp  'Used to open Acrobat App
    Dim theForm As Acrobat.CAcroPDDoc  'Used for opening the form
    Dim jso As Object   'Javascript object for the PDF form
    Dim fldTextField As Object  'Used for the text field in the PDF document
 
    Set AcroApp = CreateObject("AcroExch.App")
    Set theForm = CreateObject("AcroExch.PDDoc")
 
    theForm.Open (strFilePath)   'Opens the document for editing in the background

Closing the form: Below is code that closes the vba editing session, usually at the bottom of a function:

Code:
    theForm.Save PDSaveFull, strFilePath
 
    theForm.Close
 
    AcroApp.Exit
    Set AcroApp = Nothing
    Set theForm = Nothing

Editing a text field value: This edits the value of the text field named "Demo Text Field". CStr ensures any string passed to the text field is indeed a string. It tends to error out when not passed a string. Nz sets the value if the variable passed is NULL; CStr doesn't like working with null values, so using both of these is a good habit

Code:
   Dim fldTextField As Object
   Set fldTextField = jso.getField("Demo Text Field")

strText = "Some Text to be placed in a field"

    'Set the value of the text
    fldTextField.Value = CStr(Nz(strText, ""))

Editing a check box value: Check boxes don't work with TRUE or FALSE values through VBA, it uses the string value of "Yes" or "No". If that doesn't work, use "On" or "Off" (for some reason that works in a different form produced in a different way).

Code:
    Set chkCheckblock = jso.getField("Demo Check Field")
    check = TRUE
 
    'Set the value of the check block. "On" is used for true and "Off" for false
    chkCheckblock.Value = IIf(check, "Yes", "No")

Page Templates: Page Templates are pages within the PDF that can be cloned or copied. Generally, page templates should be a hidden page from the user so the user doesn't fill them template with data and end up cloning the same data to multiple pages. By default, the "Page Templates" menu is also hidden in Acrobat. To get to it, go to 'Tools' - (dropdown for more) - Check ' Document Processing' then under ' Document Processing' select 'Page Templates'.
DocumentTemplates.JPG



To create a template, highlight the page you want to convert to a template under "Page Thumbnails", and under the "Page Templates" name it then "Add".


PageTemplate.JPG


Unchecking the eyeball will hide the template page from the user. You'll have to recheck it IOT edit the templates again later on if needed.
 

Attachments

  • Demo Adobe Forms.accdb
    776 KB · Views: 16
  • LockAfterSigning.js.txt
    345 bytes · Views: 11
Last edited:

joshuar

New member
Local time
Today, 22:33
Joined
Dec 15, 2020
Messages
7
Spawning pages from Template without Renaming fields: Templates are pages within the PDF that can be cloned or copied. By default, all fields on the new page are renamed and don't link to each other. It is also created as the last page. With the example below, we create a page from a template named "Page" that does not rename the fields. Keep in mind that all pages spawned without renaming will have "linked fields", i.e. you change the value of a text box on one page it changes the value of the text boxes on all pages where the text box name is the same.


Code:
  Call jso.spawnPageFromTemplate("Page", jso.numPages, False) '"Page" is the template name embedded in the PDF document

  '"jso.numPages" is a basic function to get the total number of pages in the current document
   'It's used here to tell the spawnPageFromTemplate function to spawn the template after the last page
   '"false" at the end prevents any fields from being renamed

Spawning pages from Template AND renaming fields: This is the default action of this function, it prevents fields from being linked to other pages. Usually it appends P<pageIterationNumber>.<TemplateName>. to the begining of each field with 1 being the starting iteration page. (example "P1.Page.Template Text Field"). If I expect the Templates to be continuation pages of say an inventory list, I'll name all the row fields of the first non-spawned page with P0.<templateName>.fieldName (example: "P0.Page.Template Text Field") This allows me to start with P0 and parse through a list in VBA and iterate through to the template continuation pages.

Unfortunately, spawning either renames all fields or doesn't. It's all or nothing. If you want to have, say, a document number on each page linked together, you'll have to create it by adding the field for it with the same name on each spawned page after the spawning.

Below's the code; not sure why VBA doesn't require 'Call' before the function in this usage:

Code:
jso.spawnPageFromTemplate ("Page")

Adding Fields and Changing Properties: These are all required parameters. It took me a while to figure out the correct syntaxt for the coordinates array, but it seems to like it this way. With the example below, we'll make a text box with multiple lines, font size of 10, and we'll make the text upside down.

Code:
  Dim coords() As Variant 'Used to hold the coordinates of left bottom corner and right top of the text box

                         '(Left, Bottom, Right, Top) measured from the bottom of the form and left
  coords = Array(50, 300, 430, 350)
  'an 8x11" landscape document is usually 612x792

  Set fldTextField = jso.addField("Field Name", "text", 0, coords)
                                                   'Field name, type, page to add field to, coordinates
                                      'field types: text, signature, button, checkbox, radiobutton, combobox, listbox

  fldTextField.Value = CStr("This is a new text box added from VBA")
  fldTextField.multiline = True 'Makes the new text block multiline
  fldTextField.textSize = 10 'Sets the font size
  fldTextField.rotation = 180 ' Sets the field to upside down. Can use 0, 90, 180, 270

Removing Fields: An upside down text block may be totally unnecessary, so let's remove it

Code:
jso.removeField ("Field Name")

Adding a Signature: In some cases of spawning, you may want to add a signature to the last and only last spawned page. Otherwise, you'll end up with a signature block on each spawned page. And a signature is worthless in PDFs if it doesn't lock out fields that are being "signed off" on. As stated before, setting the fields to be locked through Javascript (or in this case VBA) is a privileged function that can't be executed by the form or VBA, so for the below code to really do it's job, make sure that .js file is under the javascript directory. The VBA will still run and ignore the errors without it, but the form won't be set to lock anything when the block is signed.

Code:
  Dim coords() As Variant
  coords = Array(50, 200, 430, 250)

  Call jso.addField("Signature Field", "signature", 0, coords)

  varLockObjects = "Demo Text Field,Demo Check Field" 'Comma seperated list of fields to lock (or not lock when exclude used) after signing

  On Error Resume Next ' avoiding errors if the LockAfterSigning.js is not installed
  Call jso.LockAfterSigning("Include", varLockObjects, "Signature Field")
  ' (lock type, objects to lock, name of signature field to set lock for)
  'lock types: exclude, include, all

    On Error GoTo 0
 

joshuar

New member
Local time
Today, 22:33
Joined
Dec 15, 2020
Messages
7
Forewarning: These next two functions give me errors even when using JavaScript through Acrobat as well as VBA, so I don't think it's a problem with VBA or syntax.

Acrobat errors: “(void 0) is not a constructor
(void 0) is not a constructor
can't convert Error to string”

VBA Error was “Run-time error 1001”

Set a calculation Field: As stated, this produces errors so I set it to ignore the errors. This also takes a lot of recalculation time, so producing long forms with multiple calculation fields can be a frustrating wait for the user (note that when spawning and renaming fields, calculation fields retain the original template names of the fields they are calculating in the formula). In addition, those errors thrown in Acrobat make it so the form cannot be digitally signed till the form is first closed and reopened after being produced. That all said, in the project I'm working on, I've elected not to use this function and just set the calculated fields value to a static one through VBA. Sucks because if there is a change on the form, the user has to manually adjust everything that's effected.

Oddly, I can get a variation of this Calculate action to work in a different project, but can't get it to work at all in the attached ACCDB. But it's here for reference. Notice the double quotes; yes, the array of field names that need to be added as Identified fields need to be passed on with Quotes within the string. The Semi colon at the end needs to also be passed on within the string.

Code:
  On Error Resume Next '.setAction causes errors but still works, so we ignore them with this
     Set fldTotal = jso.getField("Demo Total Cost")
     Call fldTotal.setAction("Calculate", "AFSimple_Calculate(""PRD"",[""Demo QTY"",""Demo Unit Price""]);")
                      'Other simple calculations: ("SUM"), product ("PRD"), average ("AVG"), minimum ("MIN"), or maximum ("MAX")
  On Error GoTo 0 'Re-allow errors


Set an Icon (Picture): There are no picture fields in Acrobat for forms, but there are buttons with Icons so we can use buttons for picture fields. The button on original document must be set to an Icon layout (button properties - Options - Layout). I haven't figure out how to set this property through code yet. Unfortunately, the code to set the "icon" produces errors in Acrobat here, but it still does the job. It first imports a picture from file as an icon then sets the button to that icon.

Code:
  strFilePath = "c:\myPictureFile.png"

  Call jso.importIcon("demoicon", strFilePath)
  Set fldSendTo = jso.getField("Button1")
 
  On Error Resume Next
      Call fldSendTo.buttonSetIcon(jso.getIcon("demoicon"))
  On Error GoTo 0 'Re-allow errors

Opening documents from file/form library: In order to do the above, I have to ensure the picture is actually in the file system somewhere. I'd much rather get the file directly from a record attachment, but I haven't figured out how to do that yet. Same with forms; I wish I knew how to copy a template form from a table that acts as a form library over to another table holding all filled out forms then open that record attachment and make it directly editable via VBA. But for now, the template form and picture file needs to be pulled from the "form library" and placed somewhere on the file system. In the case below, I am placing it in the temp directory as defined by the user's environmental variables:

Code:
Private Function createForm(strFormNum, strName)

On Error GoTo ErrorHandler
   Dim dbs As DAO.Database
   Dim rst, rstChild As DAO.Recordset
   Dim fldAttach As DAO.Field
   Dim strTempDir As String
   Dim strFileName As String

   Dim strFilePath As String ' This variable is being used as a global variable for the sake of this demo

   strTempDir = Environ("Temp")
   If Right(strTempDir, 1) <> "\" Then strTempDir = strTempDir & "\" ' Make sure the path always ends with a backslash.
   Set dbs = CurrentDb

   Set rst = dbs.OpenRecordset("Select * From fileLibrary Where fileName = '" & strFormNum & "'")

   Set rstChild = rst.Fields("Attachment").Value

   rst.MoveFirst

   Set rstChild = rst.Fields("Attachment").Value ' -- The FileName field in Table1 hold the name of the file

   strFilePath = strTempDir & strName & "_[" & Format(Now(), "yyyy-MM-dd-hhmmss") & "]." & rstChild.Fields("FileType") 'name the file - time is appended so I don't make files with the same name overwriting versions I may want to keep.

   If Dir(strFilePath) <> "" Then ' the file already exists--delete it first.
       VBA.SetAttr strFilePath, vbNormal ' remove any file attributes (e.g. read-only) that would block the kill command.
       VBA.Kill strFilePath ' delete the file.
   End If

   Set fldAttach = rstChild.Fields("FileData") ' -- FileData field in Table1 hold The binary data of the file.
   fldAttach.SaveToFile strFilePath ' -- Save the file

   rst.Close ' -- close the rs and clean
   Set rst = Nothing
  
   createForm = strFilePath

Exit Function

ErrorHandler: ' Error-handling routine.
   MsgBox (Err.Number & Err.Description)
   Resume Next
End Function

Most of this code and how to was borrowed and consolidated here for reference. Hope it helps some guy doing late night google searches figure out what took me way to long to figure. Happy form filling!
 
Last edited:

isladogs

CID VIP
Local time
Today, 21:33
Joined
Jan 14, 2017
Messages
14,420
Hi joshuar
Many thanks for your time and effort in uploading all of this to the forum.
I've never used Adobe Forms so would be interested to know what the main benefits are from your perspective.
I did download your DEMO but as I don't have the full version of Acrobat on this PC, I get reference errors and can't proceed.

I do own an ancient copy of Acrobat - 9.0 IIRC so may reinstall it at some point to see what this does.
Alternatively is it possible to change to late binding and do without the Acrobat reference?
 

joshuar

New member
Local time
Today, 22:33
Joined
Dec 15, 2020
Messages
7
Hi joshuar
Many thanks for your time and effort in uploading all of this to the forum.
I've never used Adobe Forms so would be interested to know what the main benefits are from your perspective.
I did download your DEMO but as I don't have the full version of Acrobat on this PC, I get reference errors and can't proceed.

I do own an ancient copy of Acrobat - 9.0 IIRC so may reinstall it at some point to see what this does.
Alternatively is it possible to change to late binding and do without the Acrobat reference?
I don't think it is possible to activate many of the functions described here without Acrobat. It might work with Acrobat 9, but keep in mind I'm using Acrobat XI and haven't tested it with any earlier versions (the Scripting guides that most of this stuff came out of was from 7.0 though). I'm not sure if it's able to just use Acrobat.tlb (the reference library file) ala carte and be able to execute it with that, but I'd imagine it wouldn't work.
 

isladogs

CID VIP
Local time
Today, 21:33
Joined
Jan 14, 2017
Messages
14,420
Thanks. So what are your main reasons for doing this?
You've obviously put in a lot of work. What does this provide that isn't available in Access itself or possibly in something like Crystal Reports?
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 13:33
Joined
Oct 29, 2018
Messages
14,095
This thread is a list of a few VBA tricks to getting information from an Access database to a PDF form.

I wish I knew how to do this stuff about a decade ago for a big project I was working on, but the resources on how to do it were pretty limited at the time; I had to settle for Access Report formats with the forms little more than pictures in the background- painful. Resources are still somewhat limited, so below are a few consolidated cliff notes on how to do it. If it wasn’t for a few more recent postings from Karl Heinz Kremer on the subject, I’d likely still be producing forms with Access reports. Big thanks to him.

There are 2 attached documents:
  • LockAfterSigning.js(.txt) is a script that must be located in the javascript folder of Acrobat. This allows VBA to set lock (setLock) fields for a signature block. The javascript used to do this is a privileged method that can’t be called directly from VBA. Remove the .txt extension to use. The file should be placed in a directory similar to below:
    C:\Program Files (x86)\Adobe\Acrobat 11.0\Acrobat\Javascripts\

  • Demo Adobe Forms.accdb has two forms in it:
    • simpleForm produces an example form and each button does one of the functions listed below to alter the form or fill it
    • Make2062 has a button that puts it all together to produce a pretty lengthy inventory list with digital signatures (a DA2062) from dummy data in tbl2062Data.
Adding the Acrobat library to the VBA project: In order to use most of these Acrobat functions, you will have to enable the Acrobat reference library in VBA. In the Microsoft Visual Basic for Applications, 'Go to "Tools - References" and select "Adobe Acrobat 10.0 type library" or "Acrobat"

Opening the form:
With Acrobat, I can open the form, have VBA adjust stuff in it and see the changes as they happen. If the PDF document is open in Adobe Reader, this doesn't happen and it doesn't really change anything in the document. If Adobe Reader is the default application for PDFs, I wait till the VBA is done making the adjustments in the form prior to opening the form. That said, I use the code below to do check if PDFs open with Acrobat by default:

Code:
'Check if Acrobat is default and open form if it is

Private Function checkForAcrobat(ByVal sFile$)
    strFileAssociation = GetFileAssociation(sFile)
    If strFileAssociation Like "*Acrobat.exe" Then
        checkForAcrobat = True
    Else
        checkForAcrobat = False
    End If

End Function

Private Function GetFileAssociation$(ByVal sFile$)
    GetFileAssociation = "File not found !"
    If Dir(sFile) = "" Or sFile = "" Then Exit Function
    GetFileAssociation = "No association found !"
    Dim i&, E$: E = String(260, Chr$(0))
    i = FindExecutable(sFile, vbNullString, E)
    If i > 32 Then GetFileAssociation = Left$(E, InStr(E, Chr$(0)) - 1)
End Function

This code is used before the section of VBA that modifies the form. strFilePath is a string variable with the path to the PDF form:

Code:
   strFilePath = "C:\PathToPDF\MyForm.pdf"
    usesAcrobat = checkForAcrobat(strFilePath)
    If usesAcrobat Then
        VBA.Shell "Explorer.exe " & Chr(34) & strFilePath & Chr(34), vbNormalFocus ' Use Windows Explorer to launch the file
    End If

This code is used after the section to open the form if Acrobat is not the default program
Code:
    If Not (usesAcrobat) Then
        VBA.Shell "Explorer.exe " & Chr(34) & strFilePath & Chr(34), vbNormalFocus ' Use Windows Explorer to launch the file
    End If

Editing the form: Below is the code necessary for VBA to open the form (usually in the background) and start editing it

Code:
    Dim AcroApp As Acrobat.CAcroApp  'Used to open Acrobat App
    Dim theForm As Acrobat.CAcroPDDoc  'Used for opening the form
    Dim jso As Object   'Javascript object for the PDF form
    Dim fldTextField As Object  'Used for the text field in the PDF document
 
    Set AcroApp = CreateObject("AcroExch.App")
    Set theForm = CreateObject("AcroExch.PDDoc")
 
    theForm.Open (strFilePath)   'Opens the document for editing in the background

Closing the form: Below is code that closes the vba editing session, usually at the bottom of a function:

Code:
    theForm.Save PDSaveFull, strFilePath
 
    theForm.Close
 
    AcroApp.Exit
    Set AcroApp = Nothing
    Set theForm = Nothing

Editing a text field value: This edits the value of the text field named "Demo Text Field". CStr ensures any string passed to the text field is indeed a string. It tends to error out when not passed a string. Nz sets the value if the variable passed is NULL; CStr doesn't like working with null values, so using both of these is a good habit

Code:
   Dim fldTextField As Object
   Set fldTextField = jso.getField("Demo Text Field")

strText = "Some Text to be placed in a field"

    'Set the value of the text
    fldTextField.Value = CStr(Nz(strText, ""))

Editing a check box value: Check boxes don't work with TRUE or FALSE values through VBA, it uses the string value of "Yes" or "No". If that doesn't work, use "On" or "Off" (for some reason that works in a different form produced in a different way).

Code:
    Set chkCheckblock = jso.getField("Demo Check Field")
    check = TRUE
 
    'Set the value of the check block. "On" is used for true and "Off" for false
    chkCheckblock.Value = IIf(check, "Yes", "No")

Page Templates: Page Templates are pages within the PDF that can be cloned or copied. Generally, page templates should be a hidden page from the user so the user doesn't fill them template with data and end up cloning the same data to multiple pages. By default, the "Page Templates" menu is also hidden in Acrobat. To get to it, go to 'Tools' - (dropdown for more) - Check ' Document Processing' then under ' Document Processing' select 'Page Templates'.
View attachment 87881


To create a template, highlight the page you want to convert to a template under "Page Thumbnails", and under the "Page Templates" name it then "Add".


View attachment 87882

Unchecking the eyeball will hide the template page from the user. You'll have to recheck it IOT edit the templates again later on if needed.
Hi @joshuar. Welcome to AWF!

I will definitely take a look at your demo later. Thanks for sharing.

Have you by any chance seen my demo at


Sent from phone...
 

joshuar

New member
Local time
Today, 22:33
Joined
Dec 15, 2020
Messages
7
Yes, your previous post on the subject helped me a lot and I think brought me to this forum. I actually modified and used theDBguyPDFDemo.accdb to play around with a couple of these concepts initially, so thank you for that. The limitation I couldn't figure out was how to spawn pages for continuous forms in the PDF. That's why I ended up going this route over producing everything as an XFDF.
 

isladogs

CID VIP
Local time
Today, 21:33
Joined
Jan 14, 2017
Messages
14,420
Please could you answer my questions in post #6
 

joshuar

New member
Local time
Today, 22:33
Joined
Dec 15, 2020
Messages
7
Thanks. So what are your main reasons for doing this?
You've obviously put in a lot of work. What does this provide that isn't available in Access itself or possibly in something like Crystal Reports?
I'm in the Army and we still manage property using archaic paper based forms from the 1980s. Fortunately these forms have transitioned over to PDFs and can use digital signatures (which is another key feature of the PDF that we need). I am unfamiliar with Crystal Reports but even if I did familiarize myself with it, I'd have a long road of trying to get it authorized for use on our networks. MS Office and Acrobat is already in our baseline image.

The video below is version 1 of the project I'm updating now. This will be version 3 and PDF filling will be part of it.

 

Users who are viewing this thread

Top Bottom