Solved

Fill in PDF form using MS-Access data

Posted on 2014-04-02
10
5,127 Views
1 Endorsement
Last Modified: 2016-09-09
I'm trying to create a VBA routine to use my customer data in an MS-Access 2003 (mdb) database to populate a shipping request PDF form.  The problem is that I don't know the names of the fields in the PDF form.

So first I need a utility VBA routine that will simply iterate thru all the fields in the form to retrieve their names.

I've seen plenty of examples on how to populate the fields using something like:
jso.getField("topmostSubform[0].Page1[0].fieldName[0]").value = "myValue"
where jso is the JSObject from PDDoc object from AVDoc object.  There aren't any good coding examples using JSObject in VBA.

It seems the most closely guarded secret on the internet is how to use jso.getField("topmostSubform[0].Page1[0].fieldName[0]") more dynamically so that I can iterate thru all the fields in a PDF form using VBA code in Access.

I work for the Navy (DoD) so my machine is locked down and I cannot install any third party software.  I'm actually running Access 2010 but because the Navy has the machines so locked down and they really don't understand how to implement the Trust Center settings properly I have kept my db apps as .mdb to prevent security warnings and issues for my users.

Thank you for any suggestions and advice.
1
Comment
Question by:Asteroid_Miniic
10 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 39973098
Does it absolutely have to populate the PDF form? Or could it be a simulation? And do you have access to a PDF printer?

Is there anything stopping you from make your own Access report and then "print" it to a PDF and send it off?

Just a dumb thought.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39974614
I work in a similarly constrained environment.  Our general approach to PDF forms is what Jim has suggested.  

ie:  Make a replica of the PDF form as an Access Report, and use the OutputTo command with acFormatPDF as the format parameter to export the report to PDF (and yes, this does work in Access 2010 with databases in .mdb format).

That said, to loop through fields in a PDF form, you should be able to treat it like any other collection in VBA.  

I haven't tested this myself, but look at the lines labeled  "borrowed code" in this Adobe Forums post:
http://forums.adobe.com/thread/1147373

I'm *guessing*, based on Access's fields collection, that this would also work for PDFs (assumption: you have already declared 'JSO"):

     Dim fld

     For Each fld In JSO.Fields
           Debug.Print "Field Name: " & fld.Name & "  Value: " & fld.Value
     Next 

Open in new window

0
 
LVL 44

Expert Comment

by:Karl Heinz Kremer
ID: 39975603
Based on the path for your form fields, you are using XFA forms (forms created in LiveCycle Designer, not AcroForms). To get the list of fields, you have to do some extra work.

One approach is to open up your document in Adobe Acrobat, then open up the JavaScript console (Ctrl-J or Cmd-J), then execute the following line of code:

this.xfa.data.saveXML();

Open in new window


This will reply with an XML data structure that shows you all the fields:

<?xml version="1.0" encoding="UTF-8"?>
<xfa:data xmlns:xfa="http://www.xfa.org/schema/xfa-data/1.0/"
><form1
><Page1
><TextField1
/><TextField2
/><TextField3
>0</TextField3
></Page1
></form1
></xfa:data
>

Open in new window


Now you should be able to figure out what the field names are and how to access them.
0
 

Author Comment

by:Asteroid_Miniic
ID: 39990568
Thanks all, for your input.  Sorry for the late response.

I cannot do an Access report and print to PDF printer (driver) because the PDF form is a standard shared form meaning we need to put our shipping data on the certain fields on the form and then the form is sent electronically to the shipping department and they fill in other fields on the form, which is then send to another organization who fills in the their part on the same form (the govt is a boon-doggle when it comes to processing anything--sigh).

Good news, I'm mostly there.  I found a way to retrieve the field names from a PDF form and a way to populate the form with my Access data.  I'm not at work right now, so I'll post the code later.  I just need to figure a way to keep the blank form intact by keeping it hidden (protected) from users, and have each user using a copy as their own form.  Not too hard.
0
 

Accepted Solution

by:
Asteroid_Miniic earned 0 total points
ID: 40019021
This routine will read through the fields in a PDF form and and put the name of the field as the value of the field, and then save it as a new form so you can see the field names.  WARNING: You most likely will get errors within Adobe using this routine because of the format of some fields (e.g. Date and numeric).  If this happens, simply switch to the Adobe window and click through the warnings (maybe take note of them).  You should have the following libraries (or some version thereof) included in the References: Adobe Acrobat 10.0 Type Library and AFormAut 1.0 Type Library.  The IF statements are just to avoid some Adobe warnings and are unique to my situation.   Also missing is the step to populate the form with data from the database, but I assume anyone reading this will know how to retrieve data from their database.

Here is the code:
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Asteroid_Miniic
ID: 40019027

Private Sub test_Adobe()

Dim jsFile As String
Dim joApp As AcroApp
Dim joAVDoc As AcroAVDoc
Dim joPDDoc As Acrobat.AcroPDDoc
Dim joFormApp As AFORMAUTLib.AFormApp
Dim joFormFields As AFORMAUTLib.Fields
Dim joFormField As AFORMAUTLib.Field

    On Error GoTo Error_Handler
    
    jsFile = "C:\Users\someuser\Documents\Projects\EXWC_SHIPPING_REQUEST.pdf"
    Set joApp = New AcroApp
    Set joAVDoc = CreateObject("AcroExch.AVDoc")
    If joAVDoc.Open(jsFile, "") Then
        
        Set joPDDoc = joAVDoc.GetPDDoc()
        
        Set joFormApp = CreateObject("AFormAut.App")
        Set joFormFields = joFormApp.Fields
        For Each joFormField In joFormFields
            
            If joFormField.Type = "text" Then
                If Left(joFormField.Name, 3) <> "TC " _
                And Left(joFormField.Name, 6) <> "PRICE " _
                Then
                    joFormField.Value = joFormField.Name	'<-- this is the main line of code
                End If
            Else
                If joFormField.Name = "FUNDS TYPE" Then
                    joFormField.Value = "NWCF"
                End If
                If joFormField.Name = "SHIPMENT MODE" Then
                    joFormField.Value = "Ground"
                End If
            End If

        Next joFormField
        
        jsFile = "C:\Users\someuser\Documents\Projects\EXWC_SHIPPING_REQUEST1.pdf"
        joPDDoc.Save PDSaveFull, jsFile
        joPDDoc.Close
    End If

Exit_Handler:
    joAVDoc.Close True
    Set joPDDoc = Nothing
    Set joAVDoc = Nothing
    Set joApp = Nothing
    MsgBox "Done processing"
    Exit Sub
    
Error_Handler:
    MsgBox Err.Number & ": " & Err.Description, vbCritical, "Adobe Testing"
    GoTo Exit_Handler
    Resume

End Sub

Open in new window

0
 
LVL 38

Expert Comment

by:Jim P.
ID: 40019071
I'm glad you resolved your issue. I'm going to add this to my personal knowledge base for future use.

Thanks for using EE.
0
 

Author Comment

by:Asteroid_Miniic
ID: 40021870
Jim P, you're welcome.  Please share with anyone who gets stuck on this issue.

G
0
 

Author Closing Comment

by:Asteroid_Miniic
ID: 40026812
Solves my problem.
0
 

Expert Comment

by:frankie cunes
ID: 41790846
Helpful suggestions . For my two cents if others are requiring a Taiwan Synology Developer Guide File Hosting Module , my family came across a fillable document here http://goo.gl/3zl7eV.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now