[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3006
  • Last Modified:

Populating Access data to Pdf form

How do I implement the procedure for populating Access data in pdf forms

I am very little about th Acrobat features and also do not know anything about Javscript. In order to learn, I made used EE search engine and have been studying all kinds of suggestions regarding this subject matter but I am stil much confused.

To get started, I loaded my Acrobat 6.0 Professional, and installed Access 2003, then implemented the steps suggested by an expert at the link below:

http://www.experts-exchange.com/Web/Graphics/Adobe_Acrobat/Q_21419116.html

In short:

(1). I created 10 fields (incliuding checkboxes) using a scanned image of a Pdf form, then provided names for the
     fields as suggested by the expert.
(2). I created an Acess Db called "Pdf_DB" with one table with the Pdf fields in them
      - Lets call them Field 1, thru Field10
(3). From here I got stuck, I did not know where to paste the recommended VBA codes for Acrobat TJB
(4). I read all the other suggested info on Javascript, I was evn more confused.

What i will really want is to let my users:
(a). Fill my Access forms for the fields bounded to my Access table, then
(b). With an On Click Event button in my Access form launch a template the PDf file to populate the data on the fileds I
      have designed on the pdf which is also expected to be bounded to the same Access table
(c). Then print the Pdf file to a Laser Printer

As you will notice here, I am not going to use the pdf form to save data directly to Access DB, I am going to use Access form for data entry and then populate the data to the pdf form

I will really like to know how to proceed from here. Any help will be appreciated.
0
billcute
Asked:
billcute
  • 17
  • 8
  • 5
  • +1
1 Solution
 
softplusCommented:
I did this the following way:
- create a form "template" in Acrobat, fill all the fields with placeholders (i.e. "<firstname>", "<lastname>", etc.). Save the field contents to a fdf-file (I think the ending is fdf).
- copy the FDF file as a template somewhere else :)
- set the document action on load to read the content of the FDF file

In the program:
- copy the original FDF file to the location of the PDF
- using standard file search+replace techniques, replace the placeholders in the FDF file with the real data (use \r for new line, etc.)
- Use shellexec to open the PDF -- the document actions will read the FDF: presto, your PDF form data is populated, the user can now edit it or just print it (you can also shell to print directly)

I used this way to avoid having to use the Acrobat API :))

Hope it works for you :)
0
 
billcuteAuthor Commented:
softplus,
Thanks for your assistance, may i see your sample DB? Please click on my userId and drop me a note
0
 
softplusCommented:
billcute, sorry, my db doesn't matter much :) - I use this code for all types of tables.. Here's more or less what I do in the program (assuming you have created the pdf / fdf) - this is all in VB code, which you can use in Access:

    FileCopy strFileTemplateFdf, strFileLocalFdf
    FileCopy strFileTemplatePdf, strFileLocalPdf

    ' read FDF-File into sText
    strText = FileTextRead(strFileLocalFdf) ' this just reads the contents of the FDF file into a string variable

    ' edit file
    strText= Replace(strText, "<Firstname>", taData!FirstName)
    strText= Replace(strText, "<Lastname>", taData!LastName)
' ... etc. until all necessary fields are replaced (could also enumerate the fields, if you have no calculated values)

    ' write back to FDF file
    call FileTextWrite(strFileLocalFdf, strText)

    ' run pdf file
    call ShellExecute(0&, "open", p_strFileLocalPdf , vbNullString, vbNullString, vbNormalFocus)

    ' done

Does that make sense? You'll need to code the FileTextRead / FileTextWrite / ShellExec similar to this:


Public Function FileTextRead$(p_strFilename$)
    On Error Resume Next
    ' reads contents of a file into a string
    Dim intFile%, strText$, lngSize&
    lngSize = FileLen(p_strFilename)
    If lngSize > 0 Then
        intFile = FreeFile
        Open p_strFilename For Binary As #intFile
        strText = Space$(lngSize)
        Get #intFile, , strText
        Close #intFile
        FileTextRead = strText
    Else
        FileTextRead = ""
    End If
End Function

