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