cascading combo box and list selection 2

[Guest]

fixed my previous problem. Now, I have three Combo Boxes which refresh the
list of variables each time something is selected. However, I would ideally
like any selection in any combo box to limit the selections in the other
combo boxes, according to whether any thing exists.

To be specific, i have a theme selector combo box, a industry selector combo
box, and a region selector combo box...all of which feed a indicator list.
But, I would prefer it if a selection in one of either the theme, industry,
region combo boxes would limit the choices in the other two combo boxes
according to the data i have stored. Furthermore, I would like the ability
for the listbox to list all available indicators, regardless of other two
combo box criteria (assuming they are left empty).

To me, this seems like endless circling, and i do not have a strong grasp of
SQL and VBA code, which obviously limits my Access skill. Any help would be
appreciated, but I understand if it requires too much of your time!

Thanks

 

[Wayne Morgan]

You need to put criteria in the SQL for the Row Source of each combo box
that refers to the selection in each of the other two combo boxes. In the
AfterUpdate event of each combo box, you need to issue a Requery to each of
the other two combo boxes. The criteria should accept the fact that either
or both of the other two combo boxes may be Null, in which case it should
not filter the entries for that field.

Example criteria:

=Forms!frmMyForm!cboCombo2 Or IsNull(Forms!frmMyForm!cboCombo2)

The reason this works is because if you make a selection, the right side of
the Or will be False, but the left side will then limit the results by what
was entered in Combo2 (only one side of an Or statement needs to return True
for the statement to be True). The left side of the statement will be True
for all results that match the selection. If Combo2 is Null, then the right
side of the Or is True and will always be True, regardless of the value in
the field, so all values are returned.