Stephen [ at ] ZennHAUS wrote:
[Quoted Text] >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
--
Marsh
MVP [MS Access]
|