Finding Records Using Combo Boxes

[davegillian]

I've got a data entry form with the following piece of code which allows an existing record to be selected:



Private Sub Client_Select_AfterUpdate()

Me.DataEntry = False
Me.Contact_Details_Subform.Form.DataEntry = True

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Client No] = '" & Me![Client Select] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

[Client Select] = Null

End Sub



This works perfectly!

As you will see there's also a subform and I want to be able to select contact records for the client in question in the same way on this subform. I've used the same code structure:



Private Sub Contact_Select_AfterUpdate()

Me.DataEntry = False
Me.[Referral Details Subform].Form![Category Records Subform].Form.DataEntry = True

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Contact No] = '" & Me![Contact Select] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

[Contact Select] = Null

End Sub



...but here it doesn't work and I get Run-time error '3464': Data type mismatch in criteria expression.

The Client No field is a Text field whereas the Contact No field is an autonumber. I presume this is the reason that the same code isn't working but I can't figure out how to adapt the code to make it work.

Any help appreciated!

Cheers,

Dave

 

[Remou]

Is [Contact No] numeric? If so, you need to drop the single quotes:

[tt]rs.FindFirst "[Contact No] = " & Me![Contact Select][/tt]

If this code is not running on the subform itself, but the main form, you will need to make further changes.

 

[davegillian]

Haha I was sure I'd tried that already but it worked this time. Thanks very much!

More sleep is needed for a clear mind me thinks!