Cascading Comboboxes

[spikebeal]

don't know the name for it

i am doing my course work for sixth from computing atm and would like a bit of help. the course work involves creating an access database linked with VB. The problem is i have to create forms for car details, customer details etc. i have started creating the car details form and have used the looup wizard to list all car makes, i have used another form to place all the car makes into. now i would like the user to select a car make, then the next cell would have each car model fro that make of car. How do i do this? thanks in advance
Gary.

 

[pbaldy]

The name you're looking for is cascading combo boxes. Search on that and you should find plenty of help. Basically you have the second combo refer to the first in its criteria, and requery it on update of the first.

 

[Lister]

Check this Link out. It should be some help.

 

[spikebeal]

ok, will check it out, thanks guys

 

[spikebeal]

Still having problems with the cascading boses, maybe i will figure it out better if it is shown to me, My main table car details has the (cbo stands for combo right?) cbocarmake and the dependent cbocarmodel. car model needs to rely on car make for answers in its box. i'll attach the file here and see if any of you guys can check it out for me.
tia

 

[Mile-O]

Example on this thread

Simple (I think) instructions on this

 

[spikebeal]

Whats all that after update stuff about? i can't find where to enter the code that was in that section. also click the arrow in the combo box it asks for which one car make i want, i want it to get it from the previous combo box, not have to type it in.

 

[Mile-O]

Whats all that after update stuff about? i can't find where to enter the code that was in that section.

The first/top combobox properties has a tab for Events. The After Update event is here. Find the correct line and click the button with three dots at the far right of this line. Select Code Builder from the popup box. You can now enter the line of code.

also click the arrow in the combo box it asks for which one car make i want, i want it to get it from the previous combo box, not have to type it in.

What's the SQL of your two combo's RowSource properties?

 

[spikebeal]

i have not got any forms btw, so all this is being done in tables , here is what it says atm...SELECT carmodel, carmodel FROM cars WHERE carmake=Forms!frmExample!cbocarmake ORDER BY Country;
i cannot find the events tab in the place u said it was. does this all mean to take the combo boxes out of the tbale, create a form from the tables and then add the combo boxes?

 

[Mile-O]

If you don't have any forms then that's that whole concept buggered!

Why don't you have any forms? Yes, to your question about creating a form.

You should never have to enter data directly into a table - forms are the best way.

 

[spikebeal]

Ok, i think i have it figured now my bad, thats why i could'nt find the after update bit etc... now one last problem, it still asks for the name of the make of car, it also does not update, does the "Me.cboCountry.Requery" need to be based on the first combo box or the dependent one?
Heres a bigger elplanation of the tables layouts,
main table called car details
customerID
CarRegistration
CarMake
CarModel
TyreMake
Then there is a form containing all the car makes called carmakes
it has a field called
carmake
Next is a table holding the car modles, for this it had to have 2 fields, one for showing the car make and one for showing the car model called:
Carmake
Carmodel
it has something like 850+ entries so is why i didn't make a seperate table for each car make because there would then be 98+ tables to handle.
With all this data maybe you could figure out the right layout for the row source "SELECT carmodel, carmodel FROM cars WHERE carmake=Forms!frmExample!cbocarmake ORDER BY Country; " and the after update part.
Thanks alot

 

[Mile-O]

The problem wasn't helped by your structure - I've fixed this in this example database.

You don't need to have the Make of car in the Car's information table, only the model as the query proves.

I forgot to add the table for Tyres back in but that can be a little exercise for you.

 

[spikebeal]

http://www.fontstuff.com/access/acctut10pfv.htm
Fixed my problem, found the answer here.

 

[Mile-O]

http://www.fontstuff.com/access/acctut10pfv.htmFixed my problem, found the answer here.

To be fair, I already fixed it for you.

As for that link I'm not convinced by its methods. I just can't envision the tables that it builds the combos off of not having an Autonumber primary key.

The examples are laborious (some involve hard coding) which means that when you add something you'll have to remodify some code.

 

[Rich]

http://www.fontstuff.com/access/acctut10pfv.htm
Fixed my problem, found the answer here.

Who wrote that rubbish

 

[Mile-O]

Who wrote that rubbish

This Guy!

 

[Mile-O]

Actually, it's quite worrying that anyone may be using this guy's tips - check out the one for calculating a person's exact age: click here

 

[wizcow]

Mile-o-phile & Rich

You and so many others have been such a great help to me and other new Access users, on this bulletin board. Your expertise has been greatly appreciated and the fact that it is given free, is a credit to all.

Having said that I would like to defend Martin Green just a bit.

I found that his SQL examples to very easy to understand for us beginners.

I do agree that some of the projects are less than perfect, but he is trying to get a point across in his tutorials and I expect that he may be trying to keep them simple. And he too makes them available for free.

Just my opinion.
And again I would thank all those having helped me in the past.
Respectfully

Tom