I have used a similar system, on my forms as there are too many 'selection' options to make a static query viable.
I construct the SQL in VB and then assign it as the recordsource
For the command button
Code:
dim rsql as string
if commandbutton.caption="Caption for default" then
rsql="SELECT * from tbl_example where Primary_ID is not null ORDER BY Primary_ID ASC"
me.recordsource=rsql
commandbutton.caption="Caption for alternative"
elseif commandbutton.caption="Caption for alternative" then
rsql="SELECT * from tbl_example2 where Secondary_ID is not null ORDER BY Secondary_ID ASC"
me.recordsource=rsql
commandbutton.caption="Caption for default"
endif
In your case you may wish to switch the if criteria from a commandbutton caption to a list or combobox valuee, but hopefully it should point you in the right direction.