Amanda:
As you are dealing with the same set of form fields and the same table each
time you will know the number of columns in the table and data type of each
one, so as you loop through the FormFields collection of the Word Document
you can increment an integer variable n. You can then examine the value of n
each time and wrap the value in the appropriate delimiters. Extending my
example so that the document has 4 form fields and the table has 4 columns
FirstName, LastName, DateOfBirth and Salary the first two are text data type,
the third date/time and the last a number, so the code would be amended like
so:
Sub GetWordForm(strPath As String)
On Error GoTo Err_Handler
Dim objWord As Object
Dim objDoc As Object
Dim fld As Word.FormField
Dim cmd As ADODB.Command
Dim frm As Form
Dim strSQL As String
Dim strValueList As String
Dim n As Integer
' if Word open return reference to it
' else establish reference to it
On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err.Number = 429 Then
Set objWord = CreateObject("Word.Application")
End If
AppActivate "Microsoft Word"
On Error GoTo Err_Handler
Set objDoc = objWord.Documents.Open(strPath)
' loop through form fields in document and build value list
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13889
For Each fld In objDoc.FormFields
n = n + 1
Select Case n
Case 1, 2 ' text field so delimit with quotes
strValueList = strValueList & ",""" & fld.Result & """"
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13889
Case 3 ' date field so delimit with hashes and
' format date in US short date format
strValueList = strValueList & ",#" & _
Format(fld.Result, "mm/dd/yyyy") & "#"
Case 4 ' number field so no delimiters
strValueList = strValueList & "," & fld.Result
End Select
Next fld
' remove leading comma
strValueList = Mid(strValueList, 2)
' insert row into table
strSQL = "INSERT INTO MyContacts(FirstName,LastName, DateOfBirth,
Salary)" & _
"VALUES(" & strValueList & ")"
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = strSQL
cmd.Execute
objDoc.Close
Set objDoc = Nothing
Set objWord = Nothing
Exit_here:
On Error GoTo 0
Exit Sub
Err_Handler:
MsgBox Err.Description & " (" & Err.Number & ")"
Resume Exit_here
End Sub
Note that the date is also formatted. This is because date literals in
Access must be in US date format or otherwise internationally unambiguous.
This is important to us Yurpeans, who unlike Mercans, use dd/mm/yyyy as our
standard short date format, so without formatting Access would interpret 4
July here as 7 April. For use with US formatted dates the formatting is not
actually necessary, but it does no harm and it internationalizes the
application if you leave it in.
By looping through the FormFields collection in order it is necessary for
the columns in the column list in the SQL statement to be in the same order
of course.
Ken Sheridan
Stafford, England
"AmandaH" wrote:
>
> How would a statments like that work? What would it look like
>
> 
Our Donation Goal
: Why Donate? : Donate Today! : Donate Anonymously (ਗੁਪਤ) : Our Family of Supporters 
Linear Mode
