Results 1 to 11 of 11
  1. #1
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118

    Multivalued Combo Alternative

    I have a DB with a few multivalued combo boxes. E.g. Select Days you are available: with a combo listing Mon,Tue,Wed etc. and you can select Mon & Wed.


    I am intending to convert the backend to My SQL and understand that Multivalued are not supported. How can I convert my Multivalued fields into normalised table structure that will have the same look and functionality of the built in Access Multivalued combo box?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I recommend you post a copy of the database - remove anything confidential - include instructions to get to the issue you're are describing. You can alter your table structure to remove the need for MVF fields. But you are talking in the abstract, and readers need something concrete in order to provide more focused responses.
    You may find something in the Similar Threads at bottom of the page.

  3. #3
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118
    My DB has over 300 objects so would be a bit difficult to anomalies so I have created an example.
    MyForm shows a combo box with values from a MVF. I need to create something with the same functionality as this but that will be supported in MySQL. I believe I need to create a link table with maybe a Y/N field type, but I am struggling
    CheersClick image for larger version. 

Name:	MVF.JPG 
Views:	37 
Size:	23.9 KB 
ID:	48074

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Based on your example, a consideration for table structure would be

    Person --->PersonFavColor<---Color

    Where
    Person table has fields
    PersonID.....PK
    PersonName...

    Color has fields
    ColorID.......PK
    ColorName..

    and PersonFavColor (junction table) has fields

    PersonID
    ColorID

    You would use a multiSelect listbox to display the colors
    The selected favorites and the current PersonID would be used to populate table PersonFavColor.

    Any relational database should support the table structure shown above.

    You can search youtube with "ms access multiselect listbox" to get video tutorials.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Also, using a multi-select listbox would require code to loop the selected items and save a record for each.
    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.

  6. #6
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118
    Thanks both but was hoping for a bit more detail; What would table/query would the combo box be based on?, what table would the Y/N check box be in?, how can I add an OK,Cancel and possibly a Select all button to a combo box? If I need code to do any of this, what is it and were would it go? Sorry but only a beginner. Thanks

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    We are advising that for multi select you should use the Listbox control not combobox.

    Here is a youtube describing multiselect listbox by evil programmer

    It may facilitate communications if you could post a copy of your database or a custom copy that focuses on the issue you are trying to resolve.

    It seems you are describing a part of a larger issue in abstract terms. More details please.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If you want checkboxes and a list that looks like a multi-select combobox, that would require an unbound form and lots of code to write data to table. This would be fine if your list is not long, say no more than the 6 colors as shown in your example.
    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.

  9. #9
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You should read very carefully this link ->> Multivalued Fields - BAD
    Last edited by ssanfu; 06-22-2022 at 01:26 PM. Reason: updated link URL

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Sorry Steve but links to my old site no longer work reliably.
    This is the updated link https://www.isladogs.co.uk/multivalu...lds/index.html
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Thanks for the update Colin. I have updated my reference file to the new link.
    (and updated the link URL in Post #9)

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

Similar Threads

  1. Replies: 2
    Last Post: 03-29-2016, 04:52 AM
  2. Multivalued combo box
    By ezz in forum Access
    Replies: 3
    Last Post: 04-14-2015, 07:34 AM
  3. Replies: 1
    Last Post: 02-04-2015, 03:12 PM
  4. Replies: 2
    Last Post: 02-02-2015, 03:23 AM
  5. Replies: 6
    Last Post: 07-03-2014, 02:32 PM

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