We help IT Professionals succeed at work.

Is there a simple way to change the list on a combo box  in Access as you type?

Robert Hester
on
I will be gratefull if I could receive any simple sample that I can understand and apply to my application .The question says it all...

Better if this sample could use  as the row source a table with 2 fields: ID and DescriptionText where the user would type strings to be located in the DescriptionText but the ID field would be saved.

Thanks in advance.

Robert
Comment
Watch Question

Top Expert 2016

Commented:
use a  query  as  rowsource of your combo

select id, descriptiontext from tableName

now set the following combo property

column count 2
bound column 1
column width  0; 2

Author

Commented:
Thank you...but what I need is more complex. For instance:

Description text is a description of componentes for dishes at a restaurant:

Rice, beans, potatoes and letuce
Baked beans with sausages
Fruit salad with ice cream
Yorkshire pudim
Beans brazilian style
Chinese beef with pasta

If user types BE, then the row source would narrow down to:

Rice, beans, potatoes and letuce
Baked beans with sausages
Beans brazilian style
Chinese beef with pasta

IF then types A, then row source would narrow down even more to:
Rice, beans, potatoes and letuce
Baked beans with sausages
Beans brazilian style


Robert
Jim Dettman (EE MVE)President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:
Ray was heading you in the right direction; if the combo's rowsource is based on a query, then that query can use criteria that the user types, and refresh the combo presenting a shorter list.

Two things:

1. You need to use the Controls OnChange event to be notified as each character is typed.
2. You need to use the .Text property to retrieve the value that user is typing.  

  What I'd do is use a hidden control to hold the value and point the query to it for it's criteria.  In the onChange event, populate that control with the current value and requery the combo.

 I think however you'll find performance to be dismal.  

 Because of that, an alternate technique is to use a text box and subform to emulate a combo.  In doing so, you can apply a filter pretty darn fast (because your not re-fetching the records).

 I did this exact thing for a zip code look-up years ago.

However if your rowsource is small (hundred or so entries at most), then you might get away with a combo.   There's also the technique of using an in-memory array and a call back function to fill it, which is slightly faster in some cases.

Jim.
Jim Dettman (EE MVE)President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:
and BTW, don't forget about Auto Increment!

Jim.

Author

Commented:
Thank you Jim...I got the idea...would you have a sample code?

Thanks,
Robert

Author

Commented:
Does anyone have code to help on this?

Thanks in advance,
Robert
President / Owner
Fellow 2019
Most Valuable Expert 2017
Commented:
Sorry I set this aside to come back to because I needed to dig out some code, but looking back, I'm not sure what route you want to go exactly.   There are a half a dozen different ways to do this and it would depend on the size of your list.

If your going to stick with the combo, then you would need to:

1. Create a hidden control

2. In the combo's OnChange event, take what's been typed in the combo so far and save it in the hidden control:

  Me![txtHiddenControl] = Me![cboMyCombo].Text
  Me![cboMyCombo].Requery

3. Have cboMyCombo's rowsource based on a query, which uses the txtHiddenControl to restrict the results.

Jim.