Word to Access Import error (xpost VBA forum)

[cynaptic]

I have taken this code and tried to modify for a small project. I am not a VBA wizard and have come unstuck. The following code produces a compile error "type mismatch" - I don't know what that means, could someone please enlighten me and suggest a solution.

This seems to be the offending line:

strDocName = "C:\Contracts\" & _

TIA

Option Compare Database
Option Explicit

Sub GetWordData()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim rst As DAO.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&quot

Set appWord = GetObject(, "Word.Application&quot
Set doc = appWord.Documents.Open(strDocName)

Set rst = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\My Documents\" & _
"SoftwareAcademyContacts.mdb;"
rst.Open "tblQuestionnaire", cnn, _
adOpenKeyset, adLockOptimistic

With rst
.AddNew
!Name = doc.FormFields("Name&quot.Result
!Location = doc.FormFields("Location&quot.Result
!University = doc.FormFields("University&quot.Result
!Qualification = doc.FormFields("Qualification&quot.Result
!GraduationDate = doc.FormFields("GraduationDate&quot.Result
!Looking = doc.FormFields("Looking&quot.Result
!CurrentState = doc.FormFields("CurrentState&quot.Result
!CurrentStateExpand = doc.FormFields("CurrentStateExpand&quot.Result
!HearAbout = doc.FormFields("HearAbout&quot.Result
!HearAboutExpand = doc.FormFields("HearAboutExpand&quot.Result
!SAPurpose = doc.FormFields("SAPurpose&quot.Result
!ScottishOnly = doc.FormFields("ScottishOnly&quot.Result
!Connection = doc.FormFields("Connection&quot.Result
!ConnectionExpand = doc.FormFields("fldAdditional&quot.Result
!SADoneforme = doc.FormFields("SADoneforme&quot.Result
!RateService = doc.FormFields("RateService&quot.Result
!Methods = doc.FormFields("Methods&quot.Result
!MethodsExpand = doc.FormFields("MethodsExpand&quot.Result
!SuccesfulMethods = doc.FormFields("SuccesfulMethods&quot.Result
!SuccesfulMethodsExpand = doc.FormFields("SuccesfulMethodsExpand&quot.Result
!SubmittedCV = doc.FormFields("SubmittedCV&quot.Result
!Impression = doc.FormFields("Impression&quot.Result
!ImpressionExpand = doc.FormFields("ImpressionExpand&quot.Result
!Improvements = doc.FormFields("Improvements&quot.Result
.Update
.Close
End With
doc.Close
If blnQuitWord Then appWord.Quit
MsgBox "Questionnaire Imported!"
Cleanup:
Set rst = Nothing
Set doc = Nothing
Set appWord = Nothing
Exit Sub
ErrorHandling:
Select Case Err
Case -2147022986, 429
Set appWord = CreateObject("Word.Application&quot
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

 

[RickSpr]

I copied and pasted your code in a module and tried to compile it. I'm getting a type mismatch error too, but not in the same place. I'm getting it on the line:
"Data Source=C:\My Documents\" & _

You've defined rst as a DAO Recordset variable, but you're attempting to assign a connection string to it. In a later statement you're using ad... constants when trying to access records with it. It looks like you meant this to be an ADO Recordset. Rick Sprague