Restricting Lookup Values in a Subdatasheet

[ultimateguy]

I am new to Access and am currently learning it so that I can construct a database of trivia questions.



I have a "Questions" table with question, answer, type, etc. This table is linked to a "Categories" table with a many-to-many relationship using a junction table called "Questions_Categories". The "Categories" table is also linked to a "SubCategories" table with a Categories as the parent and SubCategories as the child. I have attached the database for ease of understanding.

When I enter data into the "Questions" table using datasheet view, I open up a subsheet that shows me the Category and SubCategory fields which have their own lookups.

My problem is the following: I want each category to have its own set of SubCategories. For example, the "Region-Specific" category has "Canada", "London", and "Ontario" as sub categories. However, the movies category should not have any sub categories. The problem is that when I choose Movies in the sub datasheet, the subcategory lookup still shows Canada London and Ontario when I don't want it to show anything. It doesn't make sense to have a Movies category with a London subcategory.

I have a feeling that maybe the layout of my database is not set up for this. Any advice would be appreciated.

[June7]

Review http://www.datapigtechnologies.com/f...combobox2.html

[ultimateguy]

Review http://www.datapigtechnologies.com/f...combobox2.html

I set up the combo boxes the way it outlined, using cmb_CategoryID and cmb_SubCategoryID in the Questions_Categories form. I put Questions_Categories as a subform in the form Questions. Now when I load the form Questions, it asks me for the parameter Lookup_CategoryID.Category... I have no idea why.

cmb_SubCategoryID Row Source:
SELECT Questions_Categories.SubCategoryID FROM Questions_Categories WHERE (((Questions_Categories.CategoryID)=[forms]![Questions_Categories].[cmb_CategoryID])) ORDER BY Questions_Categories.SubCategoryID;

I am pulling my hair out at this point

[June7]

I finally had a chance to look at your db.

First comment, you are using lookups with alias in the table. Advise not to. Review http://access.mvps.org/access/lookupfields.htm

Second, Type field in Questions table is multi-value. I NEVER use multi-value field. Be sure you fully understand multi-value before commit to this.

Third, the subform container SourceObject is table. Cannot have code behind the table to do what you want. The SourceObject must be a form.

Fourth, I don't understand the Region-Specific category value. Is this the only category that will have sub-category? I am inclined to think this should be a field in Questions table. Maybe need another value - All. Can a question be associated with more than one Region? Can a question be associated with more than one category? Is that what you are trying to do with Questions_Categories table, create these associations?

[ultimateguy]

I finally had a chance to look at your db.

First comment, you are using lookups with alias in the table. Advise not to. Review http://access.mvps.org/access/lookupfields.htm

Second, Type field in Questions table is multi-value. I NEVER use multi-value field. Be sure you fully understand multi-value before commit to this.

Third, the subform container SourceObject is table. Cannot have code behind the table to do what you want. The SourceObject must be a form.

Fourth, I don't understand the Region-Specific category value. Is this the only category that will have sub-category? I am inclined to think this should be a field in Questions table. Maybe need another value - All. Can a question be associated with more than one Region? Can a question be associated with more than one category? Is that what you are trying to do with Questions_Categories table, create these associations?

Thanks for taking the time to look at this. Is there a way to quickly change the lookup with alias to a Number field that displays foreign keys?

Each category may or may not have a sub-category. For example, the category Region-Specific currently has subcategories London and Canada, and in the future, Science will have Physics, Chemistry, Biology, etc. I just want to have flexibility in the future when I search for questions. Each question may have multiple categories or sub-categories, and each category and subcategory will have multiple questions. This is what I was trying to do with the junction table Questions_Categories.

I am not sure what is best for the subcategories. Should I have a "None" value to avoid blank values? Would appreciate any advice you have.

[June7]

I presume each subcategory will be associated with only one category. For instance Physics is in the Science category but could not be in Region-Specific. I suggest a junction table that associates the categories and subcategories. Then the Questions_Categories table would save the Question ID and the Cat_SubCat ID as foreign keys.

Then you might want to make use of cascading (dependent) comboboxes for selecting Cat_SubCat ID. Review: http://www.datapigtechnologies.com/f...combobox2.html

The fields with lookups in table don't need to be changed to anything, they are already Number type fields, just change the Lookup properties from combobox to textbox.

