Version 1.2 First Published 19 Jan 2025 Difficulty level : Moderate
Section Links
Introduction
Sort text and number value lists
Sort table level value list lookup fields and multi-valued fields
Randomize Value List Sorts
Summary
Downloads
Feedback
Introduction
Return To Top
If you use value lists as row sources in combo boxes or listboxes, these appear in the order the list was created. This can make the lists difficult to navigate.
The same issue also applies to value lists used as lookup fields in tables, including their use as multi-valued fields.
Although the lists can be manually sorted in design view, if the value lists are then edited, any additional items will appear at the end.
The easiest way of avoiding these issues is to store the row source lookup values in a table.
The row source should then be based on a query where the displayed field is sorted in ascending order.
However, using a Table/Query as a row source isn't an option if you want users to be able to edit the row source lists.
To manage this issue, code is needed to sort the value list arrays .
Although this isn't hard to do, an easier solution is to use the SortStringArray function from the very useful but undocumented Wizhook object.
The SortStringArray function will sort text value lists in combos, listboxes and table lookup fields (including MVFs) with just one line of code.
For example, to sort a listbox List0, use the code: SortValueList Me.List0
However, if the SortStringArray function is used on number value lists, the string sorting will result in an 'alphabetical' sort e.g. 1; 11; 2; 23; 3; 35; 4; 40
The attached example app includes both text and number value lists.
Different code is used to correctly sort numerical value lists. Similar code is used to un-sort (randomize) the lists again.
For this example, all lists are randomized when the form is loaded.
Sort text and number value lists
Return To Top
Click the Sort Listbox button in the main form of the example app. The listbox value will be sorted alphabetically and the button caption changes to Randomize Listbox.
| Unsorted Listbox |
Sorted Listbox |
 |
 |
Click the button again to randomly sort the listbox order again.
The button code is as follows:
Private Sub cmdSortListbox_Click()
'string sort value list using Wizhook SortStringArray function
If Me.cmdSortListbox.Caption = "Sort Listbox" Then
SortValueList Me.List0
Me.cmdSortListbox.Caption = "Randomize Listbox"
Else
RandomizeValueList Me.List0
Me.cmdSortListbox.Caption = "Sort Listbox"
End If
End Sub
Randomizing wouldn't normally be needed in a real-world app, so the code could be reduced to:
Private Sub cmdSortListbox_Click()
'string sort value list using Wizhook SortStringArray function
SortValueList Me.List0
End Sub
The SortValueList code is in module modSortValueLists:
Sub SortValueList(ctl As Control)
'string sort of value list using Wizhook SortStringArray
Dim valueList As String
Dim itemArray() As String
Dim sortedRowSource As String
' Get the RowSource of the combo / listbox
valueList = ctl.RowSource
' Split the RowSource into an array
itemArray = Split(valueList, ";")
' Sort the array using WizHook.SortStringArray
WizHook.SortStringArray itemArray
' Reassemble the sorted array back into RowSource format
sortedRowSource = Join(itemArray, ";")
' Update the RowSource of the listbox
ctl.RowSource = sortedRowSource
End Sub
This code can be used with any other value list control. For example, the button code to sort the combobox Combo3 is:
Private Sub cmdSortCombo_Click()
'string sort value list using Wizhook SortStringArray function
If Me.cmdSortCombo.Caption = "Sort Combobox" Then
SortValueList Me.Combo3
Me.cmdSortCombo.Caption = "Randomize Combobox"
Else
RandomizeValueList Me.Combo3
Me.cmdSortCombo.Caption = "Sort Combobox"
End If
End Sub
The simplified version of the button code is just:
Private Sub cmdSortCombo_Click()
'string sort value list using Wizhook SortStringArray function
SortValueList Me.Combo3
End Sub
| Unsorted Combobox |
Sorted Combobox |
 |
 |
However using this code on a numerical value list will result in an 'alphabetical sort' as it is sorting the values as text strings:
| Unsorted Number Value List |
Alphabetical Sort |
 |
 |
