Cascading Combos using different tables

Linking Combo Boxes - Help

Hi all,

Ive attached a test db to this post so u can all see wot im trying to do here. Ive basiclly got 3 tables with fields:

tblCar: Make, Model, Mileage
tblMake: MakeID, Make
tblModel: MakeID, Model

If u look at the relationships ull see how ive set them up. This should work.
On the form frmCar ive got 2 combo boxes and a textbox. This form adds a record to tblCar. The first combo lets you select a car make. The second combo lets you select a car model and the textbox is so you can enter the car milage. This is just an example database remember just focusing on what im trying to do. I want it so when you select a car make from the first combo this filters whats in the 2nd combo, so u can only select a specific model of car for the make selected in the first combo. This is not like the other cascading combo situations ive seen, because ive structered the tables especially for this. If u look at the table relationships and structure you'll see each make has a unique ID which is linked to the model table. And in the model table a model that is specific to a make in the make table has the specific MakeID next to it. For example Ford has a MakeID of 1 in the make table. So in the model table all models that are made by ford have a 1 in next to them in the MakeID table, which is linked to the MakeID in the make table. So the MakeID is related to the Model. U will see what i mean when you look at the structures and relationships of the tables. I know what ive done will work, but i dont know howto get the combos to cascade to this so i can select a make and then a specific model for that make. Is there a few queries i need to use or something else? Theres got to be a query that can be put together in the after event or row source of the combos hasnt there?

Im really stuck with this 1 guys
Any help would be greatly appriciated, in pointing me in the right direction.

Thanks
Jim

Please Please guys i really need help with this one, its so important to the development of my system.

Thanks guys

There is nothing different about this cascading combo than any other. I made some changes to your table structure that might clarify things for you.

tblCar:
CarID (autonumber pk)
MakeID (foreign key to tblModel, not tblMake)
ModelID (foreign key to tblModel)
Mileage

tblMake:
MakeID (autonumber pk)
MakeName

tblModel:
ModelID (autonumber pk)
MakeID (foreign key to tblMake)
ModelName

Car is linked to model which is linked to make. The MakeID is only in the car table for convenience when building forms. It is not necessary.

The first combo's rowSource should be a query that selects the MakeID and MakeName and orders by MakeName. The second combo's rowsource should be a query that selects ModelID and ModelName from the Model table and contains selection criteria that restricts the rows selected to those where MakeID = Forms!YourForm!cboMake. Order this query by ModelName.

thanks for the help m8,

however it will not let me specify 2 relationships between tblCar and tblModel. I set up the table structures like you said:

tblCar:
CarID (PK - AutoNumber)
MakeID (Number)
ModelID (Number)
Mileage (Text)

tblMake:
MakeID (PK - AutoNumber)
MakeName (Text)

tblModel:
ModelID (PK - AutoNumber)
MakeID (Number)
ModelName (Text)

I linked MakeID from tblMake to tblModel MakeID and ModelID from tblModel to tblCar ModelID. I then tried to link MakeID from tblModel to tblCar MakeID, but it said there was a relationship already specified. Ive tried adding MakeID from tblModel first then ModelID from tblModel but it says the same. I need both make and model fields in tblCar, like you say to make it easier when building forms. Plus ive designed my system this way. Would my first way not work in the attached db to the first post? with the use of a query?

If not what would i need to do to get your way working because it wont let me specify another relationship. If you could upload your changed as an attachment it would be easier. However if you cant, please tell me where im going wrong.

Thanks alot
Jim

Now then Jim, i was not sure if the other reply had sorted you will have a look now.

Thanks alot m8

If i could use my original table structure it would be great, with the use of a query or 2 lol. But if i HAVE to change the table structure in order for it to work then i suppose ill have to. Its just i was told i could do it with my original table structure. See what you can do m8.

Thanks again
Jim

Looking as we speak see what i can do and then send it back, is it ok to send it back to you as an attachment i could send via e-mail if you send it to me via E-mail on this system.

if you tell me what you want the database to do i will write it for you, if you want or at lest a demo for you.

Alastair

pmed you m8 with details.

Thanks again
Jim

I then tried to link MakeID from tblModel to tblCar MakeID, but it said there was a relationship already specified.

Click to expand...

- There are not two separate relationships. There is one relationship which is made on two fields. Go back to the first relationship and modify it to add the second field.

Its just i was told i could do it with my original table structure.

Click to expand...

- Your original structure is incomplete.

Pat can you post the 2nd combos query SQL syntax?

Select ....
From ....
Where MakeID = Forms!YourForm!cboMakeID;

I forgot to remind you that you need to requery the SECOND combo in the AfterUpdate event of the FIRST.

Me.cboMakeID.Requery