Dependent comboboxes with fields from different tables

[jle0003]

I thought that my previous request on how to create a dependent combobox based on selections from two previous comboboxes, but the example provided only worked if the fields were all in the same table. Here is an explanation of my problem:

I have 3 tables:

FoodType
fruit
vegetable

Color
green
red
yellow

FoodName
apple
banana
cabbage
green beans
kiwi
lettuce
lime
raspberries
beets
spinach
star fruit
strawberries
squash
tomato

I have 3 comboboxes on a form: cboFoodType, cboColor, cboFoodName.


I would like to filter the value lists in cboFoodName based on the selections from cboFoodType and cboColor.
For example, if I select "fruit" from cboFoodType and "red" from cboColor, I want the value list available in cboFoodName to consist of "apple;raspberries;strawberries"
Likewise, if I select "vegetable" from cboFoodType and "green" from cboColor, I want the value list available in cboFoodName to consist of "cabbage;green beans;lettuce;spinach"

Can someone tell me the best way to approach this? Thank you!

[alansidman]

You will have to build some very complex VBA logic that will cover each and every combination of vegetables and colors. I don't believe that cascading combo boxes can do this. I would suggest that you look at Select Case statements. Here is a link to how that would work.

http://www.techonthenet.com/access/f...anced/case.php

Alan

[alansidman]

With a little bit of help from Bob Fitz and VillaRestal, attached are two solutions to your issue. Pick the one you like and go for it.

Alan