Results 1 to 6 of 6

Thread: Drop down lists

  1. #1
    Join Date
    Nov 2007
    Posts
    7

    Exclamation Unanswered: Drop down lists

    Hi

    I am designing a simple database for a food shop, using access. I will be using access 2007. I have a drop down list with food types, such as "fresh food", "drinks" etc. I want a drop down list next to it that displays sub food types. However, I want it so when "drinks" is selected in the first list, ONLY "alcoholic", "fruit juices", and "cans" are listed. This will also be the same for when "fresh food" is selected from the first list. ONLY fresh food types will show in the second list.

    Cheers David

  2. #2
    Join Date
    Nov 2003
    Location
    Sussex, England
    Posts
    404
    Use events.

  3. #3
    Join Date
    Nov 2007
    Posts
    7
    what are events

  4. #4
    Join Date
    Nov 2007
    Posts
    7

    events

    what are events?

    I want to do this:

    I am designing a simple database for a food shop, using access. I will be using access 2007. I have a drop down list with food types, such as "fresh food", "drinks" etc. I want a drop down list next to it that displays sub food types. However, I want it so when "drinks" is selected in the first list, ONLY "alcoholic", "fruit juices", and "cans" are listed. This will also be the same for when "fresh food" is selected from the first list. ONLY fresh food types will show in the second list.

    David

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depends on how your data is stored
    but a classical way of doing that is to have (say) two UNBOUND list boxes retrieving data from your tables
    the fist is populated from the product types table, the second is populated when the user selects something from the first list box. So you then tell the second list box to select items based on the selected value in the first list box

    say you have a product type table which is something like
    1 Beverages
    2 Grocery
    51 Alcoholic
    94 Vegetables
    95 Potatoes
    96 Fruit Juice
    2000 Carbonated
    3 Still
    5 Mineral Waters

    but you can extend that functionality by defining what product type belongs to what parent type

    1 Beverages
    2 Grocery
    51 Alcoholic 1
    94 Vegetables 2
    95 Potatoes 94
    96 Fruit Juice 1
    2000 Carbonated 1
    3 Still 5
    5 Mineral Waters 1

    so form that you can navigate up and down your product type

    you can then query your product type table to find all product types that belong to the selected items, and based on that select all actual product in that category. place some code in the list box on click event that sets a filter or selects a new recordset for the listbox2.

    eg select ProdID, ProdDesc from myproducttable where myproducttable.ProdType in (1,51...)

    A slightly easier apparoach is to use a say 3..5 digit code which identifies the product

    eg
    A?? Beverages
    B?? Grocery
    AA? Alcoholic
    BA? Vegetables
    BA1 Potatoes
    AB? Fruit Juice
    2000 Carbonated
    AC9 Still
    AC? Mineral Waters

    the problem, with this approach is that its not very flexible, if you decide you need another sub category your are stuffed.... it needs a rewrite. But if you know you categories and you know they are never going to change or are not going to change significantly then it can work. If this is for a work project then make sure you get the user to sign off on the requirement before you code anything, if its for homework, then put it in as an assumption / limitation on the system design

    HTH

  6. #6
    Join Date
    Sep 2006
    Posts
    9

    Use sub-form

    You could also use a Form & sub Form approach.

    Your form would access the food types table (drinks, sandwhiches, etc.). Your sub form would access the sub types and would require that each sub type also had a field for the food type that this sub type was in. Easy matter then to link the 2 such that when you changed the food type a different list of sub types would be displayed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •