hie
kinda confused about how to go about doing this. but anyway, i have a many-to-many relationship for the tables tblRadio and tblBlock, so they are connected by a junction table called tblMapRadioBlock.
now, i have one listbox, it's populated by all blocks in the block table. i want to be able to dynamically populate a second listbox with all the radios that are associated with the selected block (every time i click on a different block, the second listbox will be requeried)
how do i do this? i would think.. i'd need some kinda join on all three tables?? then i select where tblRadio.RadioID = tblMapRadioBlock.RadioID and the same for BlockID... but somehow... this doesn't seem to work... my 2nd listbox remains empty.
here's the full SQL string:
SELECT tblRadio.RadioID, tblRadio.CategoryID, tblRadio.BandID, tblRadio.DisplayID, tblRadio.PowerID
FROM tblRadio LEFT JOIN (tblBlock RIGHT JOIN tblMapRadioBlock ON tblBlock.BlockID=tblMapRadioBlock.BlockID) ON tblRadio.RadioID=tblMapRadioBlock.RadioID
WHERE tblMapRadioBlock.BlockID = Me.lstBlocks.ItemData(varItem);"
also.. the 1st listbox is multi select, so if the user selects more than one block, the 2nd listbox should display only radios associated with all selections, (an AND not an OR). but that's a headache for another day... any thoughts anyone? thanks..
kinda confused about how to go about doing this. but anyway, i have a many-to-many relationship for the tables tblRadio and tblBlock, so they are connected by a junction table called tblMapRadioBlock.
now, i have one listbox, it's populated by all blocks in the block table. i want to be able to dynamically populate a second listbox with all the radios that are associated with the selected block (every time i click on a different block, the second listbox will be requeried)
how do i do this? i would think.. i'd need some kinda join on all three tables?? then i select where tblRadio.RadioID = tblMapRadioBlock.RadioID and the same for BlockID... but somehow... this doesn't seem to work... my 2nd listbox remains empty.
here's the full SQL string:
SELECT tblRadio.RadioID, tblRadio.CategoryID, tblRadio.BandID, tblRadio.DisplayID, tblRadio.PowerID
FROM tblRadio LEFT JOIN (tblBlock RIGHT JOIN tblMapRadioBlock ON tblBlock.BlockID=tblMapRadioBlock.BlockID) ON tblRadio.RadioID=tblMapRadioBlock.RadioID
WHERE tblMapRadioBlock.BlockID = Me.lstBlocks.ItemData(varItem);"
also.. the 1st listbox is multi select, so if the user selects more than one block, the 2nd listbox should display only radios associated with all selections, (an AND not an OR). but that's a headache for another day... any thoughts anyone? thanks..
