Don't know how you are populating the listbox, but if it is with a field
list, I don't have a clue.
However, If you are using a table that contains the field names as the
source for your list, then the technique I use is as follows. The code
below is patched together from memory. I've got code similiar to this in my
code library, and have not had to think this thru in a while, but it should
give you an idea of how I handle the problem.
1. Add a Sort_Order field to the table.
2. Put in a sort order that you think is most likely to work properly.
3. If you don't already have in ID field in the table, add one of them
also.
At this point, your SQL for the listbox should look like:
SELECT ID, FieldName, Sort_Order
FROM yourTable
ORDER BY Sort_Order
4. Add two command buttons (Move Up, Move Dn) next to your list box.
5. In the Click event of the buttons add code that calls ListMove(-1), and
ListMove(1)
6. Add the following subroutine to your code module
Public Sub ListMove(Increment as integer)
'Increment should be a value of -1 to move the item
'up the list or 1 to move it down the list
Dim strSQL as string
Dim intSortOrder as Integer
Dim intID as long, intOtherID as long
intSortOrder = me.listName.column(2)
intId = Me.listName
intOtherID = Me.listname.column(0, Me.listName.listindex + Increment)
strSQL = "UPDATE YourTable " _
& "SET Sort_Order = Sort_Order + " & Increment & " " _
& "WHERE ID = " & intID
Currentdb.execute strSQL
strSQL = "UPDATE YourTable " _
& "SET Sort_Order = Sort_Order - " & Increment & " " _
& "WHERE ID = " & intOtherID
Currentdb.execute strSQL
me.listName.Requery
end sub
7. You should also probably add some code that tests to see whether the
selected item in the listbox is the first or last entry, and disable the
appropriate button if at the top or bottom of the list.
HTH
Dale
