Results 1 to 4 of 4
  1. #1
    Mariee is offline Novice
    Windows 11 Access 2007
    Join Date
    May 2023
    Posts
    1

    Limit a list depending on selected category

    Hi Sorry if this is a stupid question, I am very new to access.



    I want to create a unique record for different categories.
    For example, I have different categories of food: Fruit, Veg, Cereal, Dairy.
    Each will then have a type: for fruit, it could be unspecified, banana, apple, etc

    I would like all the food in the same table with a drop down menu that allows me to enter only food that matches the right category. (so if the category is dairy, I can't select banana)
    what is the simplest way of doing that?

    At the moment I have a table list of category, one for the type and I want them togther in a table called FOOD so I can assign the same attributes to them.

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Search for cascading combos.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    For start, you have to create tables where you register various info handled in your DB. Related to your post #1, you need tables like:
    tblFoodCategories: FoodCategotyID, FoodCategory (my advice is, FoodCategoryID will be autonumeric, and FoodCategory text);
    tblFoods: FoodID, FoodName, FoodCategoryID (FoodID will be Autonumeric, FoodName will be text, and FoodCategoryID will be long integer and is a foreign key linking food category with food. I'd prefer using long integer FK linking to autonumeric PK in source table, as this way you can at any time edit food categories texts when there is a need for it.).

    Then you have a table, where you added a row, and need to insert a food info into this row. I.e. this row has the field like FoodID (a long integer FK, which links some food from tblFoods with the row you are editing in your work table. In your form, to select this food you use a combo box which is linked to FoodID FK. To select the food of specific category, you either:
    Have a unbound combo box in your form, where you can select a category, and whenever you change the category, the RowSource property for combo box to select food item is updated, so the query there is filtered by setting WHERE clause to FoodCategoryID selected in 1st combo (a solution posted by Welshgasman);
    Have the RowSource of the combo for food item selection in your form like:
    Code:
    SELECT f.FoodID, fc.FoodCategory & ": " & f.FoodName As Food FROM tblFoods f INNER JOIN tblFoodCategories fc ON fc. FoodCategoryID = f.FoodCategoryID ORDER BY 2 Asc
    With 2nd solution, the combo displays and selects foods like "dairy: milk". When you activate the combo, and start typing with 'd' then combo starts suggest foods, which belong to categories starting with 'd' and alphabetically down from there. You continue typing until food items starting with "dairy" are displayed, and select the product you want from those.

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    The attached db is an example of what you are trying to do.

    Post back if you have questions.
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

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

Similar Threads

  1. Replies: 3
    Last Post: 11-05-2021, 02:57 PM
  2. Combo box limit to list doesnt limit
    By mcmcd99 in forum Access
    Replies: 6
    Last Post: 11-09-2017, 07:31 PM
  3. Newbie: How to filter depending on previous selected.
    By Zeeker in forum Database Design
    Replies: 1
    Last Post: 01-28-2016, 12:34 PM
  4. Replies: 6
    Last Post: 11-26-2015, 01:54 AM
  5. Replies: 3
    Last Post: 07-15-2015, 03:14 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