Link to home
Start Free TrialLog in
Avatar of fishrich
fishrichFlag for United States of America

asked on

Access 2007 Module Code to Import Word Doc Fields

First off I just want to start off by thanking all of the experts for sharing their knowledge!!! You guys really make this the best page to come for research&.  Now on to my issue..
I would consider myself an experienced novice user with Access and here is my problematic scenario:
What I am trying to do is have my users fill out a word document form, and then using a module import the fields into multiple tables in my DB.
I created a Word Form 2003 type, and created the fields in it with matching names the multiple table fields names.
I have built a db in MS ACCESS 2007 and the structure is as such: TABLE1, TABLE2, TABLE3&  With fields as such: TABLE1 FIELDA FIELDB FIELDC; TABLE2 FIELD1 FIELD2 FIELD3, TABLE3 FIELD* FIELD** FIELD***
I need assistance with the proper code to create a module to import the info from the Word Doc fields into the multiple Tables in the DB.
I have tried to use something along these lines but obviously the code is old and the syntax is for a totally different version of the Jet engine.  I think this would be a good starting ground, but I dont know how to code and so I ask the experts for help!!!
http://msdn2.microsoft.com/en-us/library/aa155434(office.10).aspx
Sub GetWordData()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean
On Error GoTo ErrorHandling
strDocName = "C:\Contracts\" & _
      InputBox("Enter the name of the Word contract " & _
      "you want to import:", "Import Contract")
Set appWord = GetObject(, "Word.Application")
Set doc = appWord.Documents.Open(strDocName)
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=C:\My Documents\" & _
      "Healthcare Contracts.mdb;"
rst.Open "tblContracts", cnn, _
      adOpenKeyset, adLockOptimistic
With rst
      .AddNew
      !FirstName = doc.FormFields("fldFirstName").Result
      !LastName = doc.FormFields("fldLastName").Result
      !Company = doc.FormFields("fldCompany").Result
      !Address = doc.FormFields("fldAddress").Result
      !City = doc.FormFields("fldCity").Result
      !State = doc.FormFields("fldState").Result
      !ZIP = doc.FormFields("fldZIP1").Result & _
            "-" & doc.FormFields("fldZIP2").Result
      !Phone = doc.FormFields("fldPhone").Result
      !SocialSecurity = doc.FormFields("fldSocialSecurity").Result
      !Gender = doc.FormFields("fldGender").Result
      !BirthDate = doc.FormFields("fldBirthDate").Result
      !AdditionalCoverage = _
            doc.FormFields("fldAdditional").Result
      .Update
      .Close
End With
doc.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "Contract Imported!"
Cleanup:
Set rst = Nothing
Set cnn = Nothing
Set doc = Nothing
Set appWord = Nothing
Exit Sub
ErrorHandling:
Select Case Err
Case -2147022986, 429
      Set appWord = CreateObject("Word.Application")
      blnQuitWord = True
      Resume Next
Case 5121, 5174
      MsgBox "You must select a valid Word document. " _
            & "No data imported.", vbOKOnly, _
            "Document Not Found"

Case 5941
      MsgBox "The document you selected does not " _
            & "contain the required form fields. " _
            & "No data imported.", vbOKOnly, _
            "Fields Not Found"
Case Else
      MsgBox Err & ": " & Err.Description
End Select
GoTo Cleanup
End Sub

Thank you for you help!
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of fishrich

ASKER

k than you, i will give that a try and reply with my results
Forced accept.

Computer101
EE Admin