Results 1 to 3 of 3
  1. #1
    Chris_James is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2017
    Posts
    1

    MultiValue Option in form and how they populate a table

    Hi, first post on here and relatively new to access with basic vba knowledge form Excel.



    I have a problem i am struggling with. Basically I have the following tables:

    - Tbl_Brand
    - Tbl_Variety
    - Tbl_DrinkSize
    - Tbl_Product

    Using soft drinks as an example I am trying to create a form that allows me to select i.e Coca Cola from the category combobox, diet coke from Variety Combobox and then this is where i come unstuck. Basically i want to be able to select multiple drink sizes for the same Variety. Ie diet coke is available in 330ml, 500ml, 1L, 2L etc. I have these sizes in Tbl_Drinksize.

    By doing the above the aim is then when i sell a product via an invoice and choose product it would read "diet coke-500ml" for example. I have read not to use multivalue fields so not really sure how to do this on a form and also then how to populate it back into a table.

    Any help will be greatly received.

    Cheers

    Chris

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    do you mean to populate the base tables, or to populate an order?

    For an Order, you would have a form with combo boxes.
    user picks Brand,
    the query for Variety looks at cboBrand to limit the choices.
    then when user picks Variety,
    the cboSize looks at cboBrand to limit its picks.
    etc...

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Tbl_Product should have a record for each combination of Brand, Variety, Size.

    Then I presume you have something like Tbl_Order and Tbl_OrderDetails tables.

    Tbl_OrderDetails form can have cascading comboboxes as described by ranman to aid selecting record from Tbl_Product but be aware they don't work nice with Continuous or Datasheet form. Cascading comboboxes require some code (VBA or macro).

    A single combobox can be made to work with a RowSource that concatenates fields: SELECT ProdID, Brand & ", " & Variety & ", " & Size AS Product FROM Tbl_Product ORDER BY Brand, Variety, Size;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 11
    Last Post: 11-01-2016, 03:58 AM
  2. Replies: 3
    Last Post: 08-07-2016, 09:14 AM
  3. Replies: 10
    Last Post: 07-04-2015, 02:55 PM
  4. Replies: 2
    Last Post: 03-28-2014, 11:25 AM
  5. Replies: 2
    Last Post: 10-22-2012, 05:32 PM

Tags for this Thread

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