Solved: Word Runtime error '5941'

[Mifty]

Hi,

I'm hoping someone can help please.

I've been trying to adapt Dian Chapman's code for autofilling an Access database record from a Word Autoform.

I'm not skilled in either Word or Access but have used VBA in Excel.

I've downloaded the sample db and word form from mousetrax.com Autoforms with grateful thanks to Dian

The original form and database work fine. But when I run this from Command Button 1 to check if record exists NB comments have not been changed from orig code
Private Sub CommandButton1_Click()

'declare variables for new connection and recordset and declare variables
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim vDeptCode As String
Dim vDept As String
Dim vJob1, vJob2, vJob3, vJob4 As String

'provide connection string for data using Jet Provider for Access database
vConnection.ConnectionString = "data source=c:\computorcompanion\JobLog.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
'open connection
vConnection.Open

'test connection state...this can later be commented out...but it helps
'let you know if the connection was successful when testing the code
vConnectionState = vConnection.State
If vConnectionState = 1 Then
MsgBox "The connection to this database is working!", vbInformation
Else
MsgBox "You were unable to connect to the assigned database!", vbInformation
End If

'set variable equal to whatever is entered into First & LastName form field
'so you can query the DB with this name to see if a record exists
vDeptCode = ActiveDocument.FormFields("bkDeptCode").Result
vDept = ActiveDocument.FormFields("bkDept").Result

'open a RecordSet with SQL query results...to see if first/last name matches a record
vRecordSet.Open "SELECT * FROM JobLog WHERE tJobLog!DeptCode = " & _
Chr(34) & vDeptCode & Chr(34) & "AND tJobLog!Dept = " & _
Chr(34) & vDept& Chr(34), vConnection, adOpenKeyset, adLockOptimistic

I get the error message above saying that "The requested member of the collection does not exist".
I've checked the bookmarks by using Edit|Go To|Bookmark. Also checked names in DB and Table name although code does not get to table name bit. Stops at vDeptCode = ActiveDocument.FormFields("bkDeptCode").Result. Checked that numbers in doc map to Numbers in DB
The Locals window shows Me Type ThisDocument/Document vRecordset Nothing VDept Value "" Type String

I don't know enough to know whether my problem is in the DB, doc or code...... please help:help

Thanking you in advance
Mifty

[fumei]

If it stopped there, it sure looks like there is no formfield named bkDeptCode.

Are you using Option Explicit? If you are not, it could be something as simple as a typo.

[Mifty]

If it stopped there, it sure looks like there is no formfield named bkDeptCode.

Are you using Option Explicit? If you are not, it could be something as simple as a typo.

I'll have a better look - thanks:thumb

[Mifty]

If it stopped there, it sure looks like there is no formfield named bkDeptCode.

Are you using Option Explicit? If you are not, it could be something as simple as a typo.

Hi Gerry,

I now have to admit to being very dense :doh:

I had thought that the bk in the code was identifying the bookmark

Obviously not - it was part of the bookmark name - my bad!

Thank you though, your post put me on the right track, the penny dropped almost straight away and I checked the bookmarks in the sample form. Until then I'd been in a total fog. Thank you !
Mifty

[fumei]

And use Option Explicit!!!!!

vDeptCode = ActiveDocument.FormFields("bkDeptCode").Result

BTW: do you see ANYTHING about a bookmark in that? It is a formfield that is the issue. Unfortunately people equate the two - formfields and bookmarks. They are NOT the same. Formfields have bookmarks.

And start using Option Explicit.