sorting a listbox in VBA

Question

I have a listbox with values. Now I need to sort that listbox, BUT NOT BY ALPHABETICALLY.

You see the values in a listbox are from a table.

------------------------
| name | order |  size |
========================
 value1   4
 value2   3
 value3   1 
 value4   2

I hope I made myself clear. So the list box has the items "value1, value2, value3, value4". I want it to sort by the order declared in the table. The list box has nothing to do with the table and is not "data bound" to it. The values come from somewhere else.

Tech used: VBA, Access 2007

Answer 1

It sounds like the Row Source Type for your list box is "Value List". Since the values come from a table, change Row Source Type to "Table/Query", then use a query for Row Source:

SELECT [name], [order]
FROM YourTable
ORDER BY [order];

You don't have to display [order] in the list box ... set its column width to zero.

Your list box control does not have to be bound to a data source field for this approach to work. If the list box is unbound, the user selection will not be stored, but the list box will still allow users to make selections from the list box.

Notice I enclosed [name] and [order] in square brackets because both are reserved words. See Problem names and reserved words in Access