Coding 2nd Combo Box Based On Entry In First

[Guest]

I'd like to have the users make two entries into seperate combo boxes with
the entry in the second box filtered by the entry in the first.

Below is a simplified table (tblProcesses) representing the data, with two
fields, Stage and Process:

Stage: Process:
Primary Raw Pumping
Primary Grit Removal
Primary Sludge Pumping
Secondary Aeration
Secondary Clarification
Secondary Chemical Addition

What I have here is a sort of tree like menu. The size of the actual tree
would be too large to list this way for a single combo box to be practical.

I'm new to Access, but someone in the "New User" group suggested I post here.

What I'd like to have happen is for the available entries in the second
combo box to be filtered by the selection in the first box. So if an
operator selects "Primary" he would see only Raw Pumping, Grit Removal, and
Sludge Pumping in the list - as opposed to the entire list.

Actually, while I'm at it: I populate the first combo box by reference to a
one-field table (tblStages) that simply list all stages (Primary, Secondary,
Tertiary, Solids Handling, Admin, etc). Every stage occurs in tblProcesses;
is there a way to generate a list of unique values to populate a combo box
based on the entries in the "Stages" field of tblProcesses? That would save a
table and in my mind make things a bit neater.

Finally, I'm guessing this type of thing occurs often in database
configuration; is there a standard way to deal with it?

Thanks,
Sludge

 

[Michel Walsh]

Hi,


Have the second combo box row source property set to something like:


SELECT DISTINCT process FROM table1 WHERE
stage=FORMS!FormNamehere!FirstComboBoxNameHere OR
FORMS!FormNamehere!FirstComboBoxNameHere IS NULL


where all cap words are keyword to type as they are, and other names to be
replaced by those of your particular design.


The query pumps the processes that have the stage equal to the one mentioned
in FORMS!FormNamehere!FirstComboBoxNameHere , but, if that control has
nothing in it, all distinct processes are pumped back.



Hoping it may help,
Vanderghast, Access MVP

 

[Ron2005]

As to your second question.

Change the row source to be

SELECT DISTINCT stage FROM table1

Also on the query given by Michel, the DISTINCT is only needed if it is
possible to have the process to appear more than once for any one
stage.

for example if the following can occur then you need the DISTINCT (this
may be true if there is perhaps a date involved so the
Stage: Process:
Primary Raw Pumping
Primary Grit Removal
Primary Sludge Pumping
Primary Raw Pumping

 

[Guest]

Thanks to both of you for your responses. I had no trouble with the SQL for
the first combo box as Ron wrote but I'm afraid I managed to mistype the code
for the second box. Once I looked a bit more closely and typed the SQL
exactly as Michel suggested it also worked perfectly.

Regards!
Sludge