Using Listbox selection to limit other Listbox Options

[Brian Carlson]

I have a form that I have two list boxes on. One of these is Employee and the
other is Activity. I would like my selection in the employee list to
filter/limit the list in the Activity box. For example, I may have 200
activities, but there are only 4 that Ben does, and thereby if I choose Ben I
would like this Activity list to be filtered by this. I have a table where
each person is partnered with the activity they do, but do not understand how
I could do this in a form (NOTE: a person can be assigned many activities,
but no activity is assigned to more than one person) It seems that I would
build a parameter query and have this as the source for the Activity list,
but I do not know how to cause the Employee list to set the parameter. Am I
on the write path? Does anyone know where I can find information on how to
do this? Thank you in advance.

 

[KARL DEWEY]

Search on 'Cascading Combos' for the answer.

 

[Piet Linden]

I have a form that I have two list boxes on. One of these is Employee andthe
other is Activity.  I would like my selection in the employee list to
filter/limit the list in the Activity box.  For example, I may have 200
activities, but there are only 4 that Ben does, and thereby if I choose Ben I
would like this Activity list to be filtered by this.  I have a table where
each person is partnered with the activity they do, but do not understandhow
I could do this in a form (NOTE: a person can be assigned many activities,
but no activity is assigned to more than one person)  It seems that I would
build a parameter query and have this as the source for the Activity list,
but I do not know how to cause the Employee list to set the parameter.  Am I
on the write path?  Does anyone know where I can find information on how to
do this?  Thank you in advance.

read this article...
http://www.mvps.org/access/forms/frm0028.htm

if it doesn't solve your problem, search for "listbox" and you'll find
the solution.

 

[Arvin Meyer [MVP]]

There is a sample of list boxes doing exactly that:

http://www.accessmvp.com/Arvin/Combo.zip

 

[Brian Carlson]

Thank you all for your responses.

There is a sample of list boxes doing exactly that:

http://www.accessmvp.com/Arvin/Combo.zip
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Rosa]

Dear Arvin,

I am having a similar problem. However, I have two combo boxes and my form
has to do with choosing a company name and the second boxes is limited to the
employees names for that company. I also have other fields from other tables
in that same form. I got the combo boxes working to list correctly, however,
once I close and go back into the form the second combo box is missing the
employees name. I have to click the company name again (requery) to show the
employees name that I original picked. How do I get the second combo boxes'
employee's name to keep in the form? (Do I still use the AfterUpDate macro
on Company (Requery employee)?). I use the form to input records and see
what I have already inputted. See below for the purpose of my database.

(My form is based similar to a tracking database of employees from different
companies who have to submit certain documents. So I track the employee,
company, date document set, date document received, the document sent, etc.
all in one form.)

Rosa

 

[Arvin Meyer [MVP]]

Data is not kept in forms, it is kept in the tables which ultimately
underlie those forms, so if you wanted to store the EmployeeID for the name
you had picked, assign it to another textbox, either visible or not, on that
form. Use the AfterUpdate event on the Employee list or combo box.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com