Filter a listbox based on the value of a combobox

[NKK]

I have a form with a combobox (combo1) and a listbox (list1); the controls
are unbound and the row sources are two different tables with a common field
(system_id). I would like to use the value chosen in combo1 in a subquery to
make up the "where" clause of list1, but I am not sure quite how to
accomplish this or which event to use. Ideally, I want the row source of
list1 to be:
"select list_table.field1, list_table.field 2 from list_table where
list_table.system_id=(select combo_table.system_id from combo_table where
combo_table.field2=combo1).
I started out with a sub-form with a parameterized query and when I manually
entered a value, it worked correctly, however I could not figure out how to
pass the value from the combo as a parameter. Now I have both the controls
on the same form, hoping I could filter it somehow, but that does not seem to
be feasible. A point in the right direction would be greatly appreciated!

 

[Al Campagna]

NKK,
Using an example of cboState and lstCity, where only cities in the State
you selected appear in your listbox.
On the AfterUpdate event of cboState, Requery lstCity.
The query behind lstCity should use the value of cboState to filter it's
results.
lstCity fields...
City State
= me.cboState
On my website (below), I have a 97 and 2003 sample file called Synched
Combo boxes that demonstrates the above.
While it's a combo to combo, there will be no difference using combo to
list.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[NKK]

Thanks Al, that gets me one step closer, however I do not use the value of
the combobox directly in the list box. Here are the two tables that are the
row sources of the two controls

Combobox control (cbolusubmitters), table name = lu_submitters
submitter_id
system_id

Listbox control (lboluaccountrole), table name = lu_accountroles
account_role_id
account_role_desc
system_id

The submitter_id is the value that is "stored" in the combobox for "future
use"...I want to use that value get the system_id and filter the roles shown
in the list box. I know how to do it in sql, I just can't get the syntax
quite right for vba. Here's the sql I would use

select lu_accountroles.account_role_id, lu_accountroles.account_role_desc
from lu_accountroles where system_id=(select lu_submitters.system_id from
lu_submitters where submitter_id=???(not sure about the syntax here))

I just can not figure out how to get that sql statement to work in the
rowsource property of the listbox...

 

[Al Campagna]

NKK,
It appears that the only relationship is between lu_submitters systemID
and lu_accountroles systemID.
So I assume that if your list was filtered by the combo's systemid value,
that's would be what you want.
Set up your cbolusubmitters Combobox for 2 columns...
SubmitterID SystemID

Use this query criteria in your lboluaccountrole Listbox...
SystemID = cbolusubmitters.Col(1)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[NKK]

Thanks for replying, however I took another approach...I added the requery of
the listbox to the on change of the combo...and then set up the row source
query of the listbox to accept the parameter from the combobox...worked like
a charm. Thanks for the insights!

 

[Al Campagna]

NKK,
Be aware that the OnChange fires for every/each character you enter in
the combo box, which is a bit of overkill.
I would still suggest using the AfterUpdate event.
If your code works using OnChange, it should also work using the
AfterUpdate.
Your call...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."