Is there a way to implement a cascaded or linked comboboxes in the database similar to the way you can do it in a form. I have two fields, a Major Category and a Sub Category field. When the user enters or picks a value from the combobox for the Major Category, then the Sub Category field should only show items that correspond to the Major Category field, or allow the user to only type in one of the ones on the list.

I have a separate look up table with two columns in it connected to these fields using two queries, one for the major category that only returns the first column, and one for the sub-category that shows both columns right now so the user can choose the right values, but which really should use the major category's value to limit the selection query so that only the matching values show or be entered.

Is this possible without a form, using only a query or what is available at the table level?

Thank you.

I don't think so, but you shouldn't be doing data entry in tables anyway.

mmcdonal

I agree about not doing data entry directly into the table, but coming from Oracle, I like being able to implement validation rules at the database level instead of the form. In this case when the master field contains a particular value from its pick list then the slaved field is only able to be values that correspond to the master's value. Both the master and slave fields' get their values from the same table, the master values are in one column, and the slave values in an other. So for instance, say the following is part of the lookup table used for the Master and Slave fields:

    Master_Col Slave_Col
    MVal1 SVal1
    MVal1 SVal2
    MVal2 SVal3
    MVal2 SVal4
    MVal2 SVal5
    MVal3 SVal6

Then using a query with the master field, I can show only the Master_Col of data. Currently, for the slave field, I show both columns and hope that the user picks a row that where the Master_Col value matches the one they put in the master field. What I'm trying to do is limit the look up of the slave column so that it only show the rows that do match the master field's value. In the form I am able to do this by including the form and field in my query, but I don't see a way to reference the row's master field value and put that in the slave field's query where clause.

There might be a workaround. If you have a table relating the subcategories to their respective masters, you might be able to set a trigger on the master_col above to change the query from which the slave_col draws its list of values from. The other way would be to set a trigger on the slave_col that makes sure it is a valid subcategory.

I'm not aware of triggers in Access 2000. I know of field validation, but this doesn't work for cross field validation, or support vba code.

Are you speaking of events in forms? I am using already using the exit event on the combobox control associated with slave_col from the database to change the combobox list source query to use the current value of master_col's combobox. Unfortunately, this doesn't work entirely, because the ListIn attribute is often -1, meaning that the value of the combobox isn't in the list, yet it is. I have to retype the same value or reselect it from the list in order to get the slave_col combobox ListIn to get the right number.

To get around this I changed the select statement to contain the complete path to the form and the master_col combobox value. This means that the actual query doesn't change, which is less code, but in order for the query to "see" the change in master_col, I need to use the slave_col's control.requery method, which slows things down a bit.

Actually, it's a bit more complex that what I say because I'm using the form's current event to move data from controls that are bound to master and slave to unbound controls. The unbound slave control is the one that I use the control.requery to keep its ListIn attribute synchronized. The reason for the two sets of bound and unbound controls is because the query the form is based on makes the bound fields non-updateable.

I got a bit wordy before.

How would I set up the triggers you spoke about? I'm using Access 2000 with a native database.

I apologize, apparently triggers only work with Access Projects using SQL Server. If you have that available, there's a decent guide to using them under Help. And people at my company wonder why I grumble about Access not being a real database..

Thanks for trying :)

Grumble about Access not being a true database, me too. :(
You'd think after this many years that they'd relize that things like database level triggers or vb validation (sort of a trigger) are necessary. Maybe some day.

Thanks again.