Public Sub FileTextWrite(p_strFilename$, p_strContent$)
    On Error Resume Next
    ' write contents into a file
    Dim intFile%
    intFile = FreeFile
    Open p_strFilename For Output As #intFile
    Print #intFile, p_strContent
    Close #intFile
End Sub

Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Note: it might need to be done a bit differently with Access, but the main idea will remain the same.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Karl Heinz KremerCommented:
It's certainly possible to use FDF for this purpose, but it's more straight forward to modify the form fields directly, and with VBA and the JSObject that's provided by Acrobat you can actually do that.

Even though the interface is using this JSObject, you don't have to know JavaScript to use it. You need to be able to understand the description of a certain method in Acrobat's JavaScript Scripting Reference however, but at the end you are only using VB or VBA features to access this functionality. Can I assume that you have some VB background?

The question you linked to does not provide any information about filling in form fields, it creates them. This is not necessary in your case.

 Dim App As CAcroApp
        Dim PDDoc As CAcroPDDoc
        Dim jso As Object
        Dim i As Long
        Dim FileName As String
        Dim field As Object
        Dim rect(3) As Integer

        FileName = "c:\test.pdf"

        Set App = CreateObject("AcroExch.App")
        Set PDDoc = CreateObject("AcroExch.PDDoc")

        If PDDoc.Open(FileName) Then
            Set jso = PDDoc.GetJSObject
                       
            ' add a form field
            Set field = jso.getField("myFormField")
            field.value = "the data from your ACCESS database"
           
            i = PDDoc.Save(PDSaveIncremental, FileName)
        End If

Of course, if you only want to print the document, you don't have to save the file first.

Make sure you get the documents that I referenced in the old question, and lookup the printing features in the JS Scripting Reference. You can find this as Doc.print(). You will however not be able to use a fully automated print process with Acrobat 7. With Acrobat 6 you may be able to do this without any user prompts. If you don't mind the that the user gets the normal print dialog, just add "jso.print()" to your program.
0
 
billcuteAuthor Commented:
softplus,
Thanks for your help. I am in deed a novice. the best I knew how to do is scanning forms as pdf and creating the fields on the form, I am also familiar with creating fields, forms in Ms. Access but when it comes to JavScript I am totally lost. I am not really familiar with the technique. A sample Access DB will help.

Any way thanks for the codes, if you can walk me through the steps in placing the codes and where to place them, it will be great.

looking at the codes you provided above, I can conveniently create a new Module and place this:

 Public Function FileTextRead$(p_strFilename$)

I assume you would want me to place the other two codes behind the form module, I am not too sure. If I have 10 fields on the Pdf file I created how ill Access find it?

Regards
Bill
0
 
billcuteAuthor Commented:
khkremer,
Okay, I can place the code in the module or the form if you can guide me. I know how to get to forms' module and can also create a new "Module" for the code so what is the next step. Where do I place this code?
 ----> Dim App As CAcroApp

(2). And how do I create an fdf file?
0
 
Karl Heinz KremerCommented:
Do you know how to add a reference to a COM object to a VBA project? Do you know how to add a button to a project and add VBA code that gets run when the button is clicked? I don't use Access, I don't have access to Access 'til Monday morning, so you would need to get this information from another source. But I'm pretty sure that there are enough experts in this TA who can help. Once you know how to accomplish these two tasks, you need to add a reference to the Acrobat type library to your project. After that, just try to add the code that I posted to the button callback. This should open the PDF file (you need to of course change the path to your PDF file), and you also need to get the correct value from your DB. For your 10 fields, just copy the two lines that fill in the form field for every value you want to set:

 Set field = jso.getField("myFormField")
 field.value = "the data from your ACCESS database"



