Results 1 to 9 of 9
  1. #1
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314

    Toggling sort order between two columns in a combobox at runtime


    I have a database which, among other data, includes a person's name and Amateur Radio Call Sign.

    In my forms, I use a combo box to select a person to link a new transaction table record to [many to one].
    The combo box structure is [Member_ID, Member_Name, Call_Sign]

    I sometimes need to look up a person by name and other times by call sign, but I can only design the combo to sort on one or the other. I can manually change the order in Design mode, but that is awkward.
    How can I make it, so I can toggle the combo box between the two sort orders with a single click of, perhaps, a second control?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Code would change the RowSource sql statement. Or use two combos. Review http://allenbrowne.com/ser-62.html
    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.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You could use a option group with two items, one of which is the default.
    Then reset your combo row source in the option group after update event

    Code:
    Private Sub OptSort_AfterUpdate()
    
    Select Case OptSort
    
    Case 1
    Me.combobox.rowsource = ...
    
    Case 2
    Me.combo.rowsource=...
    
    End Select
    
    End Sub
    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

  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
    Following a PM request, here's a solution provided to explain my last post in detail

    It has 2 forms:
    frmMembers2 – contains a combo & option group for this thread

    The other form frmMembers1 was done for another user who wanted a listbox selection to set a combo box value
    Nothing to do with this post but I’ve left it in as it already existed (though not a method I'd recommend)
    Similarly ignore the update query

    HTH
    Attached Files Attached Files
    Last edited by isladogs; 06-16-2018 at 07:46 AM.
    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
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Option group provides multiple possibilities but just as another option I'll mention a toggle button. IIRC, up is true, down is false. Only 2 options but slightly easier to deal with.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by Micron View Post
    a toggle button. IIRC, up is true, down is false.
    Another good method but surely it has to be up = false as you click it (down) to select/action it

    And of course you could also use 2 toggle buttons in an option group instead of radio buttons

    EDIT - from https://msdn.microsoft.com/en-us/vba...roperty-access complete with 'proprty' spelling error
    When the Value proprty is set to True, the toggle button is pressed in.
    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

  7. #7
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    Just what I needed. It works great. Thanks

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    So IIRC should have bee IIRI (If I Recall Incorrectly).

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    With toggle button, Click event is triggered on both up and down. Down = -1 and up = 0. If you set its DefaultValue to -1 it will start in down position. Likewise if bound to yes/no field, down position is True value.

    Can't recall ever using toggle control.
    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. Sort Order problem
    By Chris@harveynorman in forum Reports
    Replies: 8
    Last Post: 11-22-2016, 10:42 PM
  2. Combobox - Toggle sort between columns
    By kagoodwin13 in forum Forms
    Replies: 1
    Last Post: 04-15-2016, 04:51 PM
  3. Replies: 5
    Last Post: 06-14-2015, 07:56 PM
  4. How to order columns in ascending order?
    By darkingthereturn in forum Access
    Replies: 5
    Last Post: 06-18-2012, 05:24 AM
  5. Report will order/sort 4 columns but not 5. Why?
    By TomHolden in forum Reports
    Replies: 6
    Last Post: 12-17-2011, 04:25 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