Conditional Combo Box

[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

 

[KARL DEWEY]

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";

 

[John W. Vinson]

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

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?

 

[Thick Mike]

Brilliant!
Thanks Karl

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.

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