You can create a FDF file either manually in a text editor, or programatically by writing out line by line, or by using libs that can write out a FDF file (e.g. Adobe's FDFToolkit, which you cannot use for this project, because you would be running the toolkit on the client, which is not allowed by the license). If you want to understand the FDF format, you need to read the PDF spec.
0
 
billcuteAuthor Commented:
I know how to select a reference from reference Library and I also know how to register a DLL or copy a dll to \system32, if this is what you meant by refernce to a COM object. I know how to create, modify and place codes in the On Click event of a button.

I will try these out and get back to you on that. thanks. On creating an "fdf file", I will need some referal to where to get further info. thanks
0
 
Karl Heinz KremerCommented:
You can find the PDF reference here: http://partners.adobe.com/public/developer/pdf/index_reference.html

If you want to look at a FDF file, just take your PDF form, fill in some values and select "Advanced>Forms>Export Forms Data". This will create a FDF file. Open it in a text editor.
0
 
billcuteAuthor Commented:
khkremer,
I need some more info here for this code:

If PDDoc.Open(FileName) Then <--- "Filename", Am I substitutiing this with directory where DB resides "C:\Pdf.mdb"

 Set field = jso.getField("myFormField")         <------ "myFormField" such as "FisrtName" ?
 field.value = "the data from your ACCESS database"  <---- data from Access - What am I referencing here? table?

Since I have 10 of these fiels to add, I need more explanation.

Regards,
Bill
0
 
softplusCommented:
billcute, just another Access / PDF Link :) - http://www.igetit.net/SampleCode/Adobe_Acrobat_vba_code.asp

khkremer, just wondering, do you need the full version of Acrobat installed to use these objects? Or would the reader be enough?
0
 
Karl Heinz KremerCommented:
The "FileName" is the name of your PDF template (the file with the form fields in it that you created). When you added the form fields, you specified a name for each of them (or you accepted the default name that Acrobat assigned). If you don't remember how you named the fields, load the form back into Acrobat, select the Object Selection tool and find out what the names are (Tools>Advanced Editing>Select Object Tool).

Just copy the two lines ten times, and substitute the form field name in Acrobat on the first line, and replace the second line with a mechanism to get the data from your Access DB. As I stated before, I don't use Acccess, so I have no idea how you would retrieve information from the DB.
0
 
Karl Heinz KremerCommented:
softplus, yes you need the full version of Accrobat, Reader is not sufficient.
0
 
billcuteAuthor Commented:
softplus,
Thanks for the referral.

Cheers,
Bill
0
 
billcuteAuthor Commented:
khkremer,
What is the Acrobat Reference Library to select from the Library Window for this project ?.

Bill
0
 
Karl Heinz KremerCommented:
You need to add the "Adobe Acrobat 6.0 Type Library” reference. This step is described in this document: http://partners.adobe.com/public/developer/en/acrobat/sdk/pdf/javascript/VBJavaScript.pdf (even though it's for Acrobat 7, the COM object name is similar enough that you should not have a problem finding it).

0
 
billcuteAuthor Commented:
khkremer,
At first "Adobe Acrobat 6.0 Type Library” was not in the Reference Library but by checking again, I now found it there. basically this what I have done.

(1) Define fields in Pdf forms and save it to a folder in my "D" Drive.
(2). A created similar fields in Access2k table and save the mdb in a folder in C:\Drive.
(3). I placed your code in the On Click button and then set the fields as suggested. I also pointed to folders earlier
      mentioned in the specified directories above.
(4). I compiled my App and received no errors.

I still could not find solution to this line of code:
field.value = "the data from your ACCESS database"  <---- data from Access

I have tried several names equal to the name fields for the pdf, no luck

can we proceed by posting codes for inputting data from the "pdf" form to Ms. Access. I might get a pointer info from that code. I knew I had said I want to use Access to enter data into my Db but if your suggested code works through pdf 'while no' I will go for it.

Cheers
Bill
0
 
Karl Heinz KremerCommented:
Let me state this again: I don't use Access, I don't program for Access, I don't know how to retrieve information from an Access database. You need to find out how this is done on your own. Look at softplus' code, it uses syntax like this to retrieve data:  taData!FirstName

When you don't change the code I proposed, it should still work, and you should end up with the string "the data from your ACCESS database" in your form fields. Does this work?

I don't understand your last paragraph. Are you now trying to use a PDF form to bring data into a database? Would you use a PDF document as frontend for the database? What does 'while no' mean?
0
 
billcuteAuthor Commented:
khkremer,
I have not tried "softplus" code at all. I wanted to take it one ata time. II have used only your code for now in my experiment. I will only try softplus code if I fail to implement your code.

