Conditional Combo Box

  • Thread starter Thread starter Thick Mike
  • Start date Start date
T

Thick Mike

Hi
Is it possible to have a combo box list data from varying tables depending
on the value in another combo box?
ComboA has Vehicle, Product, Country and Employee values.
I want ComboB to list values from the Vehicles, Products, Countries and
Employees tables depending on the entry in ComboA.
The values in ComboB would also have criteria - eg. only current employees,
only products over a certain value, etc.
Thanks
 
One way is to have comboB source use a union query with criteria from comboA.
Just remember the parts of the union must have same number of fields and
same datatype.
Example for comboB --
SELECT tblA.field1, tblA.field2, tblA.field3
FROM tblA
WHERE [Forms]![YourForm]![ComboA] = "A"
UNION ALL SELECT tblB.field1, tblB.field2, tblB.field3
FROM tblB
WHERE [Forms]![YourForm]![ComboA] = "B"
UNION ALL SELECT tblC.field1, tblC.field2, tblC.field3
FROM tblC
WHERE [Forms]![YourForm]![ComboA] = "C";
 
Hi
Is it possible to have a combo box list data from varying tables depending
on the value in another combo box?
ComboA has Vehicle, Product, Country and Employee values.
I want ComboB to list values from the Vehicles, Products, Countries and
Employees tables depending on the entry in ComboA.
The values in ComboB would also have criteria - eg. only current employees,
only products over a certain value, etc.
Thanks
Click to expand...

I would suggest that your best bet would be to change the RowSource query for
ComboB in the afterupdate event of ComboA.

Better yet, since there is very little in common between vehicles, countries,
products and employees, maybe you should have four combo boxes...!? What will
ComboB be used for?
 
Brilliant!
Thanks Karl

KARL DEWEY said:
One way is to have comboB source use a union query with criteria from comboA.
Just remember the parts of the union must have same number of fields and
same datatype.
Example for comboB --
SELECT tblA.field1, tblA.field2, tblA.field3
FROM tblA
WHERE [Forms]![YourForm]![ComboA] = "A"
UNION ALL SELECT tblB.field1, tblB.field2, tblB.field3
FROM tblB
WHERE [Forms]![YourForm]![ComboA] = "B"
UNION ALL SELECT tblC.field1, tblC.field2, tblC.field3
FROM tblC
WHERE [Forms]![YourForm]![ComboA] = "C";

--
Build a little, test a little.


Thick Mike said:
Hi
Is it possible to have a combo box list data from varying tables depending
on the value in another combo box?
ComboA has Vehicle, Product, Country and Employee values.
I want ComboB to list values from the Vehicles, Products, Countries and
Employees tables depending on the entry in ComboA.
The values in ComboB would also have criteria - eg. only current employees,
only products over a certain value, etc.
Thanks
Click to expand...
Click to expand...
 
Back
Top