Trees,Nesting, fields dependant on parent fields

xenologer

New member
Local time
Yesterday, 16:00
Joined
Dec 15, 2004
Messages
5
Hi,
I'm new to Access, but I've used a lot of SQL

I dont really know where to start on how to do this...

I have items in a table that are organized by Category and SubCategory
where the SubCategories allowed should be dependant on the Category

how do I enforce such a dependance?
I've figured out how to make a column into a drop down selection lists, so you can choose a Category for a specified list
and figured out that the options for such lists can be populated via an SQL query
but I dont know how to attach information about another column of the current row being edited to this query
I want to do something like this
populate SubCategory list by:
select subcategory.id,subcategory.name from subcategory where subcategory.parentCID = ????currentrow.Category????

I've seen some tutorials that seem to do what I want, but all of them involve Forms input validation
I want this type of enforcement to exist within the Table itself

how do I do it?
thanks
 
thanks,
but all those examples use Forms to enforce the dependancy
the people who use this are going to have direct access to the data tables themselves
so the cascading restriction needs to be enforced on the datasheet view itself
is that possible?

I see how the cascading combo box solution works
but the users can simply bypass the Form itself
so i need some other way of ensuring data integrity
 
Last edited:
Form

A good policy is never to let users work on the tables directly, but only with forms. Having hem work on tables, especially with less experienced users, is simply asking for trouble.

You can hide the database window, and disable the shift bypass key.
Search this forum on these topics, there ar many posts about this.
 
others will have access to the tables
its not my choice and i cant do much about it

so basically the only way to enforce nested column options is via a Form?

it seems there is some limited data checking in datasheet view, since i can specify 'Limit to List' for the column lookup
but it looks like this is inadequate since it only takes a static list or query... not the complexity that nested categories need
 
For starters you need to enforce referential integrity. tblCategory --> tblSubCategory are linked on CategoryID. After you select to enforce RI, you will be able to select the cascade delete option. That will cause all related subcategories to be deleted if you delete a category. tblSubCategoryID should also have an autonumber ID. In your main table you technically only need SubCategoryID since that will uniquely identify both the category and SubCategory, however to allow you to create cascading combos in a continuous form, we can violate third normal form and store both CategoryID and SubCategoryID. You can link tblSubCategory --> tblItems on CategoryID AND SubCategoryID. Enforce RI but here you would NOT want to specify cascade delete because you don't want the deletion of a SubCategory record to delete related rows in the items table. So in this case RI would not allow you to delete a SubCategory record if there were ANY related rows in tblItems.

Once RI has been properly enforced, bad data can't creap into the table. So, you are left with the user interface problem. Let me reiterate - users should NEVER under any conditions be allowed direct access to tables or queries. That is simply poor practice. If you want an interface that looks like a table because it takes less space, then create a form with DataSheet view. In this view, columns are sizable and movable. The user can sort on a column. It looks and acts pretty much like a DataSheet made by simply opening a table with one BIG exception. You now have form events in which you can place code to perform edits and support the cascading combos that you want.

Data sheets made by opening tables do not expose any events. That is why they cannot support cascading combos. You need event code to make the cascade work.
 
thats what i thought...
i guess all i can do is make a nice form, and adivse everyone else that if they choose not to use it then nothing will be there to keep them from making mistakes
thanks for the info
 
then nothing will be there to keep them from making mistakes
- NOT SO! If you define the tables properly and enforce RI, Jet will not allow them to enter bad data. There simply won't be any filtering of the combo's RowSource. But, if they choose a SubCategory that doesn't belong to the previously chosen Category, RI will prevent the record from being saved.
 

Users who are viewing this thread

Back
Top Bottom