I have not changed anything in your code, I just could not figure out the string for "data fro Access database but dont worry, I'll keep trying.

Yes, I have decided to use a PDF form to bring data into a database - which means, I will now use a PDF document as frontend for the database in order to be on the safe side.

Regards
Bill
0
 
Karl Heinz KremerCommented:
If you want to do that, you can use JavaScript code in the PDF document, and access your database with ADBC (Adobe's ODBC wrapper). You can use this method also to fill your form fields. I would not mix the two methods (VBA and JavaScript) because it adds complexity, and you have to become familiar with two different technologies.

How do you plan on getting the data from the PDF to the database? Who's the user for this system? Are you using this, or will the PDF file be distributed to a larger group of people who fill in the form and submit it?

0
 
billcuteAuthor Commented:
Clients will come in to fill the form in my PC to apply The data will be stored on my DB.
0
 
billcuteAuthor Commented:
Softplus,
I created a module and placed the functions in it. I have follwed all your ither suggestions but need some clarifications in the following areas.

Question 1. What do you mean by your statement below:

   replace the placeholders in the FDF file with the real data (use \r for new line, etc.)

Can you provide an example on how to use this symbol "\r" in my fdf file:

Here is a copy of my fdf file:

%FDF-1.2
%âãÏÓ
1 0 obj<</FDF<</F(NewRecord.pdf)/ID[<8948055a04bc0ccbfc9b2a6b2007b62c><0535e1e2cfe4cf4b9677ee4276cd5409>]/Fields[<</T(BlockNo)/V("<BlockNo>")>><</T(HouseNo)/V("<HouseNo>")>><</T(JobID)/V("<JobID>")>><</T(JobNo)/V("<JobNo>")>><</T(LotNo)/V("<LotNo>")>><</T(PermitDate)/V("<PermitDate>")>><</T(PermitNo)/V("<PermitNo>")>><</T(ServiceType)/V("<ServiceType>")>><</T(Status)/V("<Status>")>><</T(StreetName)/V("<HouseNo>")>><</T(WConnType)/V("<WConnType>")>><</T(chkBox1)/V/Yes>><</T(dateCreated)/V("<dateCreated>")>><</T(dateExpired)/V("<dateExpired>")>>]>>>>
endobj
trailer
<</Root 1 0 R>>
%%EOF

Question 2.
Should I paste your suggested below behind a command button in an Access form?
*************
 FileCopy strFileTemplateFdf, strFileLocalFdf
    FileCopy strFileTemplatePdf, strFileLocalPdf

    ' read FDF-File into sText
    strText = FileTextRead(strFileLocalFdf) ' this just reads the contents of the FDF file into a string variable

    ' edit file
    strText= Replace(strText, "<Firstname>", taData!FirstName)
    strText= Replace(strText, "<Lastname>", taData!LastName)
' ... etc. until all necessary fields are replaced (could also enumerate the fields, if you have no calculated values)

    ' write back to FDF file
    call FileTextWrite(strFileLocalFdf, strText)

    ' run pdf file
    call ShellExecute(0&, "open", p_strFileLocalPdf , vbNullString, vbNullString, vbNormalFocus)

    ' done

0
 
billcuteAuthor Commented:
Softplus,
Using your suggested code above, will I be able to:
(1). Generate data with my pdf form and save in Access DB ?
(2). Open Access data in Pdf form?

Regards
Bill
0
 
softplusCommented:
Hi Bill
Just noticed your reply :)
Question 1: Do you have fields that need to be split to multiple lines (i.e. address blocks)?
if not, then you can ignore my comment. If you have fields that need to be split, just
add "\r" to the string where you want it to be split. Acrobat will read the \r and
put the following text on the next line. I.e. "Line 1\rLine 2\rLine 3" will make 3 lines
of text.
Question 2: I would put it into a seperate module, that way you keep the code together.
Call the sub in the module from your Command button.

and the other post:
1) no, it will not be able to read from the pdf form..
2) ... it will display your access data in the pdf form so that you can print it out.
It won't be saved in the form, e.g. you won't be able to send the pdf with form
contents by email (as it opens the data each time you open the pdf).

I hope that helps :)
0
 
