Returning Values in List Box based on ComboBox Value

[wheels1284 via AccessMonster.com]

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("Forms![Manage
Distribution Lists]![ListsCombo].Column(1)")));

Suggestions?

 

[Guest]

What is it you are trying to do?
What is it you expect from the Eval function?

 

[wheels1284 via AccessMonster.com]

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.

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?

 

[Guest]

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])));

 

[wheels1284 via AccessMonster.com]

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?

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?

 

[Guest]

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

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?

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?

 

[wheels1284 via AccessMonster.com]

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.
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]

 

[wheels1284 via AccessMonster.com]

for clarification: when I say "i'm good" what i mean is I am ready to try
your solution

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]

 

[Guest]

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

for clarification: when I say "i'm good" what i mean is I am ready to try
your solution

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?

 

[wheels1284 via AccessMonster.com]

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.

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]

 

[Guest]

Don't have aim, but your explanation is good. It seems you have the data
down correctly.
The query you originally posted, however, doesn't seem to follow your
description.

The combo box needs to provide a list of distribution lists, so its row
source should be based on the table that has the list of lists.
SELECT [List Name] From TableOfLists;

Then the List box's row source should filter that using the distribution
table:

SELECT tbl_l_UserDistributionList.LastName
FROM tbl_l_UserDistributionList
WHERE tbl_l_UserDistributionList.ListName = Me![ListsCombo].Column(1);

You need to requery the list box in the After Update event of the combo box.

So, the basic idea is that the combo needs to be a list of the lists, not
the users' names. The List box then should list all the users in the table
where the user in in the selected list.

--
Dave Hargis, Microsoft Access MVP

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.

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?

 

[wheels1284 via AccessMonster.com]

Ok I used your code and it makes sense.

My combo box is set up as follows:

SELECT tbl_DistributionList.DistributionListID, tbl_DistributionList.
DistributionName
FROM tbl_DistributionList
ORDER BY tbl_DistributionList.DistributionName;

My query reads as follows:

SELECT tbl_l_UserDistributionList.DistributionName
FROM tbl_l_UserDistributionList
WHERE tbl_l_UserDistributionList.DistributionName=Me!ListsCombo.Column(1);

this is what i get:
Undefined expression 'Me![ListsCombo].Column' in expression.

Suggestions?

Don't have aim, but your explanation is good. It seems you have the data
down correctly.
The query you originally posted, however, doesn't seem to follow your
description.

The combo box needs to provide a list of distribution lists, so its row
source should be based on the table that has the list of lists.
SELECT [List Name] From TableOfLists;

Then the List box's row source should filter that using the distribution
table:

SELECT tbl_l_UserDistributionList.LastName
FROM tbl_l_UserDistributionList
WHERE tbl_l_UserDistributionList.ListName = Me![ListsCombo].Column(1);

You need to requery the list box in the After Update event of the combo box.

So, the basic idea is that the combo needs to be a list of the lists, not
the users' names. The List box then should list all the users in the table
where the user in in the selected list.

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

[quoted text clipped - 24 lines]

 

[Guest]

Queries cannot understand column designations in combo or list boxes. They
can only understand direct references to the control. So, if you need to use
any column other than the bound column for a combo or list box, you have to
put it somewhere the query can understand it.
One way is to create a text box with the Visible property set to No
Make it's Control Source reference the combo column:
= ListsCombo.Column(1)

Then change the query to filter on the text box.
--
Dave Hargis, Microsoft Access MVP

Ok I used your code and it makes sense.

My combo box is set up as follows:

SELECT tbl_DistributionList.DistributionListID, tbl_DistributionList.
DistributionName
FROM tbl_DistributionList
ORDER BY tbl_DistributionList.DistributionName;

My query reads as follows:

SELECT tbl_l_UserDistributionList.DistributionName
FROM tbl_l_UserDistributionList
WHERE tbl_l_UserDistributionList.DistributionName=Me!ListsCombo.Column(1);

