Find the Matching Recordset

[cdodson]

Here's one that is probably easy for you but difficult for me. I want to be able to select a record from my Combo Box:

Sub Combo33_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[LastName] = '" & Me![Combo33] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

The problem with this is if there are several "Smiths" in the list it will only select the first one listed. What code should I add to get it to find the correct record (say matching FirstName and CompanyName also)?


TIA,
Carey Dodson
mailto:cdodson@cmte.org

 

[timfox]

A combo box should be returning the nominated value for the selected record no matter how many similar records are in the list. If the combo box is not returning the record reference that you thought it should, then I would suggest revealing more columns in the combo box to enable a more accurate selection, or (if you want to enable multiple selections) use a list box.

2nd thought - You haven't said whether you are using a primary key for the record set that you are searching on. When using a combo box for finding a specific record amongst a form's dataset, the combo box return value (bound colum) should be the primary key of the recordset that you are searching on.

- hope it helps
Tim
(If I'm way off -please provide more info on the nature of the problem...)

 

[cdodson]

Thank you. With your input I was able to go back into the Combo wizard. It solved the problem with this code:

Private Sub Combo43_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Me![Combo43])
Me.Bookmark = rs.Bookmark
End Sub