Solved

sort unbound multi-column listbox without ORDER BY

Posted on 2008-10-21
7
532 Views
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?

Thanks,
NC
0
Comment
Question by:NO_CARRIER
  • 4
  • 3
7 Comments
 
LVL 65

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.
0
 
LVL 1

Author Comment

by:NO_CARRIER
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?
0
 
LVL 65

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.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 1

Author Comment

by:NO_CARRIER
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
    Do
        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 = ""
    lstBox.Requery
   
    'Add sorted items into list
    For i = 0 To UpperBound
        lstBox.AddItem strValue(i)
    Next i
End Sub
0
 
LVL 1

Author Comment

by:NO_CARRIER
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...
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 150 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.
0
 
LVL 1

Author Comment

by:NO_CARRIER
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.)
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

810 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