Look up combos in Query Criteria?

[Moose]

I am trying to build a database that narrows the record choices as different
fields are selected. I believe that this would be a query, but whenever I
build a query and select a field choice, the record in the main table changes.
For example:
Car Dealer enters car ID, make, model, and color into main database. I as a
consumer, select make model and color, and the query lists only records that
match. I would prefer a lookup box in the query, as we don’t want to rely on
stuff being named correctly, or maybe a burgundy from the list, would suffice
for a red preference. Keep in mind, changes made to the query should not
effect the main table.

 

[Douglas J. Steele]

You need to create a form with unbound combo boxes that contain the
appropriate values.

Have your query refer to the combo boxes on the form as criteria:

Forms![NameOfForm]![NameOfControl]

Note that the query will only work properly when the form is already opened.
Running the query will not cause the form to be opened.

 

[Ken Sheridan]

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

 

[padre DEVIS ROCCO dcs]

I am trying to build a database that narrows the record choices as
different
fields are selected. I believe that this would be a query, but whenever I
build a query and select a field choice, the record in the main table
changes.
For example:
Car Dealer enters car ID, make, model, and color into main database. I as
a
consumer, select make model and color, and the query lists only records
that
match. I would prefer a lookup box in the query, as we don’t want to rely
on
stuff being named correctly, or maybe a burgundy from the list, would
suffice
for a red preference. Keep in mind, changes made to the query should not
effect the main table.