Results 1 to 13 of 13
  1. #1
    smaier is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2020
    Posts
    43

    Programmatically change listbox sort order?

    Hello, all!



    As it stands right now I am using control buttons at the top of a listbox to sort the column(s) they are over. I'm doing this with an On Click event that runs some basic VBA to alter the row source property of the listbox. Something like:

    Code:
    Private Sub SortCNCTool_IDButton_Click()
    
    
        Me.SeachListBox.RowSource = "SELECT [1-ToolSearchQuery].[CNCTool_ID], [1-ToolSearchQuery].[Description], [1-ToolSearchQuery].[XSection] FROM [1-ToolSearchQuery] ORDER BY [CNCTool_ID]; "
    
    
        DoCmd.Requery "SearchListBoxExact"
    
    
    End Sub
    ...with each button's code having its own "ORDER BY [whatever]". This works fine, but it will only sort in ascending order. Is it possible to set the sort order by toggling the listbox's Row Source similar to above or do I have to use a sql string?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    keep the sort orders in queries:

    original:
    Me.SeachListBox.RowSource = "qsSort1"


    change it by:
    Me.SeachListBox.RowSource = "qsSort2"

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I have to wonder if you know how to use sql in vba will the answer given make any difference? You'd have to know that when not specified in vba the default sort is ascending, which makes me think that you might also need to know that the query design provides a Sort: row.
    I don't see any issue with doing it in vba, especially if you have several sorts you want to provide. Let's say
    - sort by A ascending
    - sort by A descending
    - sort by B ascending
    - sort by B descending
    - sort by A ascending and B descending
    - sort by A descending and B ascending
    ... and so on. So what, design 8, 10, 12... queries for one purpose or learn some vba?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    smaier is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2020
    Posts
    43
    Thank you for the replies, ranman and Micron.

    I already have controls that toggle between queries that feed listboxes, I just didn't know if you could alter the listbox's Row Source property to control the sort order. Creating new queries was a logical solution but I just thought I'd ask since it seemed cleaner if it were possible in the way I mentioned, which it looks like it isn't.

    Thank you.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by smaier View Post
    it seemed cleaner if it were possible in the way I mentioned, which it looks like it isn't.
    Why do you say that? If it is possible to change the rowsource to a query, doesn't it stand to reason that you could change it via sql in code? This isn't how I'd probably code it but f'rinstance if I was choosing the sort order from a combo:
    Code:
    Private Sub SortCNCTool_IDButton_Click()
    Dim strSql As String, strSort As String
    
    strSql = "SELECT [1-ToolSearchQuery].[CNCTool_ID], [1-ToolSearchQuery].[Description], "
    strSql = strSql & "[1-ToolSearchQuery].[XSection] FROM [1-ToolSearchQuery] "
    
    'some sort of test determines what the sort order should be
    If Me.cmb2 = "Ascending" Then strSort = "ORDER BY [CNCTool_ID]"
    If Me.cmb2 = "Descending" Then strSort = "ORDER BY [CNCTool_ID] Desc"
    
    Me.SeachListBox.RowSource = strSql & strSort
    DoCmd.Requery "SearchListBoxExact"
    
    End Sub
    I have done this before by having no listbox headers; instead, making buttons look like the headers and assigning a click event that sorted that column. The even set module level variables to values that stored the last click result -ascending or descending, so the next click just implemented the opposite of the variable value.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by smaier View Post
    ..... I just didn't know if you could alter the listbox's Row Source property to control the sort order.
    Of course you can control the sort order.
    To sort descending, use
    Code:
    Private Sub SortCNCTool_IDButton_Click()
       Me.SeachListBox.RowSource = "SELECT [1-ToolSearchQuery].[CNCTool_ID], [1-ToolSearchQuery].[Description], [1-ToolSearchQuery].[XSection] FROM [1-ToolSearchQuery] ORDER BY [CNCTool_ID] Desc; " 
       DoCmd.Requery "SearchListBoxExact"
    End Sub

    You can use code to change the sort order.... see example dB



    BTW, it's not good to begin object names with a number. Also shouldn't use special characters (-) in object names. Names should be letters and numbers only (exception is the underscore)



    Drat!! Micron stole my idea..... again!! (I need to type faster)
    Attached Files Attached Files

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    (I need to type faster)
    You need to do less work! Looking at your post history, you'd think that you were getting paid by the clock or something! Heck there is an hour between my and your post. Actually, I admire the effort but I won't even attempt to come close to the effort some people put in. It's hard to know where to draw the line, but I usually prefer to guide rather than spoon feed.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Micron... yes, I get started on a reply, then get sidetracked by work. I try to work slow enough for what I am getting paid, but sometimes I have to produce.


    And I understand the guiding, but sometimes it is easier to give a dB and let them try and understand what is happening. At least, that is how I taught myself dBIII/IV and early Access. It helped me to learn by tearing apart example dBs.........

  9. #9
    DittoBird's Avatar
    DittoBird is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2015
    Location
    Newfoundland & Labrador, Canada
    Posts
    59
    Quote Originally Posted by Micron View Post
    ... and so on. So what, design 8, 10, 12... queries for one purpose or learn some vba?
    I suppose one could have a default query stored and use a VBA function to change the ORDER BY clause. It would make it easier to manage if one needs to change what is in the query. Same thing with where clause. I found with 100s of thousands of records, the "*" & [enter something or refer to a form control] & "*" did not work particularly well, where as specifying something specific better utilized indexes.
    --
    Tim

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    use a VBA function to change the ORDER BY clause.
    Sure, you could and it would eliminate at most, 4 lines of code that I wrote. I'm afraid I don't see the point in putting what's needed in a separate procedure instead of just the one that would call it. The code you'd write for this would likely be more than the 4 lines I'm referring to.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    smaier is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2020
    Posts
    43
    Quote Originally Posted by Micron View Post
    Why do you say that? If it is possible to change the rowsource to a query, doesn't it stand to reason that you could change it via sql in code?
    You are completely right. What I meant was, up to that point, the consensus was that I needed to be controlling the sort order through queries, and just tweaking the line of code I posted would not provide the control I was looking for. VBA itself I do not have too much of a problem with. SQL is not my strong suit, however. I was trying to avoid that (or creating multiple queries for each column of the listbox I was wanting to sort).

    Of course you can control the sort order.
    To sort descending, use
    Code:
    Private Sub SortCNCTool_IDButton_Click()
    Me.SeachListBox.RowSource = "SELECT [1-ToolSearchQuery].[CNCTool_ID], [1-ToolSearchQuery].[Description], [1-ToolSearchQuery].[XSection] FROM [1-ToolSearchQuery] ORDER BY [CNCTool_ID] Desc; "
    DoCmd.Requery "SearchListBoxExact"
    End Sub
    Exactly what I was hoping for. Thank you so much, ssanfu!

  12. #12
    smaier is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2020
    Posts
    43
    *Edit: oops, double posted by accident.

    Thanks again, all!

  13. #13
    DittoBird's Avatar
    DittoBird is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2015
    Location
    Newfoundland & Labrador, Canada
    Posts
    59
    Quote Originally Posted by Micron View Post
    Sure, you could and it would eliminate at most, 4 lines of code that I wrote. I'm afraid I don't see the point in putting what's needed in a separate procedure instead of just the one that would call it. The code you'd write for this would likely be more than the 4 lines I'm referring to.
    Well, I wrote that with the querydef mostly for ranman256.

    My approach would be to set up a sub instead of the function I first mentioned, I think, with pretty exactly much what you wrote, except a string argument. The string argument would be the sort field. All you'd have under the individual buttons is a single line calling the sub specifying the order by field name or just a number. He has three items in his SQL statement, so instead of 3 * 7 lines (excluding error handling), you'd have 7 + 3. I wouldn't bother with error handling in the button sub calls.

    If he adds more columns/fields, this way makes it easily expandable.

    Or you can copy and paste to each button sub. YMMV, it's all the same to the user.

    Take care,
    --
    Tim

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

Similar Threads

  1. Replies: 5
    Last Post: 08-27-2019, 08:41 AM
  2. Sort/Fitler listbox or Search Listbox
    By Behedwin in forum Access
    Replies: 5
    Last Post: 11-09-2017, 12:27 PM
  3. How to change report to page 2 programmatically?
    By naeemahmad in forum Reports
    Replies: 23
    Last Post: 12-04-2013, 12:27 AM
  4. Change Sort Order of Combo Box "On-The-Fly"
    By JoeM in forum Programming
    Replies: 6
    Last Post: 09-26-2013, 06:50 PM
  5. How to change query criteria programmatically
    By fekrinejat in forum Programming
    Replies: 2
    Last Post: 02-04-2013, 05:07 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