Filtering combo boxes based on a selection in another combo box

[Stephen @ ZennHAUS]

Hi Guys and Gals

I have a subform which is designed to look like a datasheet because I need
to filter a combo box called Materials based on another combo box earlier in
the selection process called Region.

To explain the data, Regions are like a location on a site. Materials are
exactly what they sound like. Each Region contains a different combination
of Materials. Obviously, there is a table called tblRegions that contains
the record describing each region and a table called tblMaterials the does
the same for each material.

I am using the fields RID and MID (the primary keys in these tables) in a
table called tblLoads to identify how many loads of which material come from
which region.

So, the form. RID and MID are combo boxes that display the Region name and
Material name. When the user choose which region they are getting their
loads from, I want the list of materials in the Materials combo box to
automatically filter based on the materials available in the selected
region.

I hope all this makes sense.

Thanks in advance everyone.

Stephen @ ZennHAUS

 

[Marshall Barton]

I have a subform which is designed to look like a datasheet because I need
to filter a combo box called Materials based on another combo box earlier in
the selection process called Region.

To explain the data, Regions are like a location on a site. Materials are
exactly what they sound like. Each Region contains a different combination
of Materials. Obviously, there is a table called tblRegions that contains
the record describing each region and a table called tblMaterials the does
the same for each material.

I am using the fields RID and MID (the primary keys in these tables) in a
table called tblLoads to identify how many loads of which material come from
which region.

So, the form. RID and MID are combo boxes that display the Region name and
Material name. When the user choose which region they are getting their
loads from, I want the list of materials in the Materials combo box to
automatically filter based on the materials available in the selected
region.

Common situation.

First, make sure you are using a continuous subform, not a
datasheet subform.

Set the materials combo box's RowSource query to something
like:

SELECT L.RID, L.MID, M.[Material name]
FROM tblLoads As L INNER JOIN tblMaterials As M
ON L.MID = M.MID
WHERE L.RID = Forms!mainform.subformcontrol.Form.RID

Then add a line of code to the subform's Current event to
keep the two combo boxes synchronized:
Me.MID.Requery

Note that all rows in the subform will only display the
correct material for records with the same region as the
current record. The other rows will display with a blank
material.

The blank display issue can be masked by placing another
combo box exactly on top of MID. this "extra" combo box
would use the same RowSource query without the WHERE clause.
To be able to select a material, add a line of code to the
new combo box's GotFocus event:
Me.MID.SetFocus