> 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" wrote:
>
>> 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" <nicole.krzywonski[ at ]dshs.state.tx.us> wrote in message
>> news:E25187D5-5BC0-4745-ADF6-4E8F95F4BD86[ at ]microsoft.com...
>> > 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" wrote:
>> >
>> >> 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" <nicole.krzywonski[ at ]dshs.state.tx.us> wrote in message
>> >> news:FBD3CC98-8FE9-49A6-83B5-D84DD3A406B1[ at ]microsoft.com...
>> >> >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!
>> >>
>> >>
>> >>
>>
>>
>>