The correct numerical sort is shown below:
| Unsorted Number Value List |
Sorted Number Value List |
| |
 |
Sorting the value list numerically requires a different coding approach involving a standard bubble sort:
Sub SortNumberValueList(ctl As Control)
'numerical sort of table lookup field (NOT Wizhook)
Dim i As Integer, j As Integer
Dim temp As Integer
Dim itemList() As String
' Create an array to hold the combo box items
ReDim itemList(ctl.ListCount - 1)
' Fill the array with combo box items
For i = 0 To ctl.ListCount - 1
itemList(i) = ctl.ItemData(i)
Next i
' Sort the array using a simple bubble sort
For i = 0 To UBound(itemList) - 1
For j = i + 1 To UBound(itemList)
If CInt(itemList(i)) > CInt(itemList(j)) Then
temp = itemList(i)
itemList(i) = itemList(j)
itemList(j) = temp
End If
Next j
Next i
' Clear the combo box and repopulate with sorted items
ctl.RowSource = ""
For i = 0 To UBound(itemList)
ctl.AddItem itemList(i)
Next i
ctl.Requery
End Sub
The simplified version of the button code is just:
Private Sub cmdNumberSort_Click()/span>
'used for correctly sorting number value list
SortNumberValueList Me.cboNumberList
End Sub
Sort table level value list lookup fields and multi-valued fields Return To Top
I do NOT recommend the use of table level lookup fields or multi-valued fields. However similar code to that above can be used to sort these.
For further information on each of these features, see Multivalued Fields . . . and why you shouldn't use them and Table Lookup Fields
To sort lookup field value lists, we must apply the sort to the table field value list itself rather than the form control.
The same code based on the SortStringArray function works with both standard and multi-valued string lookup fields.
Sub SortTextFieldValueList(strTable As String, strField As String)
'string sort of table text lookup field using Wizhook
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim valueList As String
Dim itemArray() As String
Dim sortedRowSource As String
' Set the database to current database
Set db = CurrentDb()
'set the specified table & field names
Set tdf = db.TableDefs(strTable)
Set fld = tdf.Fields(strField)
' Get the value list
If fld.Properties("RowSourceType") = "Value List" Then
valueList = fld.Properties("RowSource")
' Split the RowSource into an array
itemArray = Split(valueList, ";")
' Sort the array using WizHook.SortStringArray
WizHook.SortStringArray itemArray
' Reassemble the sorted array back into RowSource format
sortedRowSource = Join(itemArray, ";")
' Update the RowSource of the field
fld.Properties("RowSource") = sortedRowSource
End If
End Sub
The simplified version of the button code is just:
Private Sub cmdSortMVF_Click()
'sort table lookup field value list (MVF)
SortTextFieldValueList "tblLookupFields", "MVF"
End Sub
| Unsorted MVF Value List |
Sorted MVF Value List |
 |
 |
Similarly for the standard text based value list lookup field
Private Sub cmdSortText_Click()
'sort table lookup field value list
SortTextFieldValueList "tblLookupFields", "TLookup"
End Sub
| Unsorted Text Field Value List |
Sorted Text Field Value List |
 |
 |
