Using Combo Box to control Query (Access 2000 SR-1)

Topic

New Reply

I followed advice from this site to create an Unbound form with 4 combo boxes and a control button that opnes up a form based on the combo selections. All went fine except:

1. I can select from 4 combo boxes and that feeds a query linked to a form
STATE=California
CITY=Berkeley
BUILDING=home
RENT=N

But what if I just want to pick the first CombObox and then open the form. Let’s say I just want California, then Go! or California,Berkeley then go!

How do I set up my query to stop looking for Combo box 3 and 4 if I just want to select 1 and 2 and then open.

I have a query that has: [Forms]![MasterMenu]![CatCombo01], …CatCombo02, …CatCombo03,…CatCombo04 in the criteria fields.

Thanks

Paul

Reply | Quote

Replies

Paul,

Why not try using the * wildcard character?

Maybe something like: IIf(IsNull([Forms]![MasterMenu]![CatCombo01]), Like “*”, [Forms]![MasterMenu]![CatCombo01])

Just a thought… Let me know if it works…

Reply | Quote

When doing this in the past, I have set the default values of each combobox to *. Then have all your queries set us as Like(ComboboxName&’*’). Thus, if you choose California in the first combobox, the remainder of the queries will select all of the remaining data.

HTH

Reply | Quote

Thanks for clarifying that Gary! I knew it would be simple…

Reply | Quote

Glad I could help. Hey, where is Hans and Charlotte. I much prefer reading there responses.

Reply | Quote

…Hopefully taking a WELL EARNED break!!

I know what you mean though… They’re great… I read these posts day and night now…
I think I’m hooked!!!!

Have a great evening!

Reply | Quote

[indent]

---

I read these posts day and night now… I think I’m hooked!!!!

---

[/indent] Hehehehe. Then the plan is working!

Reply | Quote

I think I am missing somethiong here. It did not work. Are you saying the end result for the criteria will be *.* That did not work.

In combo1 I selected ‘California’
In the rest, I placed the default to be *
Is the query supposed to be:
Combo1=’California’
Combo2=*.*
Combo3=*.*
Combo4=*.*

Reply | Quote

Try it like this: remove the existing criteria.

In the first empty column of the query grid, enter [Forms]![MasterMenu]![CatCombo01] in the field row, and [State] Or Is Null in the criteria row.

In the next empty column, enter [Forms]![MasterMenu]![CatCombo02] in the field row, and [City] Or Is Null in the criteria row.

In the next empty column, enter [Forms]![MasterMenu]![CatCombo03] in the field row, and [Building] Or Is Null in the criteria row.

In the next empty column, enter [Forms]![MasterMenu]![CatCombo04] in the field row, and [Rent] Or Is Null in the criteria row.

Clear the check boxes to display these “fields”.

Reply | Quote

-First, go to the form where you are making selections and go to design mode. Set the default to each combobox to “*”. When you open the form, each combobox will show an “*” as the selection.

– Now go to the query driving the second form. In the criteria that links to the data selected on the first form, change each from [Forms]![MasterMenu]![CatCombo01] to
Like ([Forms]![MasterMenu]![CatCombo01]&’*’)

Save the form and query.

When you select California and then launch the second form, the query will say, get all records where Combo1 like California* and all other records for Combo 2, 3, and 4.

HTH.

Or, try Hans idea.

Reply | Quote