Conditional value lists?

[Designer]

I need to create a series of fields with value lists similar to this: Field 1 uses value list: Vegetables, Fruits, Meats.

Field 2 uses value list: tomatoes, corn, apples, grapes, beef, chicken.

Field 3 uses value list: Beefsteak, Cherry Tomatoes, Corn on cob, kernel, red delicious, granny smith, red grapes, green grapes, steak, roast, whole chicken, fryer parts.

Now here's the tricky part (well tricky for me - that's why I'm here). After I choose say Fruits from the first value list, I want ONLY choices applicable to fruit to appear in the next value list, in this case APPLES, GRAPES. Then when I choose one of those, say Apples, I want ONLY the following to appear in the value list for field 3: Red Delicious and Granny Smith.

This is actually for a client that sells TOOLS, but I wanted to use everyday examples here that anyone could easily categorize in their head to understand what I'm after. I can post a table of the values for each field if necessary.

NOTE: One thing I forgot to mention when I first posted this... In the real solution I come up with, there will be more than 2 values in each value list. I only showed 2 food values fore each "adjusted" field value list in my example above for simplicity. Also, it will be 4 fields, not just 3.

Any ideas? Is this right under my nose in a portal or relationship and I'm just not seeing it or is it more involved?

Thanks!!

Edited February 14, 2006 by Guest
Trying to make my needs more clear

[Vaughan]

Create a "Products" table with two fields: Type and Item. Populate it with records:

Type ; Item

Vegetables ; tomatoes

Vegetables ; corn

etc

Fruits ; Apples

Fruits ; grapes

etc

Meat ; beef

Meat ; chicken

etc

The order of entry doesn't matter.

Now make a value list "Type" based on the first field in this table.

In the main database, you need a "type" field. This needs to be formatted with the Type value list. Make a relationship from this Type field into the Type field in the Products table. Create an "Item" value list to show only related values based on this relationship, put this on the Item field.

As you add and remove records form the Products table the value lists will change accordingly.

[Designer]

Hi Vaughan,

That worked.

Thanks for the help!!!

Tim

Edited February 14, 2006 by Guest

[djeans]

Hi,

I used your suggestion in a database of mine, but I would like to carry it down to a couple more value lists. For instance, select a vendor, which would then show the categories that vendor sells, then select category, (meat, fish, vegetable, fruit, etc) which would narrow down the item value list in the next field.

So far, following your example above, I was able to create a vendor value list, which would then limit the category list, but unfortunately does not narrow down the next fields values. It shows me every item that vendor sells, no matter what the category.

My tables are "Products" with the fields "Vendor", "Category", and "Product" and another Table "Invoices" also with fields "Vendor", "Category", and "Product" as value lists.

Do I need to create a new relationship for each? I tried, but was not successful.

Any advice would be greatly appreciated.

Thanks

Darron