Once again, we need different sort code for the numerical value list lookup field:
Sub SortNumberFieldValueList(strTable As String, strField As String)
' numerical sort of table lookup field (NOT Wizhook)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim valueList As String
Dim itemArray() As String
Dim sortedRowSource As String
Dim i As Integer, j As Integer
Dim temp As Integer
' Set the database to current database
Set db = CurrentDb()
' Set the table & field values
Set tdf = db.TableDefs(strTable)
Set fld = tdf.Fields(strField)
' Get the value list
If fld.Properties("RowSourceType") = "Value List" Then
valueList = fld.Properties("RowSource")
' Split the RowSource into an array
itemArray = Split(valueList, ";")
' Sort the array using a simple bubble sort
For i = 0 To UBound(itemArray) - 1
For j = i + 1 To UBound(itemArray)
If CInt(itemArray(i)) > CInt(itemArray(j)) Then
temp = itemArray(i)
itemArray(i) = itemArray(j)
itemArray(j) = temp
End If
Next j
Next i
' Reassemble the sorted array back into RowSource format
sortedRowSource = Join(itemArray, ";")
' Update the RowSource of the field
fld.Properties("RowSource") = sortedRowSource
End If
End Sub
The simplified button code is:
Private Sub cmdSortNumber_Click()
'sort table lookup field number value list
SortNumberFieldValueList "tblLookupFields", "NLookup"
End Sub
| Unsorted Number Field Value List |
Sorted Number Field Value List |
 |
 |
Randomize Value List Sorts
Return To Top
Although it is unlikely that many people will want to randomize the sort order of value lists, the code below is what I used to reset all the value lists in the example app.
The same code can be used for both text and number fields as all that matters is that the lists become unsorted.
Sub RandomizeValueList(ctl As Control)
'randomize (un-sort) control text or number value lists
Dim valueList As String
Dim values() As String
Dim i As Integer
Dim temp As String
Dim pos1 As Integer
Dim pos2 As Integer
' Get the RowSource of the combo / listbox
valueList = ctl.RowSource
' Split the RowSource into an array
values = Split(valueList, ";")
'Randomize the array
Randomize 'seed the initial value randomly
For i = LBound(values) To UBound(values)
pos1 = Int((UBound(values) - LBound(values) + 1) * Rnd + LBound(values))
pos2 = Int((UBound(values) - LBound(values) + 1) * Rnd + LBound(values))
temp = values(pos1)
values(pos1) = values(pos2)
values(pos2) = temp
Next i
' Combine the array back into a single string
valueList = Join(values, ";")
' Update the RowSource of the control
ctl.RowSource = valueList
End Sub
The following (very similar code) code can be used to randomize value list sort order in table level lookup fields.
Sub RandomizeLookupFieldValueList(strTable As String, strField As String)
' randomize (un-sort) text or number value lists in table lookup fields
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim i As Integer
Dim temp As String
Dim pos1 As Integer
Dim pos2 As Integer
Dim values() As String
' Set the database to current database
Set db = CurrentDb()
' Set the table/field values
Set tdf = db.TableDefs(strTable)
Set fld = tdf.Fields(strField)
' Randomize the value list
If fld.Properties("RowSourceType") = "Value List" Then
Dim valueList As String
valueList = fld.Properties("RowSource")
' Split the value list by semicolon
values = Split(valueList, ";")
' Randomize the array
Randomize 'seed the initial value randomly
For i = LBound(values) To UBound(values)
pos1 = Int((UBound(values) - LBound(values) + 1) * Rnd + LBound(values))
pos2 = Int((UBound(values) - LBound(values) + 1) * Rnd + LBound(values))
temp = values(pos1)
values(pos1) = values(pos2)
values(pos2) = temp
Next i
' Combine the array back into a single string
valueList = Join(values, ";")
' Update the RowSource of the field
fld.Properties("RowSource") = valueList
End If<
End Sub
Summary
Return To Top
All the above module code can be used with any field lookup value lists or form control value lists.
However, unless you need end users to be able to edit value lists, it is far easier to set the Allow Value List Edits property to No and use a Table/Query for all row sources whether at table level or in form controls.
Download
Return To Top
Click to download the example app with all code used in this article:
SortValueList_v1.2 Approx 0.8 MB ACCDB (zipped)
As with all files downloaded from the internet, you will need to first Unblock downloaded files by removing the Mark of the Web
Then unzip the file and either make it a trusted document or (preferably) save it in a trusted location
Feedback
Return To Top
Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.
Please also consider making a donation towards the costs of maintaining this website. Thank you
Colin Riddington Mendip Data Systems Last Updated 19 Jan 2025