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

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
Robert HesterAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)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
Robert HesterAuthor 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 (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
and BTW, don't forget about Auto Increment!

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

Thanks,
Robert
Robert HesterAuthor Commented:
Does anyone have code to help on this?

Thanks in advance,
Robert
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.