Full Version: sort 'value list' listbox
UtterAccess Forums > Microsoft® Access > Access Forms
That are some ways to sort a 'value list' listbox? I saw a post showing the "me.orderby=fieldname" method but I couldn't get that to work. I have two listboxes on a form. lst1 has table/query rowsource and lst2 has 'value list' rowsource. I am using the add.item/remove.item method to move over records. lst1 is sorted when I load the form, I just can't figure out how to sort lst2. I have my db exactly the way I want it except for this sorting problem. I would appreciate any help on this.

Don't know about anyone else, but I find Value Lists (most times anyway, although they do have their uses) to be a bit of a pain.

If I need to move entries from one list box to another, I usually use a temporary table which I use to populate the second list box via a sorted query, before committing any changes to the real table..........

Ocan post you a quick demo if you want to go down that route...........


Take a look at this download.Once you click on the headings of the list box it will sort. I downloaded it from UA on the 23/5/06 at around 4:15pm. But i cant seem to find the link to the details of the post.
List box sorting by clicking on heading
Hope this helps
How do you generate the Value List to populate the list box? ... If you get the values from a table or recordset, then I would sort it before you populate it.
therwise ... here are my suggestions:
Single Column Value List:
I use two methods to sort ONE element string arrays. I very rarely, if every, use it for Value Lists, but you could easily apply the principle. The first method is a UDF I created some time ago, it is named appropriately as "SortStringArray" and you pass it the string array variable name. To get the Value List into an array, use the Split() function ... to turn the array back into a string, use the Join() function.
(Assumptions: listbox named "lstAreas"; Sorting actions intiated by a button named "btnSortList"; a SINGLE column value list)
Private Sub btnSortList_Click()
    Dim strArrValueList() As String
    strArrValueList = Split(Me.lstAreas.RowSource, ";")
    SortStringArray strArrValueList
    [color="green"]'Note: you can also use the HIDDEN/Undocumented "WizHook.SortStringArray" method
    '... but it is UNDOCUMENTED ... so It may go away one day!!! ... if you use the WizHook
    'the SortStringArray strArrValueList would look like this:[/color]
    'WizHook.SortStringArray strArrValueList
    Me.lstAreas.RowSource = Join(strArrValueList, ";")
End Sub

Public Sub SortStringArray(ByRef strArray() As String, Optional blAscending As Boolean = False)
[color="green"]'Sorts a single element string array.  I normally use the hidden/undocumented WizHook.SortStringArray
'method.  However, If a problem arises with WizHook.SortStringArray, or you wish to change
'the sort order then use this procedure.[/color]
    Dim strTemp As String
    Dim lngElement As Long
    Dim x As Long
    [color="green"]'Bubble sort the array[/color]
    If LBound(strArray) <> UBound(strArray) Then
        x = UBound(strArray)
        Do Until x = 0
            For lngElement = LBound(strArray) To x - 1
                If strArray(lngElement) > strArray(lngElement + 1) Then
                    strTemp = strArray(lngElement)
                    strArray(lngElement) = strArray(lngElement + 1)
                    strArray(lngElement + 1) = strTemp
                End If
            Next lngElement
            x = x - 1
    End If
End Sub

If the value list is a 2 or more column value list, then I suggest doing as has already been mentioned ... making a temp table/recordset, then sorting on the appropriate column, then loading the data back into the value list ... I would probably make use of the GetString method of an ADODB recordset.
HTH ...
... Quick note ... I did not implement the DESCENDING sorting at the time of the above post ... I put the optional parameter in there with the INTENT to implement it ... however, no need has arose, so no code to do it!!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.