Combobox baed on another combobox 2

[mudstuffin]

I know this one has been asked here and there, but I jst cant figure it out, so perhaps somebody can answer it tailored to my situation.

I have a combobox where I want to choose a caetgory, and then have a second combobox update with only the items under the chosen category.

For example:-

Table1 has the following data in the fields below


Type Manufacturer

Car Ford
Car Vauxhall
Car Fiat
Bike Yanaha
Bike Suzuki
Bike Cagiva

etc etc, and so what I want to do is when Car is selected from Combo1, Combo2 is updated to only offer Ford, Vauxhall and Fiat. Sounds simple....

Can somebody give me some examples using Table1 as my table name, Combo1 & Combo2 as the comboboxes, and Car and Manufacturer as the field names.

Its been buggin me all day....!

Thanks in advance.



mudstuffin

 

[scriverb]

Use this SQL as the RowSource for Combo1:
Select A.Type
FROM Table1 as A
Group by A.Type
Order by A.Type;

Use this SQL as the RowSource for Combo2:
SELECT A.Manufacturer
FROM Table1 AS A
WHERE (((A.type)=[Forms]![frmYourFormName]![Combo1]))
ORDER BY A.Manufacturer;

Use this VBA code in the AfterUpdate of Combo1:
Me![Combo2].Setfocus
Me![Combo2].DropDown

This should do it for you. Just update the red code to the name of your form.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[mudstuffin]

Scriverb.

Thanks for that. It works, and it has also helped me to understand how it works. Have a star for that....

n.b. It just needed to have:-

Me![Combo2].Requery

added to the afterupdate event to get it to update the second combobox.

Regards,



mudstuffin.

 

[scriverb]

Yes, you have to perform a .requery on the combo box everytime the first one is picked. Glad that this worked for you application.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[zishan619]

Awesome Have a star
Thanks