Listbox Search by Multiple Letters

[Jenaccess]

Hi,

I'm using Access 2010, though am also familiar with Access 2007. I have a listbox populated with a query (concatenated last and first names with a bound ID field that's invisible). One of my users asked if it would be possible to search the listbox by more than the first letter of the name. As it stands now, if you type Smith, the focus would go to the first name starting with the letter "H", rather than Smith.

I've spent a lot of time researching this and come to the conclusion that this functionality does not exist within a listbox. However, I wanted to see if anyone knew of a workaround. From what I've discovered, a popular suggestion is to change the listbox to a combobox, which has this functionality. Since my listbox is multiselect, that would not work for me. Does anyone know of any other way this could be accomplished? Any help would be appreciated. Thank you!

 

[Isskint]

Here is oOne way.

Put the SQL for the query as the listboxes rowsource. Then put a textbox on your form for the user to enter the search criteria in. In the textboxes ONChange event write code to change the rowsource SQL to include a WHERE statement including the seach criteria.

 

[Jenaccess]

Hi,

Thank you so much for answering. Would this solution change the way the listbox appears to the user, i.e. they wouldn't see all the rows in the beginning, just what was typed?

 

[Isskint]

It would initially display ALL data, not to mention be pretty slow for the first 2 or 3 characters typed.

You could get around this by including a LEN() function check in the textboxes OnChange event, so you only start updating the listbox rowsource after say 3 characters have been typed in.

 

[Jenaccess]

That's a really good idea. Thanks so much for your help!