[ultimateguy]

I made a Categories_SubCategories junction table as you suggested and created a form based on this table. I added the comboboxes and set it up like that video said. When I run the query it correctly shows the SubCategory values that it should, but when I save the query and go to the form, the SubCategory combobox stays blank, even with the Requery added to the Category combobox. What could be wrong?

I have attached it so you can see what I did.

[ultimateguy]

I have fixed the issue using some code you used in a previous thread. Thank you!!

https://www.accessforums.net/forms/c...ble-16462.html

[ultimateguy]

I set up cascading Combo Boxes in a form called Categories_SubCategories and they work correctly and are able to update the table Categories_SubCategories.

When using this form as a subform in the form Questions, there are 2 problems.

First:
When I choose a value in the master combo box of the subform, I get the message "You can't assign a value to this object." I click OK, yet the table is still updated with the correct value. This occurs when trying to add a new record with the combo box.

Second:
The next problem is that when there is a value in the master combo box of the subform, I am prompted for the value of Forms!Categories_SubCategories.cmb_CategoryID, which is the master combo box. This is the criteria that makes the slave combo box only display the values associated with the value displayed in the master. It seems that when inside a subform, Access doesn't know what the master combo box value is anymore.

Again, when being used in the primary form, it works fine. These problems only exist when being used as a subform in the form Questions.

It seems the learning curve for Access is much steeper than I had anticipated...

[June7]

Categories_SubCategories form should not be used as a subform in Questions. Only purpose of this form is to create new records that associate categories and subcategories. Cascading comboboxes are not useful in this form.

Now you need a junction table (Questions_Categories) that will associate questions with Category_SubCategories records. This assumes each question can be associated with more than one category/subcategory. This is where the cascading comboboxes can be useful.

[ultimateguy]

I am trying to visualize how to use the cascading Combo boxes but I am at a loss. The Questions_Categories table has a QuestionID field and a Cat_SubCatID field that links to the Categories_SubCategories table. If I use cascading combo boxes, say the master box for Category and slave for SubCategory, how do I make the Cat_SubCatID field update according to the CategoryID and SubCategoryID chosen by the cascading boxes? The latter two ID's appear in Categories_SubCategories table. Is there a way to search for the Cat_SubCatID that corresponds to the CategoryID and SubCategoryID?

[June7]

Options for data entry:

1. single form bound to Questions_Categories with comboboxes to select question and category_subcategory (use cascading comboboxes for the category/subcategory)

2. main form bound to Questions and subform bound to Questions_Categories with the cascading comboboxes

3. main form bound to Categories_SubCategories and UNBOUND cascading comboboxes to find the desired Category_Subcategory record and subform bound to Questions_Categories and combobox to select question

For options 1 and 2, purpose of the cascading comboboxes is to aid user in selecting the Category_Subcategory ID. User selects category from UNBOUND combobox which will restrict the choices of subcategories in second combobox. User selects subcategory and the ID is saved.

For option 3, the cascading comboboxes serve as filter/search tools to locate desired Category_Subcategory record on the main form.

Options 2 and 3 are form/subform arrangements and subform container control Master/Child Links properties will synchronize related records and automatically save the FK ID.

[ultimateguy]

Thank you. I have opted for Option 2. I am still having a problem with the slave combo box in the subform. When running the query for the row source, I am always prompted for the value of the expression in Criteria. If I put the value in manually it works. It seems that I have the wrong expression for Criteria... Access can't seem to find the CategoryID from the master combo box.

[June7]

Both comboboxes are on the subform? You are saving CategoryID into Categories_Subcategories?

RowSource for Categories combobox, named cbxCat:

SELECT ID, Categories FROM Categories ORDER BY Categories;

RowSource for SubCategories combobox:

SELECT ID, SubCategory FROM Categories_Subcategories WHERE Category = [cbxCat] ORDER BY SubCategory;

You need to be aware that cascading comboboxes with alias don't work nicely in Continuous or Datasheet View forms. I have used cascading comboboxes only once for data entry and the form was set up in Single View with code to refresh the second combobox in the form OnCurrent event as well as the first combobox AfterUpdate event.

I am beginning to think you might be better off using just a single combobox that lists the subcategories and the ID from Categories_Subcategories table.