I am fairly new to MS Access and VBA. My goal with this code is to import data from a MS Word Macro-Enabled Document into my database. I found a few tutorials online how to do this but I have ran into a run-time error "91: Object Variable or With block variable not set". It gets to line 29 and then jumps to my error section. Any help is much needed
My code:
Code:
Function GetWordData()
Dim appWord As Word.Application
Dim docm As Word.Document
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDocmName As String
Dim blnQuitWord As Boolean
On Error GoTo ErrorHandling
strDocmName = "C:\C:\Users\maxm\Documents\" & _
InputBox("Enter the name of the Word contract " & _
"you want to import:", "Import Contract")
Set appWord = GetObject(, "Word.Application")
Set docm = appWord.Documents.Open(strDocmName)
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Users\maxm\Documents\" & _
"importtesting.accdb;"
rst.Open "tblContracts", cnn, _
adOpenKeyset, adLockOptimistic
With rst
.AddNew
!FirstName = docm.FormFields("fldFirstName").result
!LastName = docm.FormFields("fldLastName").result
!Company = docm.FormFields("fldCompany").result
!Address = docm.FormFields("fldAddress").result
!City = docm.FormFields("fldCity").result
!State = docm.FormFields("fldState").result
!ZIP = docm.FormFields("fldZIP1").result & _
"-" & docm.FormFields("fldZIP2").result
!Phone = docm.FormFields("fldPhone").result
!SocialSecurity = docm.FormFields("fldSocialSecurity").result
!Gender = docm.FormFields("fldGender").result
!BirthDate = docm.FormFields("fldBirthDate").result
!AdditionalCoverage = _
docm.FormFields("fldAdditional").result
.Update
.Close
End With
docm.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "Contract Imported!"
Cleanup:
Set rst = Nothing
Set cnn = Nothing
Set docm = Nothing
Set appWord = Nothing
Exit Function
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 Function