Searching in comboboxes: functionality and workarounds

[Lhoj]

This thread could easily go on the forms section but since depending on the answer it would imply code, I'll open it here.

I know that when you allow typing on a combobox, access looks for any record on it matching the typed text, but it does so only on the first visible column (field) of the combobox. Is it possible for access to perform the search on all visible columns of the combobox?

And assuming the answer to the previous question is no, is it too inefficient to fire up a query when text changes in the combobox to filter it with the entered string? That would be an easy workaround but I feel that executing a query every time a character is entered or deleted is a bad idea.

Thank you all!

[ranman256]

No. Combo Items needed should only be in 1 column.

[CJ_London]

you can use VBA code to modify the rowsource to apply criteria.

Plenty of more complete examples about but in principle for a combo called say cboCustomers with a rowsource of say:

SELECT ID, custName, custAddress FROM tblCustomers ORDER BY custName

in the combo enter event (required to 'reset' the list)

me.cboCustomers.rowsource="SELECT ID, custName, custAddress FROM tblCustomers ORDER BY custName"

in the change event this air code. You may need additional code for error checking, returning the cursor to the correct position etc

if cboCustomers.Text<>"" Then
     me.cboCustomers.rowsource="SELECT ID, custName, custAddress FROM tblCustomers WHERE custName Like '*" & cboCustomers.Text & "*' OR custAddress Like '*" & cboCustomers.Text & "*' ORDER BY custName"
else
   cboCustomers_enter()
end if

but I feel that executing a query every time a character is entered or deleted is a bad idea.

That is the only way to do it simply. You would need to try it and see whether it has a significant effect on performance. Note that using the initial * prevents the use of indexing so do not use unless you have to

Other things to do is to not run the code until at least say 3 characters have been entered, or use the form timer event to execute the code say 1/2 second after the use stops typing

[Lhoj]

No. Combo Items needed should only be in 1 column.

I have a combobox to add users to a workstation, and I want the DB operator to search for the user by the username or by the person's name (which are completely different) thus the multiple columns. Ideally, typing in the combobox would search/filter by both fields.

you can use VBA code to modify the rowsource to apply criteria.

That was my plan as a workaround but I fear it'll hurt the performance. I'll try it as soon as I have a moment to implement and test it. Thanks, Ajax.

[sxschech]

You might also look into FAYT find as you type. I use this and it allows to search anywhere within the cols of a combobox. Once you start typing letters, it narrows down the list.

Post #7 has the download that I use. (there is another version in Post #17) There are additional discussions and attachments, but if your situation is straightforward, then post #7 attachment should provide what you need.

https://www.access-programmers.co.uk...-typed.315813/