How to create a list Box which debends on a value in the next field?

[Doorman]

Hi, guys,
Have been trying different approaches literally the whole day on Saturday. Nothing else comes into my mind. Need your help.


I have 2 tables:

Table 1:
CATEGORY : SUB-CAT
Computer : Motherboard
Computer : CPU
Car : tires
Car : wheel

Table 2: 2 Fields as Combobox or List Box
CATEGORY2 : SUB-CAT2

What do I need to do that if the value in SUB-CAT2 is selected then CATEGORY2 is automatically updated. And how to do that if Category2 is selected then the choice for selection in SUB-CAT-2 would be limited to selected Category.
Example.
SUB-CAT2 select "wheel". Value in CATEGORY2 is automatically updated to "Car". Or if I select in CATEGORY2 "Car" then for SUB-CAT2 there is a choice only of "wheel" and "tires".

 

[georgedwilkinson]

I hope this is just an example for the sake of getting your real question answered.

Do a search here on cascading combo-boxes. Even though you may not be using combo-boxes, the concept is the same.

You might also want to do a search on "Bill of Materials" to find out how to properly design/objectify/normalize something like this. For instance, a car is not the only thing that can have a wheel (in fact, a computer could have a wheel, I guess). Nor is a computer the only thing that can have a mother board (in fact, many cars have computers in them).

 

[Doorman]

georgedwilkinson,
thanks. I'll look for cascading (though I thought it is for a form not a table, I wanted to have this dependency in table for selection of entries).

yes this is just an example. Don't see the problem if it would have been a real problem without simplification
Actually the thing is a little bit more complex and has nothing to do with cars and PCs.

 

[georgedwilkinson]

So, are you saying you want to store a value in the table that is calculated based on another field within the table? If that's the case, you don't want cascading combo-boxes.

Probably best just to calculate that value on your form or in a query. However, if you insist on doing it, just use an update query on your table, updating the calculated column from the column(s) used to calculate it.