billcuteAuthor Commented:
Softplus,
Thanks for your quick response, What if I want to you use the pdf form to generate files into Access DB ? any solution for that?

I will try your other suggested info and give you a feedback later.

Cheers
Bill
0
 
softplusCommented:
Bill,
You could use a PDF form to input data into access, but I think it will be a struggle to get it to work right...
and if you want to distribute it to other people, it will really be a pain... One idea more or less matching
my export to PDF forms would be to add a document event onclose (I'm not sure if it's called that :)) that
saves the contents of the pdf form data to a fdf file. You would then need to parse the FDF file directly
for all form values.
However, this is really a pain and you can't give your users timely validation information (unless you code
it in the PDF, i.e. javascript). E.g. when a user enters his email address without an @ you would need to
pop up a short message, reload the pdf, fill the form data and then let the user try again. I think it's
unnecessarily complicated :).
Just wondering -- why would you want to do it like this? Maybe there are other solutions for the "real
issue" that we haven't looked at :)
John
0
 
billcuteAuthor Commented:
John,
Thanks for your explanation, I received an error after placing the code in a module:
Compile Error:
"Invalid Outside Procedure" and debug highlighted the word below:

"strFileTemplateFdf"  ---> from this code:  FileCopy strFileTemplateFdf, strFileLocalFdf
0
 
billcuteAuthor Commented:
softplus,
I will appreciate it if you could take a minute of your time to take a look at the sample DB I created based on your code so you could tell me what I was doing wrong. Just click on my userid and drop me a line.

Regards
Bill
0
 
billcuteAuthor Commented:
softplus,
I have tried your code out but your code does not explain
(1). How to point to the pdf ' fdf files
(2). It does not provide link to Access data

So it makes it difficult to know how to get Access to populate data into pdf form using the fdf file. I will really appreciate it if you could take a look at the template I had prepared based on your suggested code. Plesae click on my userId to contact me.

Cheers
Bill
0
 
puppydogbuddyCommented:
Bill,
I made some time to research into your enquiry on "Access data to Pdf form"  it is much more involved than you are aware.  Here is what I found......
 
There are four ways to embed pdf in your application, each of which has its pros and cons. Of all the methods dscussed below, I think you would be best served if you used the virtual printer method I recommend in # 4 below.
 
1. Installing the necessary application dll files from Adobe, and using VBA code to call and interact with native Adobe.  Under this method, the Adobe appl must compiled and bound to Access before it can be utilized. This is method suggesed by khkremer.  It requires everyone to have same version of adobe or it won't run. Complete documentation / setup instructions are sketchy and difficult to find.

2.  The second method utilizes windows shell, vba  and utiizes windows drivers to emulate adobe pdf.  This will work with whatever version of Adobe is on the local machine, but is slower than native adobe.   This is the method recommended by Softplus. Complete documentation / setup instructions are sketchy and difficult to find.
 
3. The  third method is to buy a self-contained activeX pdf emulator for approx $30 from a 3rd party, It has an advantage over the first two methods in that reduces the amount of coding you have to do, and usually comes with detailed setup / coding instructions.  However, I believe that you will find this method is not as easy to implement and use as the virtual printer driver method below. For an example click on the following link:  http://www.skysof.com/
If nothing else, look at the extensive detailed documentation provided.

4. The last method, which I have used and prefer...is to buy a 3rd party virtual printer driver. For your customers, you set the driver as the default printer with pdf print button on a native access form for the public's use. I don't see any advantage of the adobe fill-in form over a native access form .  Prnter drivers are easy to set up and use, require no coding, and are very cheap. (see links below).  
                        www.speedypdf.com    my recommended solution for pdf


0
 
billcuteAuthor Commented:
puppydogbuddy,
Thank you for assisting. I have been confused from day one on this post and still very confused as to how to proceed with suggested codes by previous experts. Your suggestions are quite valuable and I appreciate it.

I have checked on the referral sites and found them very useful. I would have loved to experiment with the suggested codes of both "khkremer" and "softplus" but their codes have not produced any satisfactory results either.

I am accepting your suggestions as my answer.
Thank you.

Bill
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 17
  • 8
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now