Conditional Combo Box

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

--
Build a little, test a little.


"Thick Mike" wrote:

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

On Wed, 20 Jan 2010 07:34:01 -0800, Thick Mike
wrote:

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?
--

John W. Vinson [MVP]

Brilliant!
Thanks Karl

"KARL DEWEY" wrote:

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" wrote:

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