Combo Box Choices Based on Result From Another Combo Box 1

[Hillary]

I have a database with 2 combo boxes; Category and SubCategory. I want the available choices in SubCategory, dependent on what the user selected in Category.

Example

Category
Fruit
Car
Sport

SubCategory
Apple
Orange
Nova
Mustang
Football
Baseball

If I select Fruit from Category I only want Apple and Orange as a choice from SubCategory

I have a Category table and a SubCategory table.

Category
Category ID (primary key)
Category

SubCategory
SubCategory ID (primary key)
Category ID
SubCategory

There is a one to many relationship between Category.Category ID and SubCategory.Category ID Right now in my database, in another table the full drop down appears for both fields. In the form, only the SubCategory choices show.

How do I make the SubCategory dependent on the Category?

Thanks,
Hillary

 

[jdegeorge]

Hilary

You can set the control query of the 2nd combo box to include the value of the first combo box.

Whenever the value of CB1 changes, the values in CB2 will change.

This is an example of something I did for the ROW SOURCE of CB2:

SELECT [qryTeam/Staff].[StaffID#], [qryTeam/Staff].[Staff Member], [qryTeam/Staff].TeamName, [qryTeam/Staff].[TeamID#]
FROM [qryTeam/Staff]
WHERE ((([qryTeam/Staff].[TeamID#])=[Forms]![frmTask Inputting & Editing]![Team ID]));

Notice that the WHERE is the value of CB1.

Hope this helps.

Jim

"Get it right the first time, that's the main thing..."

 

[Hillary]

Here is my version of your SQL statement. I am being prompted for a parameter when I'm switching from Design to Datasheet view. For the Where statement I tried both the way it is and WHERE (((Category.[Category ID])=[Forms]![Audit Data]![SubCategory].[Category ID]));

SELECT SubCategory.[SubCategory ID], SubCategory.[Category ID], SubCategory.SubCategory
FROM SubCategory, Category
WHERE (((SubCategory.[Category ID])=[Forms]![Audit Data]![Category].[Category ID]));

Thanks for your help!

Hillary

 

[jdegeorge]

Hillary

Your FROM clause is "FROM SubCategory, Category". You don't need to include CATEGORY in the FROM clause because the CATEGORY field you're looking for is coming from another form. Drop that one.

Also, you don't need CATEGORY in your WHERE clause. You have

(((SubCategory.[Category ID])=[Forms]![Audit Data]![Category].[Category ID]))

Try

(((SubCategory.[Category ID])=[Forms]![Audit Data]![Category ID]))

Assuming that "Audit Data" is your form name.

You should try to use some nameing conventions in your work. Tables should start with "tbl" as in "tblSubCategory" and "tblCategory", queries should start with "qry" as in "qryCategory", modules with "mod", macros with "mcr", etc. This way elements in your SQL statements are easier to identify.

Let me know if this works.

Jim

"Get it right the first time, that's the main thing..."

 

[Hillary]

1. The parameter prompt is gone : )

2. In frmAudit Data, there is no data in the dropdown for SubCategory. (Category is okay)

3. When I am in the Query Builder and run the query (red exclaimation point), it shows

SubCategory ID Category ID SubCategory
(Autonumber) 0

I changed the Category ID to a 1 which populated the Autonumber and entered a SubCategory "Test". What the did was write back to the SubCategory table; there was no effect on the form.

4. In your last post you said "You don't need to include CATEGORY in the FROM clause because the CATEGORY field you're looking for is coming from another form." I don't think I was clear earlier becuase they are on the same form, different tables. It's probably not correct but this is how I am set up.

Tables

Category
Category ID
Category

SubCategory
SubCategory ID
Category ID
SubCategory

Audit Data
Audit ID
Category
SubCategory
other audit info...

Forms

Audit Data
Based on tbl Audit Data

There is a one to many relationship between Category and SubCategory but there is no relationship established with Audit Data.

Again, thank you for your time.

Hillary

 

[jdegeorge]

Hillary:

Since both fields are on the same form, you need to modify the WHERE clause to include the CONTROL NAME of the field (called NAME in the PROPERTIES list) rather than the TABLE NAME for the field (called CONTROL SOURCE in the PROPERTIES list).

This is your WHERE clause: (((SubCategory.[Category ID])=[Forms]![Audit Data]![Category ID]))

You want to change the 2nd instance of [Category ID] to the name of that CONTROL on the form instead of the FIELD from the other dropdown box.

Let me know.

Jim


"Get it right the first time, that's the main thing..."

 

[Hillary]

We finally got it! What you sent we still didn't work so I changed the =[Forms]![Audit Data]![Category ID])) to =1. That worked for all the SubCategorys that had a Category ID of 1. The problem was the name of the field on the form. It is Category; the link is Category ID.

SELECT SubCategory.[SubCategory ID], SubCategory.[Category ID], SubCategory.SubCategory
FROM SubCategory
WHERE (((SubCategory.[Category ID])=[Forms]![Audit Data]![Category]));

Thanks for all your help!!!!

Hillary

 

[jdegeorge]

Hillary

In my last post, I said to change the last instance of [Category ID] to the name of the control on the form instead of the field in the table/query.

Maybe I wasn't clear in how I phrased it, but you did what I suggested by using the name of the field from the form instead of the name of the field in the table.

Nice job, and thanks for the star!

Jim DeGeorge
Developer