Data Entry forms: use 1st combo box to filter next combo box?

[WildGourd]

Hi there-
I'm new to he Access world -- self-trained through books and slogging
through trial & error. But, I've hit a wall:

Question (brief version): In a form created for data entry, can you use a
combo box in one control to determine the list of values in a following combo
box via a filter or query?

Details of relevant tables and form (challenge below):
The purpose of the data entry form ("frmFleet") is to enter the number of
vehicles in each company's "fleet" of vehicles. Each vehicle is identified
by its main ModelID and then its SubID. (for example: for a Honda Civic, the
Model = Civic, but then there are many versions of the Civic, which I call
SubModel).
The main form shows the CompanyName. The subform connects to the table
tblFleet, in order to enter data into tblFleet.
In this subform the database user will need to select a Model and then a
Submodel from a combo box before filling in quantity.

I do have a table "tblModelsubModel" with two columns (Model and SubModel)
which lists all potential submodels of main Models. I also have a table
"tblModel" that lists the main models as unique values.

Challenge:
I would like the data enterer to be able to select the Model from a
combobox. I want the SubModel combobox to ONLY list those submodels that go
with the main Model. (in other words, when Civic is selected in the Model
box, I only want the Civic-appropriate subModel values to be present in the
the SubModel combobox ). Can I do this??? It seems a pretty logical
feature, but I've failed to find any help content that tells me how.

Would love anyone's input.
Best,
W.G.

 

[Klatuu]

That is called Cascading Combos
For the SubModel combo you need a rowsource that is filtered based on the
valule of the Model combo.
In your case, it would be something like
"SELECT SubModel FROM tblModelsubModel WHERE Mode = '" & Me.ModelCombo;"
Then in the after update event of the model combo, requery the submodel combo

Me.cboSubModel.Requery

As an aside, you probably don't really need the tblModel table. You can
filter it down to the unique model values with a row source for your model
combo with something like

"SELECT DISTINCT Model FROM tblModel;"