Data Type Mismatch - recordsetclone's bookmark

[JHMarshIII]

The following code fails with Data Type Mismatch at the statement:

.FindFirst "[mid] = '" & Me![MemIDLU] & "'"

mid is a autonum (long integer) so I assumed it was the quotes. MemIDLU is a control on a form. But if I remove the quotes, then I get MemIDLU is not a field or expression in the DB. I tried:

.FindFirst [mid] = " & Me![MemIDLU] & "



Yet if I float over the field in the debugger, it shows the value keyed in the form.

I guess I need help with making this code work with long integers
Thanks John ...
---------------------------------------------
Private Sub MemIDLU_AfterUpdate()
' code needs a reference to the dao object library
' Find the first record that matches exactly
' the contents of control [MemIDLU]
With Me.RecordsetClone
.FindFirst "[mid] = '" & Me![MemIDLU] & "'"
If Not .NoMatch Then
' there's a match
' synchronize the form with it's recordsetclone's bookmark
Me.Bookmark = .Bookmark
' Set focus on the Salut control
Me("Salut").SetFocus
Else
' no match
'clear the MemIDLU control
Me("MemIDLU") = Null
' Set focus back on the MemIDLU control
Me("MemIDLU").SetFocus
End If
End With

End Sub

 

[RichMorrison]

John,

Try this:

Dim myRset As Recordset

Set myRset = Me.RecordsetClone

myRset.FindFirst etc. etc

I know this way works.

RichM

 

[JHMarshIII]

John,

Try this:

Dim myRset As Recordset

Set myRset = Me.RecordsetClone

myRset.FindFirst etc. etc

I know this way works.

RichM

Ok, OT, but can you help me to understand the syntax of this statement, I am confused by the single, double quotes and the appersand:

FindFirst "[mid] = '" & Me![MemIDLU] & "'"

 

[jjturner]

Don't encase the control reference in single quotes if the datatype is long integer.

As Rich implied, you may be cutting too many corners starting your 'With' block referenced to 'Me.RecordsetClone' instead of setting a discrete recordset object to 'Me.RecordsetClone', then using the named object (i.e. - myRset) for the 'With' block reference.

If you're getting the error without the single quotes, then the next question is what kind of control type are you referencing. If it's a combobox, then you need to reference to the Value property:

.FindFirst "[mid] = " & Me![MemIDLU].Value

You may find that a cleaner solution is to assign your FindFirst criteria to a string variable prior to executing the search, as per the Help file example in Access.

HTH,
John

 

[JHMarshIII]

Resolved
memidlu is a combo control on a form. The .value and dropping the single quotes made the difference. Thanks for the structure suggestion.

John...