Not in List Event generates type mismatch error

[dpedley]

I have been using the simple code (below) for a not in list event for years without a problem. In this particular db I keep getting the message "type mismatch runtime error 13" when executing the 'Set tblONIL = DB.OpenRecordset("tblPosition", dbOpenDynaset)' line. It occurs in any Not in List Event on any form, referenced to any table. I have checked all the references, and repaired and recreated it from scratch with the same result. The tables are all linked to a backend which is located in the same directory as the frontend. Does anyone have any idea why this is happening?

Private Sub Position_NotInList(NewData As String, Response As Integer)
Dim NewItem As Integer
Dim DB As Database
Dim tblONIL As Recordset

If IsNull(NewData) Or NewData = " " Then
MsgBox "Select an item from the list or" & Chr(10) & Chr(13) & "cancel adding the record."
Response = DATA_ERRCONTINUE
GoTo ItemListEnd
End If

NewItem = MsgBox("Item Not Found!" & Chr(10) & Chr(13) & "Do You Want to Add This Item?", 36, "NewItem")
If NewItem = 6 Then 'New Item is YES
Set DB = DBEngine.Workspaces(0).Databases(0)
Set tblONIL = DB.OpenRecordset("tblPosition", dbOpenDynaset) ' Change table name
tblONIL.AddNew
tblONIL![Position] = NewData 'Change Field Name
tblONIL.Update
tblONIL.Close
Response = DATA_ERRADDED
Else
Response = DATA_ERRCONTINUE
End If

ItemListEnd:
End Sub

 

[kjv1611]

'Set tblONIL = DB.OpenRecordset("tblPosition", dbOpenDynaset)

So, is tblONIL supposed to be a Table (TableDef) object, or a recordset object? I would think that by the naming, it is a TableDef object.

If so, you could try this:

Dim tblONIL As TableDefs
Set tblONIL = db.TableDefs("tblPosition")

Or if it is a recordset, try just this:

Set tblONIL = db.OpenRecordset("tblPosition")

--

"If to err is human, then I must be some kind of human!" -Me

 

[dpedley]

Thanks for the reply. To answer your question. It was intended to be a recordset object. And so far has never given me a problem.

I had thought the same thing about removing the dbOpendynaset, but it didn't work either.

As I may have stated this is just odd to me. I will give the tabledef a shot though and let you know. Thanks.

 

[Remou]

You may need to change this:

Dim tblONIL As Recordset

To

Dim tblONIL As DAO.Recordset

 

[kjv1611]

Yes, you could try using specifically DAO and see if that fixes your issue, as suggested by Remou.

You'll be best off doing it this way:

Dim db As DAO.Database
Dim rONIL As DAO.Recordset [green]'rONIL instead of tblONIL for making it easier to read/follow.[/GREEN]

Also, if this is a version of Access prior to 2003, you'll probably need to set a reference to [blue]"Microsoft Direct Access Objects #.#"[/blue]

--

"If to err is human, then I must be some kind of human!" -Me

 

[dpedley]

Thanks for both of the suggestions. A user is currently working in the db, so I will try these the first chance I get and let you know what happens.

Thanks.

 

[dpedley]

The DAO change worked. Thank you both so much for your efforts.