Cascading Combo Boxes as Validation

[Stew]

I have a data validation table seperate from my main table. For certain fields I need to make them dependent on the previous selection. I have linked the excel spreadsheet I am using for my data validition. I have imported this into my database. I am setting up the data validation on my main table. I'm trying to make the cat field dependant on the status. I want only the valid options to be available in a drop down menu.

[HansV]

I have attached an example from Microsoft that shows how it's done.

SyncCombo.zip

[Stew]

I'm not sure if that applies to what I am trying to do. On the main table, in designview. I am pulling lokup values from that access template basically. I need to set up the validation inside the field properties if possible to make the second drop down box only show the cat codes for the status that they already chose. I just need to know if this is even possible.

[HansV]

It is *exactly* what you want to do. You need to create a Cat table and a Status table like the Categories and Products tables in the sample database.

Here is the database from my previous reply with Cat instead of Category and Status instead of Product. Nothing else has been changed.

SyncCombo.zip

[Stew]

When I set it up like that, it changes all my values for the status on every update due to the AfterUpdate event redoing the query. I need that information dependent to each record. That is why I was trying to see if there is a way to put that in the data validation option in field properties

[HansV]

Where are you using this? In a datasheet or continuous form?

[Stew]

Useing it in a datasheet

[HansV]

Useing it in a datasheet

I'm sorry, it won't work there - at least not the way you want. Cascading combo boxes work best in a single form (i.e. a form that displays a single record at a time). With a couple of tricks, you can also make it work in a continuous form, but not in a datasheet form (or in a table or query).

[Stew]

So there is no way to Validate a datasheet to a dependent field before it?

*edit* Let me repharse that, is making a nasty if statement the only way with all the possible inputs in it.

[HansV]

No, at least not a good one in Access 2007 or before. I don't know if Access 2010 has new capabilities in this respect, I don't have that version yet.

[Stew]

Thanks alot for your help in this. Guess I'll just have to make a nasty if statement or just have to do a visual sanity check after the input is in.

[HansV]

You can make a continuous form look like a datasheet, and as mentioned, you can use cascading combo boxes in a continuous form with a bit of work.