More on FAQ Combo Box Limit

[dchMPower]

Hello,

I have a question which expands on the FAQ posted below:

>How do I limit the contents of one combo box based on the >selection in another?
>faq702-681

I am using two combo boxes as search criteria for a query. The second Combo Box limits the list based on the value of the first combo box (Code from FAQ). However, I created my first Combo Box with an "All" option. When "All" is selcted in the first combo box, I get no values in the list for the second Combo. How can I modify the second combo box so that it lists all values when "All" is selected in the first combo box.

Thank for the help!

 

[Rick39]

In the AfterUpdate event of the first combobox, you might specify what the RowSource of the second will be based on the first choice.....something like:

If Me!combobox1 <> &quot;All&quot; Then

sql = &quot;SELECT YourTable.YourField FROM YourTable&quot;
sql = sql & &quot; WHERE YourTable.YourField ='&quot; & Me!combobox2 & &quot;';&quot;

Else
sql = &quot;SELECT YourTable.YourField FROM YourTable;&quot;

End if

Me!combobox2.SetFocus
Me!combobox2.RowSource = sql
Me!combobox2.Requery
Me!combobox2.Dropdown


HTH

 

[dchMPower]

Beautiful, thank you very much!

One more question if you don't mind. Here is the rowsource in ComboBox1:

SELECT MyTable.Field1, MyTable.Field2 FROM MyTable UNION SELECT &quot;*&quot; As Field1, &quot;*&quot; as Field2 FROM tDistAllocations ORDER BY Field2;

What can I add to this combo box so that it only show DISTINCT records for Field2?

Thanks again,
dchMPower

 

[Rick39]

SELECT MyTable.Field1, MyTable.Field2 FROM MyTable UNION SELECT &quot;*&quot; As Field1, &quot;*&quot; as Field2 FROM tDistAllocations Group By Field2
ORDER BY Field2;

 

[dchMPower]

Thanks again for your help! Your code is not working for an enhancement I want to make, the part that does not work is:

Else
sql = &quot;SELECT YourTable.YourField FROM YourTable;&quot;

I want my Combobox2 to have a &quot;*&quot; selection just like ComboBox1. So, I replaced the code above with:

Else
sql = &quot;SELECT YourTable.Field1, YourTable.Field2 FROM YourTable UNION SELECT &quot; * &quot; As Field1, &quot; * &quot; As Field2 FROM YourTable ODRER BY Field2;&quot;

Do you know why your above code doesn't work with this sql statement?

Thanks for any help you can provide,
dchMpower

 

[Rick39]

What is the UNION SELECT for?