Combo Boxes on Form

[Guest]

Hi -

I have 4 unbound combo boxes on a form. Two have the Row Source Type as
Table/Query. Two use a value list. If the user leaves all fields blank, I
would like to retrieve all the records in the table.

Right now, there is an underlying query to this form which accepts the
entries of the combo boxes.

How can I do this?

Also, once the records are retrieved, I need to open an additional form to
display the records. There is a button on the form which runs the query.
Would I code it to open the form first and have the form use the query as the
record source?

Thanks for your help.

 

[Dorothy]

Hi -

I have 4 unbound combo boxes on a form. Two have the Row Source Type as
Table/Query. Two use a value list. If the user leaves all fields blank, I
would like to retrieve all the records in the table.

Right now, there is an underlying query to this form which accepts the
entries of the combo boxes.

How can I do this?

Also, once the records are retrieved, I need to open an additional form to
display the records. There is a button on the form which runs the query.
Would I code it to open the form first and have the form use the query as the
record source?

Thanks for your help.

Hello Danu.

The first thing you want to do is create a button that opens the form
that displays all your records. In the VBA code for this button, you
would have to have nested If/Then statements built into the code that
say something like:

If isnull(me.combobox1.value) then
dump out all the results.
else
if isnull(me.combobox2.value) then
dump out the results with criteria from combobox1
else
if isnull(me.combobox3.value) then
dump out the results with criteria from combobox1 and 2
else
if isnull(me.combobox4.value) then
dump out the results from combobox1, 2 and 3
else
dump out the results from combobox1, 2, 3 and 4

Let me know if this works for you.

Dorothy
(e-mail address removed)

 

[Guest]

Thanks, Dorothy.

I have changed the code behind the button which ran the query to open a
different form which displays all the records.

I am very new to VBA so don't know how to write the code which will give me
the results I need which is why I was clumsily using a query. If all four
combo boxes are null, then all records should be displayed. However, the user
can choose to pick from the drop down list for any of the combo boxes so many
combinations can be generated. The user can also leave up to 3 of the
dropdowns empty. Can you give me a starting point to write the code?

Thank you.