limit the choice of a combo box based on the choice of another

[steve goodrich]

I know this question has been asked before, but I just can't grasp how to do
it

I'm trying to limit the choice of a combo box based on the choice of another

I've got two combo boxes for the make and model of cars, When I enter a
particular make in the make box, I would only like to see models which match
that make. E.g. if I enter Ford as the make then only Ford models would be
shown in the model box.



The way I have it setup at the moment (maybe this is not right) is as
follows

I have a Table with all my fields including the make and model fields

I have a Table with all my Makes

I have a Table with all my Models



In the main Table design view I used the lookup wizard in the data type
fields of my make and model to populate the drop down boxes on the form, so
when I select any one of these drop down boxes I see all the makes and all
the models.



Would someone be kind enough to walk me through this procedure step by step



Regards



Steve Goodrich

 

[Wayne-I-M]

Check this link

http://support.microsoft.com/kb/97624/en-us

 

[John Vinson]

I know this question has been asked before, but I just can't grasp how to do
it

I'm trying to limit the choice of a combo box based on the choice of another

I've got two combo boxes for the make and model of cars, When I enter a
particular make in the make box, I would only like to see models which match
that make. E.g. if I enter Ford as the make then only Ford models would be
shown in the model box.



The way I have it setup at the moment (maybe this is not right) is as
follows

I have a Table with all my fields including the make and model fields

That's fine...

I have a Table with all my Makes

that's ok, but might not be necessary...

I have a Table with all my Models

and that you don't need at all; it's redundant, with your makes and
models table (and in fact since it doesn't contain the Make it's
pretty much useless).

In the main Table design view I used the lookup wizard in the data type
fields of my make and model to populate the drop down boxes on the form, so
when I select any one of these drop down boxes I see all the makes and all
the models.

The lookup wizard is VERY limited. In particular, I don't believe it
can be persuaded to do what you want. If you're trying to work in
table datasheets - *don't*. They're not designed for it!

Create a Form, I'll call it frmMyForm, instead.

Put a Combo Box on it, named cboMake; base it on a Query on the makes
table, sorting alphabetically. If you wish, you could do away with the
Makes table and base cboMake on a query

SELECT DISTINCT Make FROM MakesAndModels ORDER BY Make;

This will select only one instance of each make.

Base a second combo box, cboModel, on a Query

SELECT Model FROM MakesAndModels
WHERE Make = Forms!frmMyForm!cboMake
ORDER BY Model;

This is just a Query selecting those values of Model corresponding to
the selected Make on the form.

You then need *one* line of VBA code. View the properties of cboMake;
select the Events tab, and click the ... icon by the "After Update"
event. Select "Code Builder" from the options. You'll be given the Sub
and End Sub lines; add just the line in between -

Private Sub cboMake_AfterUpdate()
Me!cboModel.Requery
End Sub


John W. Vinson[MVP]

 

[Ken Sheridan]

Steve:

Tables model entity types. Model is an entity type and Make is an entity
type, so you are correct in having Makes and Models tables. The latter
should also have a foreign key column Make (or MakeID if you are using
numeric keys) referencing the primary key of Makes. Referential integrity
should be enforced in the relationship between the two tables. Your main
table should only have a Model (or ModelID) foreign key column, not a Make
(or MakeID) column, however, as the model implies the make, so a Make column
is redundant (its what's known as a transitive functional dependency in the
jargon). Redundancy leaves the door open to update anomalies as its makes it
possible to have a row where the model is from a different manufacturer to
the maker recorded in the same row.

For entering data there are a variety of methods using correlated combo
boxes. These work fine in single form view, but not in a continuous form.
For the latter you can use a 'hybrid' control made up of a text box
superimposed on a combo box, but its not usually an ideal solution. Better
is a single combo box with a multi-column list (bound to the Model column in
your case) and a text box which references the second column of the combo
box's list to show the make.

I've posted a demo of the various ways of handling this sort of situation at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


This uses geographical data with three levels of the hierarchy rather than
two, but the principle is exactly the same in your case.

Ken Sheridan
Stafford, England