Results 1 to 3 of 3
  1. #1
    jle0003 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2012
    Posts
    60

    Dependent comboboxes with fields from different tables

    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!

  2. #2
    alansidman's Avatar
    alansidman is offline Indifferent
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,536
    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

  3. #3
    alansidman's Avatar
    alansidman is offline Indifferent
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,536
    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
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Validation of the comboboxes
    By dr223 in forum Access
    Replies: 1
    Last Post: 01-13-2012, 07:31 AM
  2. Comboboxes go where?
    By PaulCW in forum Database Design
    Replies: 12
    Last Post: 10-04-2011, 02:34 AM
  3. Help with dependent values
    By ceejsing in forum Access
    Replies: 6
    Last Post: 08-05-2011, 06:36 AM
  4. How to Reset Comboboxes
    By bbarrene in forum Programming
    Replies: 5
    Last Post: 01-23-2010, 11:11 PM
  5. 3 Related Dependent Tables
    By jbarrum in forum Access
    Replies: 0
    Last Post: 11-17-2009, 11:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums