Synchronize multiple comboboxes

[phyllo]

This is a simplified version of a database I'm working on. There's a form (frmSelection) with two unbound comboboxes and one command button.


tblSpecies
tblSpecies.River
tblSpecies.Species1
tblSpecies.Study
tblSpecies.Details
frmSelection
cboSpecies
cboRiver
cmdShowSelection

At this point if one selects a River from the River combobox, the Species combobox list will then limit itself to only species that exist within that River. But I need to be able to do the reverse as well and if someone selects a species first from the Species combobox, the River list should then be limited to only those rivers in which that species is found.

Row Source for cboRiver
SELECT DISTINCT tbl.River


FROM tblSpecies
ORDER BY tblSpecies.River;

Row Source for cboSpecies
SELECT DISTINCT tbl.Species1
FROM tblSpecies
WHERE (((tblSpecies.River) Like [Forms]![frmSelection]![cboRiver]))
ORDER BY tblSpecies.Species1;

cmdShowSelection is then used to show all the records that match that selection on another form.


I use a
Me.cboSpecies.Requery
to repopulate the cboSpecies list on the AfterUpdate Event for cboRiver.

Ultimately I'll need more than two selection boxes as the underlying table is more complex than I've put in here.

I'm not sure how to approach this. Any thoughts?

phyllo

[jzwp11]

Welcome to the forum!

I would probably force the user to tell you what kind of search they want to do before I present them any combo boxes. For example, in the header of the form, you might say how do you want to search? You can give them an initial combo box with choices or an option group with check boxes. In the detail section of the form, you would have generic combo boxes i.e. cbo1, cbo2 etc. Based on what they select in the form header, you would then set the row source of the cbo1 in the detail section to the appropriate query and likewise set the cbo2 based on how you want to do your cascading.