One way of doing this would be to have an unbound main form with a continuous
form view subform bound to a query listing all available cars. The main form
would have unbound combo boxes cbMake, cboModel and cboColor say. The
subform's query would reference these controls as parameters like so:
SELECT *
FROM Cars
WHERE NOT Ordered
AND (Make = Forms!YourMainForm!cboMake
OR Forms!YourMainForm!cboMake IS NULL)
AND (Model = Forms!YourMainForm!cboModel
OR Forms!YourMainForm!cboModel IS NULL)
AND (Color = Forms!YourMainForm!cboColor
OR Forms!YourMainForm!cboColor IS NULL);
I've assumed for this example that the Cars table contains an Ordered column
of Boolean (Yes/No) data type, so 'NOT Ordered' in the query restricts it to
cars not yet ordered by a customer. By testing for NULL a selection from each
combo box in effect becomes optional, so you can narrow down the search,
starting with Make.
In the AfterUpdate event procedure of each of the three unbound combo boxes
requery the subform control with:
Me.YourSubformControl.Requery
Note that YourSubformControl is the name of the control in the main form
housing the subform, not the name of its underlying form object.
As a user selects a make the subform will update to show cars of just that
make; when they select a model it will update to show just those of that make
and model; and when they select a colour it will update to show only those of
that make, model and colour.
To make this work properly you'll really need to correlate the combo boxes
so that when a make is selected the second combo box shows only models of
that make, and similarly only the colours available for the selected model.
To do this make the RowSource of the cboModel combo box on a query which
references the cboMake control, e.g.
SELECT DISTINCT Model
FROM Cars
WHERE Make = Forms!YourMainForm!cboMake
ORDER BY Model;
and for the cboColors combo box's RowSource:
SELECT DISTINCT Color
FROM Cars
WHERE Model = Forms!YourMainForm!cboModel
ORDER BY Color;
In the AfterUpdate event procedure of cboMake, as well as requerying the
subform control, you'll need to set to Null and requery the cboModel and
cboColor controls:
Me.cboModel = Null
Me.cboColor = Null
Me.cboModel.Requery
Me.cboColor.Requery
Me.YourSubformControl.Requery
And similarly in cboModel's AfterUpdate event procedure
Me.cboColor = Null
Me.cboColor.Requery
Me.YourSubformControl.Requery
In the subform, to prevent any changes being made to the data set the Locked
property of each bound control to True and its Enabled property to False.
Ken Sheridan
Stafford, England