Combobox to lookup any part of field

[Danick]

I’m using a combobox to lookup contact names.
The rowsource is taken from a field in a contact table that contains the contact’s First name and then Last name.
You could use the dropdown to pick the name or type the First name into the combobox.
The problem is that the combobox matches only the beginning part of the field as you type. So if you start typing the person’s last name, the combobox would not display anything.

Is there an easy way to allow the combobox to lookup any part of the field that matches the text being typed?

Thanks

PS: I spent an hour looking for something similar. But the search found so many hits relating to combo or combobox that I just gave up. My apologies if this has been answered before.

 

[rzw0wr]

You are likely bound to the 1st column of the combo box.
I don't know of any easy way.
Not without VBA any way.

Dale

 

[billmeye]

ComboBoxes are not set up that way. If you wanted to be able to free type into a text box and have another filter that adjusts your combobox to limit the choices, that can be done by setting the criteria in your rowsource for the contact's name to:

Like "*" & [Forms]![yourForm]![TextBox1] & "*"

and then in the Textbox1 On KeyPress (if you want real time filtering) or After Update event set it to requery the combobox. Then when you go to your combobox it will have a restricted list based on whatever to typed into the textbox. This really works well with Listboxes as you can better see what's left after filtering.

 

[Danick]

ComboBoxes are not set up that way. If you wanted to be able to free type into a text box and have another filter that adjusts your combobox to limit the choices, that can be done by setting the criteria in your rowsource for the contact's name to:

Like "*" & [Forms]![yourForm]![TextBox1] & "*"

and then in the Textbox1 On KeyPress (if you want real time filtering) or After Update event set it to requery the combobox. Then when you go to your combobox it will have a restricted list based on whatever to typed into the textbox. This really works well with Listboxes as you can better see what's left after filtering.

Thanks, this actually works pretty well. I made a text box that is just a little bit smaller than the combobox and set it's background to transparent. So when you click on the combo, start typing and then the drop down limits just what you typed. Now I have either figure a way to allow a doubleclick event on the combo that is under the filtered text box, or just make some room on the form for this new filter box.

Thanks

 

[billmeye]

Your welcome. If you have enough room, I like putting the textbox above a listbox, it looks good.

 

[Danick]

Your welcome. If you have enough room, I like putting the textbox above a listbox, it looks good.

I may do just that if this doesn't work out. Right now, the text box is invisible and on top of the combo. So everything will work as before just like there was no text box.

Then I added a small filter button that will make the text box visible and get focus so you can start typing. Then after update, I focus on the combo and activate the drop down to show only the filtered list. This also makes the text box invisible and nulls out what was in the text box.

I'm testing it right now and it seems to be working. Just got to find out if there are any bugs with this if the user decides to escape somewhere along the way. But so far so good...

 

[billmeye]

That's great. Good luck.

 

[Danick]

Well I can't seem to get all the bugs out of trying to do it this way. Problems with lost focus and pressing the ESC key in the middle of entering data, etc..

Do you have an example of how you use a hidden list box that becomes visible when typing into a text box (prefer a combo) that then goes hidden after the user makes the selection. The selection has to act like a combo where the record is bound to the first column.

 

[billmeye]

Maybe your trying to hard. Have you rearranged the order of your columns so the text portion is the first column and what you want the field bound to is the second column. Set Bound Column to 2 and now you can take advantage of AllowAutoCorrect feature which will move you along the list as you type. Also, in the Got Focus event for the combobox add Me.Combobox.DropDown, it makes it easy to see the list.

If you are still eager to use the text filter, have you considered just placing the filter box right next to the combobox? I've attached an example showing both.

 

[Danick]

Maybe your trying to hard. Have you rearranged the order of your columns so the text portion is the first column and what you want the field bound to is the second column. Set Bound Column to 2 and now you can take advantage of AllowAutoCorrect feature which will move you along the list as you type. Also, in the Got Focus event for the combobox add Me.Combobox.DropDown, it makes it easy to see the list.

If you are still eager to use the text filter, have you considered just placing the filter box right next to the combobox? I've attached an example showing both.

I guess I forgot to let you know that I was working with Access 2003. And I have the bound column to 1 but format to 0, so you don't see it in the dropdown.

The after update of the normally hidden text box does this,
Me.Combo31.SetFocus
Me.Combo31.Dropdown
I use the label as a button to make it visible and give it focus

Then when the combo gets focus, I do this,
DoCmd.Requery "Combo31"
Me.txtContactFilter.Visible = False

The problem is, if the user typed something that is not found, the combobox shows up empty and then they're stuck.

Another problem, if the user decides to use the combo without the text filter, but enters a name that is not on the list, they get a msgbox that tells them to pick a name on the list or doubleclick to create a new entry - that part works. But if they decide to click the text filter before they hit enter, then the get the msgbox and then run time error 2110 (Access can't move focus to the txtfilter)

But you maybe right and am trying too hard and should just go back to the simple combobox that worked. Still I like the textbox idea since I can have the criteria showing not just the name, but the company, title, etc... It makes it much more functional. (Except for the error messages that is).

 

[Danick]

The problem is, if the user typed something that is not found, the combobox shows up empty and then they're stuck.

Another problem, if the user decides to use the combo without the text filter, but enters a name that is not on the list, they get a msgbox that tells them to pick a name on the list or doubleclick to create a new entry - that part works. But if they decide to click the text filter before they hit enter, then the get the msgbox and then run time error 2110 (Access can't move focus to the txtfilter)

So I got rid of the second problem with the runtime error by simply placing the text box right over both the combobox as well as the control button. This way they can't hit the button in the middle of typing into the text box. Might not be pretty, but it works.

Now the other problem could probably be fixed by somehow resetting the box to show all contacts again. Either through code with a message stating "No records found" or by simply using the ESC key.

But I can't figure out how to do either yet. :banghead:

 

[jdraw]

Can you trap the 2110 error? In your error handler, check for Err 2110 and give a message (just to see if it's "trappable") if so, then can work out what should happen in that condition/circumstance.

To reset the combo to all contacts, I think you reset the Row Source and requery.

Combo.Rowsource = "Select distinct ContactId, ContactFName, ContactLname from Contacts".... sort of thing.

 

[Danick]

To reset the combo to all contacts, I think you reset the Row Source and requery.

But which event will do this? And how do I get it to do it with the click of the ESC key? I've tried just about every combination on the text box and nothing will make it reset. Except hitting enter. But that just brings up the combo with nothing in it.

 

[Danick]

So I may have figured out the last problem with the ESC button.
I changed the form's Key Preview to YES. And then added code to the On Key Press that does this

Private Sub Form_KeyPress(KeyAscii As Integer)
If KeyAscii = 27 Then
Me.Combo31.SetFocus
Requery
Me.txtContactFilter = Null
Me.txtContactFilter.Visible = False
End If
End Sub

I'm testing it now and seems to be working so far. Anyone have any comments about doing it this way?

Thanks