Solved

sort unbound multi-column listbox without ORDER BY

Posted on 2008-10-21
7
553 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
[X]
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
7 Comments
 
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.
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 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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 66

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

729 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