data type mismatch in criteria expression, please help (1 Viewer)

[Dopeyjh]

Hi,
I've created a combo box that when you click on the list it brings up 3 columns with a number field and then a first and last name field. Note that this data is stored in a different table. When selected/changed, i want the data to input in the current table fields accordingly...

So here's what i've got...

Private Sub AgentId_Change()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("tblAgent", dbOpenDynaset)
With rs
.MoveFirst
.FindFirst "AgentId = """ & Me.AgentId & """"
If .NoMatch Then
'MsgBox "No Match Found"
Else
Me.AgentFirstName = ![AgentFirstName]
Me.AgentLastName = ![AgentLastName]
Me.AgentStatus = ![AgentStatus]

End If
End With
Set rs = Nothing

End Sub


I have the same code in another table that im using and it works perfectly, except in this new forum i've created it will not work. I keep getting the "data type mismatch in criteria expression" error. Or it doesn't populate the fields and doesnt give me an error...

Any ideas?

 

[raskew]

Hi -

If AgentID is a number, then:
.FindFirst "AgentId = """ & Me.AgentId & """"

...returns a string. Try:

.FindFirst "AgentId = " & Me.AgentId

I seldom use FindFirst, but in my notes I've found:
rs.FindFirst "[" & keyname & "] = " & keyvalue

You might give that a try if the first suggestion doesn't work.


Bob

 

[pbaldy]

I suspect Bob has already solved your problem, but I wanted to add a thought. I would open the recordset on an SQL statement that only returned the single desired record, rather than pulling the whole table over the wire.


Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblAgent WHERE AgentId = " & Me.AgentId, dbOpenDynaset)

 

[Dopeyjh]

Worked like a charm...Thanks soooooooo much!