ComboBox to Find a Record 2

[5tr0ud]

On a Main Form, which has several linked Subforms, I've created a ComboBox to search and display the selected record. I've been able to create this using the following code. PROBLEM: It will select the first record with the specific last name, but will not allow advancing to the next record with the same last name.

I'd like to create a 'normal' ComboBox, that will let you begin typing the last name (however, the list provides LastName, FirstName), and it will start to fill in the ComboBox selection for you. Then it will let you select anyone with the same LastName, not just the first person with that specific last name. I do not want to use a Filter. Can someone help? I'm new to this VBA coding, and I may have put the code in the wrong Event. Thank you so much for your suggestions.


Private Sub cboSearchByName_AfterUpdate()
' Find the record that matches the control.
Dim Rs As Object

Set Rs = Me.Recordset.Clone
Rs.FindFirst "[LastName] = '" & Me![cboSearchByName] & "'"
If Not Rs.EOF Then Me.Bookmark = Rs.Bookmark

End Sub

 

[PHV]

it will start to fill in the ComboBox selection for you
Take a look at the AutoExpand property of your ComboBox object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[5tr0ud]

Thanks for the advice. I checked and AutoExpand is 'yes'. The problem I'm having is that I can get the first person of the specific LastName. Example: If I want Robert Smith, I can start typing Smith, and the ComboBox will take me to the Smiths. I can then scroll down to Robert, and click on him. PROBLEM: The first Smith (Anna) appears. And I cannot access any other Smiths from the ComboBox. . . Can you help with further coding? Any other suggestions are welcome.

 

[Zion7]

5tr0ud,

it sounds like you have you have your bound column property, to LastName.
You must choose a more, or exclusively, distinct column, to
bind the combobox.
You can still reference the desired value of the combo, by using it's column property.

Set Rs = Me.Recordset.Clone
Rs.FindFirst "[LastName] = '" & Me![cboSearchByName.Column(1)] & "'"
If Not Rs.EOF Then Me.Bookmark = Rs.Bookmark

But, if I understand your objective correctly, I would do the search on the bound column regardless.
Keep the First & last name columns visible, for clarity, but for exclusivety, use (assuming you have one), primary key.

Excuse me, if this following explanation is elementary, but just in case...

From properties dialog box, of combobox,

RowSource = SELECT ID, LastName, FirstName FROM ....
BoundColumn = 1
Column Count = 3
ColumnWidths = 0";1in";1in'
LimitToList = Yes
AutoExpand = Yes

As for the code, either...

Rs.FindFirst "[LastName] = '" & Me![cboSearchByName.Column(1)] & "' AND [FirstName] = '" & Me![cboSearchByName.Column(2)] & "'"

or...

Rs.FindFirst "[ID] = " & Me![cboSearchByName]

since this is the bound column (default), no need to use column property, same as...
Rs.FindFirst "[ID] = " & Me![cboSearchByName.Column(0)

This is what I found to be the case, you must bind it, to a more distinct column.

Hope this helps, good luck!

 

[5tr0ud]

THANK YOU SO MUCH, Zion7, for this explicit explanation. I know just a little about VBA coding (and nothing at all about coding in a Module), so THERE IS NO EXPLANATION THAT IS ELEMENTARY TO ME.

Yes, there is a Primary Key but I did not want it to show. I added the ID to my Select statement, then I tried both your suggestions. The first, with the Column() gave me an error message that it could not find [cboSearchByName.Column(1)].

I then tried:
Rs.FindFirst "[ID] = " & Me![cboSearchByName] and IT WORKED!!!! I am so grateful! Can't wait to get to work on Monday and implement it. Thank you, Thank you!

 

[Zion7]

Great to hear 5tr0ud,
If I may, I'd like to correct my error, in case this is relevant to you in the future, or someone else.
I syntaxed the 1st example, incorrectly.

square brackets are wrong.

This...
Me![cboSearchByName.Column(1)] &....

should be...
Me![cboSearchByName].Column(1) &..

that's what happpens when you cut & paste, & try to modify after.LOL

Good luck Monday!