sort unbound multi-column listbox without ORDER BY

Posted on 2008-10-21
Medium Priority
Last Modified: 2013-11-29
I have a multicolumn listbox with 7 columns.  The listbox is a VALUE LIST, therefore not bound to a query.  I would like to have a button, say for example, to sort ascending by the 2nd column, hit it again and it'll sort descending.

Is there a way to do this in Access, perhaps in memory opposed to filling a temporary table, sorting, then repopulating the listbox?

Question by:NO_CARRIER
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 66

Expert Comment

by:Jim Horn
ID: 22766558
No recordset = No sorting.  Sorry dude.
Just slam all your values in a table, then create a query that reads them.

If all this is is a simple list of user-selectable choices, memory is insignificant.

Author Comment

ID: 22766595
Will the temporary tables be stored in the front-end by default, and cleaned up with compact on exit?.. or will I need to keep an eye on the back-end database to compact it regularly?
LVL 66

Expert Comment

by:Jim Horn
ID: 22766610
>Will the temporary tables be stored in the front-end by default
All depends on your preference.  If you want immediate control over it because of updates, put it in the back-end.
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.


Author Comment

ID: 22766649
I found the following solution, which works with only a single column listbox... but having a tough time modifying it for a multicolumn listbox...

Private Sub cmdSort_Click()
    Dim FinishedSwapping As Boolean
    Dim UpperBound As Long
    Dim Temp As String
    Dim strValue() As String
    Dim i As Long
    'Add each value in the list to a temporary array (for sorting)
    For i = 0 To lstBox.ListCount - 1
        ReDim Preserve strValue(i)
        strValue(i) = lstBox.ItemData(i)
    Next i

    UpperBound = UBound(strValue())

    'Sort array into ascending order
        FinishedSwapping = True
        For i = 0 To UpperBound - 1
            'If they are bigger then perform a swap
            If strValue(i) > strValue(i + 1) Then
                Temp = strValue(i + 1)
                strValue(i + 1) = strValue(i)
                strValue(i) = Temp
                FinishedSwapping = False
            End If
        Next i
    Loop While Not (FinishedSwapping)

    'Clear list
    lstBox.RowSource = ""
    'Add sorted items into list
    For i = 0 To UpperBound
        lstBox.AddItem strValue(i)
    Next i
End Sub

Author Comment

ID: 22766664
> All depends on your preference.  If you want immediate control over it because of updates, put it in the back-end.

I'm not worried about updates.  So I would build the table in the FE then I suppose.  I'll experiment with this too...
LVL 66

Accepted Solution

Jim Horn earned 600 total points
ID: 22766684
>I found the following solution, which works with only a single column listbox
In my opinion, this is more work than is necessary.  Just throw them in a table.
In addition to the sorting, the data will be easier to support as you can affect it via SELECT/UPDATE/DELETE/INSERT queries, instead of fishing in form control properties.

Author Comment

ID: 22767060
What is the easiest way of populating a local query with a query from the backend, where the data can be sorted / arranged as required.  I don't want to query the back end every time the user decides to apply an order by clause.

(Note the temporary table/query does not need to be updatable, it's only used to view data.)

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question