Troubleshoot code - Spot deliberate mistake

[Guest]

Can someone look at this code and tell me if there is anything blatantly
obviously wrong with it that I cannot see. All file references and field
names and references are ok but I keep getting one of three error messages
when I run the code in VB. If I can't get this to work (extracting data at
access from word into access) I need to be able to click a button in word
form to transfer data to table.

Error message is currently! - invalid SQL statement ;expexted "DELETE",
"INSERT", "PROCEDURE", "SELECT" or "UPDATE"

-

Option Compare Database

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 = "H:\studentdatabase\StudentForm.doc"



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

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=H:\studentdatabase\" & _
"Student Records.mdb;"
rst.Open "tblStudent Database - Full Details", cnn, _
adOpenKeyset, adLockOptimistic

With rst
.AddNew
!StudentNumber = doc.FormFields("StudentNumber").Result
!FirstName = doc.FormFields("firstName").Result
!Surname = doc.FormFields("Surname").Result
!IDNumber = doc.FormFields("IDNumber").Result
!DitaCode = doc.FormFields("DitaCode").Result
!Course = doc.FormFields("Course").Result
!DateofBirth = doc.FormFields("DateofBirth").Result
!Address1 = doc.FormFields("Address1").Result
!Address2 = doc.FormFields("Address2").Result
!Address3 = doc.FormFields("Address3").Result
!City = doc.FormFields("City").Result
!Postcode = doc.FormFields("Postcode1").Result & _
"-" & doc.FormFields("Postcode2").Result
!PhoneNumber = doc.FormFields("PhoneNumber1").Result & _
"-" & doc.FormFields("PhoneNumber2").Result
!MobileNumber = doc.FormFields("MobileNumber1").Result & _
"-" & doc.FormFields("MobileNumber2").Result
!Mode = doc.FormFields("Mode").Result
!Status = doc.FormFields("Status").Result
!Comment = doc.FormFields("Comment").Result
!DisclEmployer = _
doc.FormFields("DisclEmployer").Result
!DisclNB = _
doc.FormFields("DisclNB").Result
.Update
.Close
End With
doc.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "Data 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

 

[Van T. Dinh]

As a guess, try:

rst.Open "tblStudent Database - Full Details", cnn, _
adOpenKeyset, adLockOptimistic, adCmdTable

(note the extra Options argument value adCmdTable. See ADO Help)