Results 1 to 9 of 9
  1. #1
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62

    Do not re-order the results from return on multi-select combo box

    I have a combo box I set up whereby options are set in a specific way. I'm not having an option when a user views the data; it does not sort, which is what I want it to do. However, the return string of that information does auto-sort.


    Here's an example of a simple list (note the seemingly random order of the letters):

    Here's the output I'm seeing in the box:

    The reason this is important is I want to create a list of things where the contents are never certain, but the order should always remain constant. Am I missing something obvious so as to prevent a re-alphabetization of the returned choices?
    Thank you!

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    the query behind the listbox, set the sort there.

  3. #3
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62
    Quote Originally Posted by ranman256 View Post
    the query behind the listbox, set the sort there.
    It's not a query. The options were made from the table in the Lookup tab.
    Click image for larger version. 

Name:	Lookup.PNG 
Views:	16 
Size:	9.8 KB 
ID:	33165

    Or am I misunderstanding this? To be clear, I do *not* want a sort added. The way it looks above, with the combo boxes, is the same order I want to display the information once all those choices have been made.
    Last edited by Ramtrap; 03-19-2018 at 09:29 AM. Reason: additional information

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Change the combo so it has two columns with the first column hidden (width=0)
    The first column should be 1, 2, 3 etc.
    The list will then retain the order you want
    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

  5. #5
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    if you use a lookup table instead of a value list it will return the correct order. I tried it with a lookup table with a PK and a value and it worked

  6. #6
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62
    Quote Originally Posted by ridders52 View Post
    Change the combo so it has two columns with the first column hidden (width=0)
    The first column should be 1, 2, 3 etc.
    The list will then retain the order you want
    I understand what you're saying, as well as what @moke123 (below) is saying, and it's working so long as I have a list of no more than 9 items. It seems that when Access is reading anything over 9, it's following an improper sort (i.e. instead of going 1, 2, 4, 11, it's going 1, 11, 2, 4).

    Am I doing something wrong? I've tried changing the bound column, deleting and re-working the tables, queries, etc. and nothing is working.

    Here's my Lookup Criteria
    Click image for larger version. 

Name:	LookupCriteria.PNG 
Views:	8 
Size:	9.4 KB 
ID:	33188

    Here's query criteria (tried deleting this query, as well, and have it read in from the file, but no luck):
    Click image for larger version. 

Name:	qryCriteria.PNG 
Views:	8 
Size:	3.3 KB 
ID:	33189

    And here are the differences between options when only 1-9 may be selected vs what happens when you have 10+ selected. Note that it *should* read "R, D, E, A" since that's the order I want to keep (note that the order displayed in the option boxes are in the proper order, but the returned value--what is displaying in the drop-down--is not.):
    Click image for larger version. 

Name:	1to9.PNG 
Views:	8 
Size:	4.2 KB 
ID:	33190 Click image for larger version. 

Name:	1to10.PNG 
Views:	8 
Size:	4.5 KB 
ID:	33191

  7. #7
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62
    Quote Originally Posted by Ramtrap View Post
    I understand what you're saying, as well as what @moke123 (below) is saying, and it's working so long as I have a list of no more than 9 items. It seems that when Access is reading anything over 9, it's following an improper sort (i.e. instead of going 1, 2, 4, 11, it's going 1, 11, 2, 4).

    Am I doing something wrong? I've tried changing the bound column, deleting and re-working the tables, queries, etc. and nothing is working.

    Here's my Lookup Criteria
    Click image for larger version. 

Name:	LookupCriteria.PNG 
Views:	8 
Size:	9.4 KB 
ID:	33188

    Here's query criteria (tried deleting this query, as well, and have it read in from the file, but no luck):
    Click image for larger version. 

Name:	qryCriteria.PNG 
Views:	8 
Size:	3.3 KB 
ID:	33189

    And here are the differences between options when only 1-9 may be selected vs what happens when you have 10+ selected. Note that it *should* read "R, D, E, A" since that's the order I want to keep (note that the order displayed in the option boxes are in the proper order, but the returned value--what is displaying in the drop-down--is not.):
    Click image for larger version. 

Name:	1to9.PNG 
Views:	8 
Size:	4.2 KB 
ID:	33190 Click image for larger version. 

Name:	1to10.PNG 
Views:	8 
Size:	4.5 KB 
ID:	33191
    Sorry, all. I think I have (at least) a workaround. I just made a calculated field and added 100 to the auto-numbered ID (thus, I shouldn't encounter the error unless I had 1000+ items now).

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    When you added the first column 1, 2, 3 etc, did you wrap each number in quotes? If so, Access will have treated the column as text and sorted it in alphabetical order.
    So it would be 1, 10, 11, .... 19, 2, 20 etc.
    You should have omitted the quotes and it would have been a number column and sorted by number
    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

  9. #9
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62
    No, they were not in quotes; it was an AutoNumber, the standard ID field whenever you create a table. No idea why it was treating it alphabetically.

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

Similar Threads

  1. Replies: 5
    Last Post: 02-14-2015, 04:11 AM
  2. Multi Select Simple Returning No Results
    By jsimard in forum Programming
    Replies: 11
    Last Post: 01-24-2012, 10:28 PM
  3. Turn a list into a Multi select combo box
    By seth1685 in forum Programming
    Replies: 1
    Last Post: 01-11-2012, 07:52 AM
  4. Replies: 4
    Last Post: 08-16-2011, 05:54 PM
  5. Replies: 8
    Last Post: 12-22-2010, 08:12 AM

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