Returning Values in List Box based on ComboBox Value

I am currently working on a form called Manage Distribution Lists. What I
want to happen is when I select a list via a ComboBox called ListsCombo, the
Listbox (which right now is named List Box shows me all the users associated
with that list. I have the users stored in a table called
tbl_l_UserDistributionList. So I have a query on the list box where I can
pull back all users and that works fine. However when I try to set up
criteria it doesn't show up. I don't get any errors.
The bound column on the combobox is 1
My query is:

SELECT tbl_l_UserDistributionList.LastName
FROM tbl_l_UserDistributionList
WHERE (((tbl_l_UserDistributionList.LastName)=Eval("Form s![Manage
Distribution Lists]![ListsCombo].Column(1)")));

Suggestions?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200707/1

What is it you are trying to do?
What is it you expect from the Eval function?
--
Dave Hargis, Microsoft Access MVP


"wheels1284 via AccessMonster.com" wrote:

I am currently working on a form called Manage Distribution Lists. What I
want to happen is when I select a list via a ComboBox called ListsCombo, the
Listbox (which right now is named List Box shows me all the users associated
with that list. I have the users stored in a table called
tbl_l_UserDistributionList. So I have a query on the list box where I can
pull back all users and that works fine. However when I try to set up
criteria it doesn't show up. I don't get any errors.
The bound column on the combobox is 1
My query is:

SELECT tbl_l_UserDistributionList.LastName
FROM tbl_l_UserDistributionList
WHERE (((tbl_l_UserDistributionList.LastName)=Eval("Form s![Manage
Distribution Lists]![ListsCombo].Column(1)")));

Suggestions?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200707/1

Thanks for the response. Each of the distribution lists has users associated
with them. So what I am trying to do is to 1. select the list I need via the
combobox. 2. Have the list of associated users appear in a list box that I
have below it. I was using the eval function because before I put it in I was
getting 'undefined function errors'. Basically I want the query to look at
the combobox and return the users from that list. Hope this helps.

Klatuu wrote:
What is it you are trying to do?
What is it you expect from the Eval function?
I am currently working on a form called Manage Distribution Lists. What I
want to happen is when I select a list via a ComboBox called ListsCombo, the
[quoted text clipped - 12 lines]

Suggestions?

--
Message posted via http://www.accessmonster.com

Oh, I should have seen this sooner. The problem is, a query cannot
understand the column property of a combo box. In this case, it doesn't
matter because you are using the bound column. So all it really needs is:
WHERE (((tbl_l_UserDistributionList.LastName)=Forms![Manage
Distribution Lists]![ListsCombo])));
--
Dave Hargis, Microsoft Access MVP


"wheels1284 via AccessMonster.com" wrote:

I am currently working on a form called Manage Distribution Lists. What I
want to happen is when I select a list via a ComboBox called ListsCombo, the
Listbox (which right now is named List Box shows me all the users associated
with that list. I have the users stored in a table called
tbl_l_UserDistributionList. So I have a query on the list box where I can
pull back all users and that works fine. However when I try to set up
criteria it doesn't show up. I don't get any errors.
The bound column on the combobox is 1
My query is:

SELECT tbl_l_UserDistributionList.LastName
FROM tbl_l_UserDistributionList
WHERE (((tbl_l_UserDistributionList.LastName)=Eval("Form s![Manage
Distribution Lists]![ListsCombo].Column(1)")));

Suggestions?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200707/1

OK I put in your code. But its still not working. I get no current record
errors. And nothing shows up.I even tried ones that I knew had records

SELECT tbl_l_UserDistributionList.LastName
FROM tbl_l_UserDistributionList
WHERE (((tbl_l_UserDistributionList.LastName)=[Forms]![Manage Distribution
Lists]![ListsCombo]));

Any Ideas?



Klatuu wrote:
Oh, I should have seen this sooner. The problem is, a query cannot
understand the column property of a combo box. In this case, it doesn't
matter because you are using the bound column. So all it really needs is:
WHERE (((tbl_l_UserDistributionList.LastName)=Forms![Manage
Distribution Lists]![ListsCombo])));
I am currently working on a form called Manage Distribution Lists. What I
want to happen is when I select a list via a ComboBox called ListsCombo, the
[quoted text clipped - 12 lines]

Suggestions?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200707/1

Okay, so I went back and reread your original post. Let me see if I
understand.
You have a List Box that has a list of users. You have a combo box that
allows you to select a list of specific users that should be in the list box.
If nothing is selected in the combo, then all users are shown in the list box.
If you select a list from the combo, then only the users in that list are
shown in the list box.
If this is correct, then we need to do another thing. The row source query
for the list box needs to include a reference to the combo so it will know to
filter based on the combo. Then in the after update event of the combo box,
you need to requery the list box.

--
Dave Hargis, Microsoft Access MVP


"wheels1284 via AccessMonster.com" wrote:

OK I put in your code. But its still not working. I get no current record
errors. And nothing shows up.I even tried ones that I knew had records

SELECT tbl_l_UserDistributionList.LastName
FROM tbl_l_UserDistributionList
WHERE (((tbl_l_UserDistributionList.LastName)=[Forms]![Manage Distribution
Lists]![ListsCombo]));

Any Ideas?



Klatuu wrote:
Oh, I should have seen this sooner. The problem is, a query cannot
understand the column property of a combo box. In this case, it doesn't
matter because you are using the bound column. So all it really needs is:
WHERE (((tbl_l_UserDistributionList.LastName)=Forms![Manage
Distribution Lists]![ListsCombo])));
I am currently working on a form called Manage Distribution Lists. What I
want to happen is when I select a list via a ComboBox called ListsCombo, the
[quoted text clipped - 12 lines]

Suggestions?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200707/1

Yes except for one thing: I don't see anything in my listbox when I use
criteria. It doesn't matter if the combobox is selected or not.

Other than that I'm good. Let me know what you think.

Klatuu wrote:
Okay, so I went back and reread your original post. Let me see if I
understand.
You have a List Box that has a list of users. You have a combo box that
allows you to select a list of specific users that should be in the list box.
If nothing is selected in the combo, then all users are shown in the list box.
If you select a list from the combo, then only the users in that list are
shown in the list box.
If this is correct, then we need to do another thing. The row source query
for the list box needs to include a reference to the combo so it will know to
filter based on the combo. Then in the after update event of the combo box,
you need to requery the list box.

OK I put in your code. But its still not working. I get no current record
errors. And nothing shows up.I even tried ones that I knew had records
[quoted text clipped - 16 lines]

Suggestions?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200707/1

for clarification: when I say "i'm good" what i mean is I am ready to try
your solution
wheels1284 wrote:
Yes except for one thing: I don't see anything in my listbox when I use
criteria. It doesn't matter if the combobox is selected or not.

Other than that I'm good. Let me know what you think.

Okay, so I went back and reread your original post. Let me see if I
understand.
[quoted text clipped - 13 lines]

Suggestions?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200707/1

Okay. First I need to know how you establish the lists. Looking at the SQL
you posted for the combo, I don't know how you know which users are in which
list. If you can see that each user can be in only one list, then you need a
field in the user table to identify the list they are in. If a user can be
in more than one list, you need a table that joins the table of lists to the
table of users.

If you can help me understand your data structure, I can help with the rest.

--
Dave Hargis, Microsoft Access MVP


"wheels1284 via AccessMonster.com" wrote:

for clarification: when I say "i'm good" what i mean is I am ready to try
your solution
wheels1284 wrote:
Yes except for one thing: I don't see anything in my listbox when I use
criteria. It doesn't matter if the combobox is selected or not.

Other than that I'm good. Let me know what you think.

Okay, so I went back and reread your original post. Let me see if I
understand.
[quoted text clipped - 13 lines]

Suggestions?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200707/1

Ok I'll try my best and we'll work from there.

At the very base I have a Users Table - which I can populate via a Manage
User Form I created. Then I have this Manage Distribution List form. This
form first pulls the User Names from the User Table and Places it in a
listbox. Then I have a text box where I can add the name of the list. Finally
I have a button called add users which takes the users I selected and adds
them to the respective list by placing a record in a third table called
tbl_l_UserDistributionList. This table contains the distribution lists and
what users are assigned to them. So basically I want to show the users that
are assigned to each specific list based on the DistributionListName - which
is the selector in the combobox. Hope this helps. If you would like to chat
via aim I can do that as well.

Klatuu wrote:
Okay. First I need to know how you establish the lists. Looking at the SQL
you posted for the combo, I don't know how you know which users are in which
list. If you can see that each user can be in only one list, then you need a
field in the user table to identify the list they are in. If a user can be
in more than one list, you need a table that joins the table of lists to the
table of users.

If you can help me understand your data structure, I can help with the rest.

for clarification: when I say "i'm good" what i mean is I am ready to try
your solution
[quoted text clipped - 8 lines]

Suggestions?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200707/1