this is what i get:
Undefined expression 'Me![ListsCombo].Column' in expression.

Suggestions?

Don't have aim, but your explanation is good. It seems you have the data
down correctly.
The query you originally posted, however, doesn't seem to follow your
description.

The combo box needs to provide a list of distribution lists, so its row
source should be based on the table that has the list of lists.
SELECT [List Name] From TableOfLists;

Then the List box's row source should filter that using the distribution
table:

SELECT tbl_l_UserDistributionList.LastName
FROM tbl_l_UserDistributionList
WHERE tbl_l_UserDistributionList.ListName = Me![ListsCombo].Column(1);

You need to requery the list box in the After Update event of the combo box.

So, the basic idea is that the combo needs to be a list of the lists, not
the users' names. The List box then should list all the users in the table
where the user in in the selected list.

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

[quoted text clipped - 24 lines]

Suggestions?

 

[wheels1284 via AccessMonster.com]

Sorry to keep bothering you. How do I set up the filter. I got the text box
working but I can't seem to get it to work right.

SELECT tbl_l_UserDistributionList.LastName
FROM tbl_l_UserDistributionList
WHERE ((tbl_l_UserDistributionList.DistributionName) Like "*" & Forms![Manage
Distribution Lists]!text1 & "*");

Suggestions?

Queries cannot understand column designations in combo or list boxes. They
can only understand direct references to the control. So, if you need to use
any column other than the bound column for a combo or list box, you have to
put it somewhere the query can understand it.
One way is to create a text box with the Visible property set to No
Make it's Control Source reference the combo column:
= ListsCombo.Column(1)

Then change the query to filter on the text box.

Ok I used your code and it makes sense.

[quoted text clipped - 43 lines]

 

[Guest]

No bother. I want to see this work.
What do you mean it is not working right?
The SQL you posted appears to be for the list box. Where is the SQL for the
combo?

Can you describe what is happening?
--
Dave Hargis, Microsoft Access MVP

Sorry to keep bothering you. How do I set up the filter. I got the text box
working but I can't seem to get it to work right.

SELECT tbl_l_UserDistributionList.LastName
FROM tbl_l_UserDistributionList
WHERE ((tbl_l_UserDistributionList.DistributionName) Like "*" & Forms![Manage
Distribution Lists]!text1 & "*");

Suggestions?

Queries cannot understand column designations in combo or list boxes. They
can only understand direct references to the control. So, if you need to use
any column other than the bound column for a combo or list box, you have to
put it somewhere the query can understand it.
One way is to create a text box with the Visible property set to No
Make it's Control Source reference the combo column:
= ListsCombo.Column(1)

Then change the query to filter on the text box.

Ok I used your code and it makes sense.

[quoted text clipped - 43 lines]

Suggestions?

 

[wheels1284 via AccessMonster.com]

hi,

SQL for combo box:
SELECT tbl_DistributionList.DistributionListID, tbl_DistributionList.
DistributionName
FROM tbl_DistributionList
ORDER BY tbl_DistributionList.DistributionName;

Then in After Update I have an event procedure which reads as follows:
Private Sub ListsCombo_AfterUpdate()
Me.List26.Requery
End Sub


SQL for listbox:
SELECT tbl_l_UserDistributionList.LastName
FROM tbl_l_UserDistributionList
WHERE ((tbl_l_UserDistributionList.DistributionName) Like "*" & Forms![Manage
Distribution Lists]!text1 & "*");


What is happening is when I first open the form and nothing is selected in
the combo box it returns all users. However as soon as I click the combo box
and select an option, all the choices disappear.

No bother. I want to see this work.
What do you mean it is not working right?
The SQL you posted appears to be for the list box. Where is the SQL for the
combo?

Can you describe what is happening?

Sorry to keep bothering you. How do I set up the filter. I got the text box
working but I can't seem to get it to work right.

[quoted text clipped - 20 lines]