Relationing tables in a form

[Sonia]

Hi,

I've the following tables created:



BRAND (PK.brand_id, brand_name)

MODEL (PK.model_id, model_name, brand_id)

SUBMODEL (PK.submodel_id, submodel_name, model_id)

VEHICLES (PK.vehicle_id, brand_id, model_id, submodel_id)



In a simple form, I've created fields to store the information in
tblVEHICLES and I use combo boxes to store the table id for the brand, model
and submodel. All works fine and data is stored in the table. However, once
selecting the brand, say Mazda, the model (and the submodel) combo box
displays all the brands models. It's not filtering the Mazda's only.



What am I doing wrong?

TIA

Sonia

 

[Sprinks]

Sonia,

Search the forum for "Cascading Combo Box". Basically you use the
AfterUpdate event of the first combo box to change the RowSource of the 2nd,
limiting it by the value chosen in the 1st, then requery.

Sprinks

 

[Tim Ferguson]

BRAND (PK.brand_id, brand_name)

MODEL (PK.model_id, model_name, brand_id)

SUBMODEL (PK.submodel_id, submodel_name, model_id)

VEHICLES (PK.vehicle_id, brand_id, model_id, submodel_id)

How do you control this, so that, for a given Vehicle, the
Vehicle.Brand_ID points to the same brand as the Vehicle.ModelID->
Model.Brand_D and the Vehicle.Submodel_ID -> Submodel.Model_ID ->
Model.Brand_ID?

If this thing is really meant to be as simple as it looks, surely you
should have just

Brands(*BrandID, FullName)
Models(*ModelID, FullName, BrandID+)
Submodels(*SubmodelID, FullName, ModelID+)
Vehicles(*VehicleID, SubmodelID+)

That way there is no danger of buying the world's first Ferrari Mondeo
(yecccch!)


All the best



Tim F

 

[Sprinks]

Sonia,

Tim is right; there is no reason to include the BrandID and ModelID in the
Vehicles table; they are fully defined through the other intermediate tables.
However, you probably need some other meaningful field to distinguish one
Vehicle from another, such as the VIN.

Sprinks