The way I have done this is with two combo boxes, you would need to set up a
table to hold the value of Category and one for Issue with a 1 to many link
between the two tables. In other words Category "HR" will have many Issues.
set up a query where the value for both tables resides (Select both tables
when setting up the query and providing you have the link, this should work)
Table Category should show a link to Table Issues with 1 to many (Lets Call
this QryLink) Opening the query, you should see lots of Categories of the
same name with a few issues against each category if you have done it right.
HR Issue1
HR issue1a
HR Issue2
etc with the Category changing to show another many selection of issues
1st combo (Combo 1)
when you set up a combo box by dragging a combo box to your form based on
the main table by using the tools menu, you get a wizard, choose option 1
find a value in a table or query (Or something like that). The wizard takes
you through the table or query to choose and where to save the value once it
has been selected in the combo box. Normally this would be the field where
you want to store the value selected. Base the Combo box source on your query
(ie from "QryLink" Select "Category" and store this value in the field of the
form where you want the CategoryValue stored)
This will give a selection of the Categories and will save it in the form
where you tell it to.
Combo 2
Exactly the same as combo 1 exept From "QryLink" Select "Issues" and store
that value in the forms Main Table field where you want the value IssueValue
to be stored.
Now you have two Combo Boxes which don't relate to each other, you will see
al the Categories in Combo 1 and all the issues in Combo 2, I know this is
not what you want but!
With your form open in design view
In the properties of Combo 2, you will see that Access has set a Select
Query in the Row Source, Click in the Row Source and click on the far right
box which pops up. (marked "...") This will take you direct to the select
query in design view.
As you have based the Combo box on the query QryLink, you will see both
Categories and issues inthe query table, right click in the crietria row of
"Categories" and select build. You with see the expression builder pop up. In
the left hand column, select forms and navigate to you main form through all
open forms. Select from your main form Combo1 and in the right box, select
value. this should give you something like this
[Forms]![Frm_MainFormName]![Combo1]
This means that the result of the query is based on the value selected in
the first Combo1 control on your form. I use this typically to select
Different Contacts from the same company.
So, what have we? if you select "HR" from Combo1, you should only see issues
which relate to "HR" Issue 1, Issue 2, Issue 3 etc in Combo2, you should not
see anything relating to any other Category.
I'm sorry if this is a long drawn out method, there are easier ways to do
this but as yet I don't know them.
Perhaps someone else can assist but I hope this helps
Best Wishes, Mike
Angela said:
You were so helpful with this issue, I wonder if you could help me with
another one in the same database. I have 2 fields that I need to nest
together. The first field is "Category" which has a drop-down list of
categories. The 2nd field "Issue" and needs to be dependent on the Category
field.
Ex: If the category is "HR Issues", then the drop-down in the issue field
would contain a list of issues to choose from. Each category would have
differerent issues that would relate.
Is it possible to do something like this? Do I need to create additional
tables?
Any help you can offer on this would be great!!
Thanks so much.
"MikeJohnB" wrote:
