Run-Time Error 91 with import code

[maxmd29]

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:

 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

[ItsMe]

Where is line 29?

[June7]

With ADO, need to Set the connection and recordset.

Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset

[maxmd29]

Is there more to it than just adding in those 2 lines? I have added those 2 lines into my code, but when it gets to

!FirstName = docm.FormFields("fldFirstName").result

error 91 still comes up

[June7]

Sorry, I've never coded for import from Word doc.

Think I am looking at the article you got the code from. It was built for Access 2000, which might explain why the Set lines were omitted or that was an oversight in the article.

This thread revises the code to use CreateObject instead of GetObject and it also uses DAO instead of ADO. http://www.access-programmers.co.uk/...d.php?t=199989

[ItsMe]

Where did your original error happen, exactly? Where is table "tblContracts" located? Is it in another database?

[maxmd29]

My original error occurred at !FirstName=docm.FormFields("fldFirstName").result.

Also, table "tblContracts" is located in the same database.

[June7]

Did you try different code from the link?

[maxmd29]

I did, to no avail. I thought that I had it when I got to "set rst=CurrentDb.." but it still returned my error 91 object variable not set. It also is still failing at the same spot.

[June7]

If you want provide the files for analysis, follow instructions at bottom of my post.

[maxmd29]

I appreciate all the time and effort you have already invested in my issue. I hope that I have attached my files correctly, I have also included the word document that I am trying to get the data from.

[ItsMe]

Can you test this code in a sepearate control button and let us know if it opens your word doc?

Dim appWord As Object
Dim docm As Object
Dim strDocmName As String
strDocmName = "C:\Users\maxm\Documents\" & _
    InputBox("Enter the name of the Word contract " & _
    "you want to import:", "Import Contract")
Set appWord = CreateObject("Word.Application")
Set docm = appWord.Documents.Open(strDocmName & ".docm")
appWord.Visible = True

[ItsMe]

@June

Maybe a file picker would be more appropriate. VTW, I found a typo in the string for the path in the original code. "C:\C:\Users...

[maxmd29]

I just noticed that too after taking a break from staring that the code. Good catch but unfortunately was not the fix

[ItsMe]

I just noticed that too after taking a break from staring that the code. Good catch but unfortunately was not the fix

Are you interested in my suggestion in post #12?