FDF file format and Access

[DomFino]

Hi everyone,
I have several PDF forms that I want to fill in certain data from my Access database. I created a FDF file with the fields I need. In this example, it is a W4 form and I am only filling in Name, SSN, Address, etc.

My question is this. How can I make the fill in fields (in blue) variables so that I can run a query in Access that updates the FDF file? Any Ideas will be greatly appreciated.
Thanks
Dom

%FDF-1.2
1 0 obj
<<
   /FDF
   <<
   /F(fw4.pdf)
   /Fields
  [<</T(c1-1)/V/Yes>>
   <</T(c1-2)/V/Off>>
   <</T(f1-10)/V([COLOR=blue]lastname[/color])>>
   <</T(f1-11)/V([COLOR=blue]555 street[/color])>>
   <</T(f1-12)/V([COLOR=blue]baltimore, md 21222[/color])>>
   <</T(f1-13)/V([COLOR=blue]212[/color])>>
   <</T(f1-14)/V([COLOR=blue]22[/color])>>
   <</T(f1-15)/V([COLOR=blue]2222[/color])>>
   <</T(f1-9)/V([COLOR=blue]firstname[/color])>>
  ]
   >>
>>
endobj
trailer
<</Root 1 0 R>>
%%EOF

 

[DomFino]

Hi again,
I have managed to create a text file that outputs the data I need to make a file in FDF format. However, I am having one problem with the SSN field. The PDF file has the SSN broken into three fields xxx xx xxxx. I have the SSN coming in from the query as xxxxxxxxx. What I cannot figure out is how to parse the SSN field from the query into three chunks of data. See the code in blue below!
Any help or ideas?

Option Compare Database
Option Base 1

' ***********************************************************
Function Main()
On Error GoTo BYE
'
' *************** Declare Locals **************************
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim aryCustomerInfo()
Dim lngCount As Long
lngCount = 0
'
' ******** Fill aryCustomerInfo() array with data *****
'
strSQL = "SELECT [EmplFirstName],[EmpLMiddleInitial], [EmplLastName], [EmplAddress], [EmplCity],[EmplState], [Emplzip], [EmplSSN], [EmplMaritalStatus] FROM tblMaster"

' ***********************************************************
'
Set rs = New ADODB.Recordset
rs.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
Do Until rs.EOF
    lngCount = lngCount + 1
    ReDim Preserve aryCustomerInfo(9, lngCount)
    aryCustomerInfo(1, lngCount) = rs![EmplFirstName] + " " + rs![EmplMiddleInitial]
    aryCustomerInfo(3, lngCount) = rs![EmplLastName]
    aryCustomerInfo(4, lngCount) = rs![EmplAddress]
    aryCustomerInfo(5, lngCount) = rs![EmplCity] + " " + rs![EmplState] + "  " + rs![EmplZip]
    aryCustomerInfo(8, lngCount) = rs![EmplSSN]
    aryCustomerInfo(9, lngCount) = rs![EmplMaritalStatus]
    rs.MoveNext
Loop
'
' ***********************************************************
'
rs.Close
CurrentProject.Connection.Close
Set rs = Nothing
'
' *********** Output array info to text file *********
'
aFileNum = FreeFile
Open "X:\My Documents\CPSI\work\Query.txt" For Output As #aFileNum
'
For i = 1 To UBound(aryCustomerInfo(), 2)
    Print #aFileNum, "%FDF-1.2"
    Print #aFileNum, "1 0 obj"
    Print #aFileNum, "<<"
    Print #aFileNum, "/FDF"
    Print #aFileNum, "<<"
    Print #aFileNum, "/F(fw4.pdf)"
    Print #aFileNum, "/Fields"
    Print #aFileNum, "[<</T(f1-9)/V(firstnamemidinitial)>>" & aryCustomerInfo(1, i)
    Print #aFileNum, "<</T(f1-10)/V(lastname)>>" & aryCustomerInfo(3, i)
    Print #aFileNum, "<</T(f1-11)/V(address)>" & aryCustomerInfo(4, i)
    Print #aFileNum, "<</T(f1-12)/V(CitySTZip)>> " & aryCustomerInfo(5, i)
  '
    [COLOR=blue]Print #aFileNum, "<</T(f1-13)/V(ssn3)>>" & aryCustomerInfo(8, i)
    Print #aFileNum, "<</T(f1-14)/V(ssn2)>>" & aryCustomerInfo(8, i)
    Print #aFileNum, "<</T(f1-15)/V(ssn4)>>" & aryCustomerInfo(8, i)[/color]
  '
    Print #aFileNum, "ssn: " & aryCustomerInfo(8, i)
    Print #aFileNum, "maritalstatus: " & aryCustomerInfo(9, i)
    Print #aFileNum, "]"
    Print #aFileNum, ">>"
    Print #aFileNum, ">>"
    Print #aFileNum, "endobj"
    Print #aFileNum, "trailer"
    Print #aFileNum, "<</Root 1 0 R>>"
    Print #aFileNum, "%%EOF"
    Print #aFileNum, "*******************************************"
Next
Close #aFileNum
Btn = MsgBox("Query Saved!" & vbCrLf & vbCrLf & _
                         "Do you wish to Open in Notepad?", vbYesNo, _
                         "Query Output Results")
If Btn = vbYes Then
    Shell "Notepad.exe X:\My Documents\CPSI\work\Query.txt", vbMaximizedFocus
vbMaximizedFocus

End If
BYE:
End Function

 

[PHV]

Print #aFileNum, "<</T(f1-13)/V(ssn3)>>" & Left(aryCustomerInfo(8, i), 3)
Print #aFileNum, "<</T(f1-14)/V(ssn2)>>" & Mid(aryCustomerInfo(8, i), 4, 2)
Print #aFileNum, "<</T(f1-15)/V(ssn4)>>" & Right(aryCustomerInfo(8, i), 4)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DomFino]

PH,
That did the trick. Thanks so much for your reply. I have noticed your name on many posts throughout this fantastic site. You truly must love your work to be so dedicated to helping others. I, as I am sure many others, appreciate your contributions and expertise.
Dom