Jane,
Here's one way to do it. I'm sure there are other ways, some of which will
be easier and more efficient.
Start with a table to hold your option and its rank. Rank should be single
or decimal data type.
Put a list box on your form bound to the table. Your rowsource would be
something like "SELECT Option from tblOptions ORDER BY Rank".
Put two command buttons next to the list box, one to move an option up, the
other to move it down. When the user clicks the button, open a recordset,
move to the selected item in the listbox, and increase or decrease it's
rank. In the code below, I am decreasing the rank by 1.5. This would
effectively move the option upward in preference. You have to
increase/decrease the rank by enough to make the option lower/higher than
the next value.
Dim db as database
Dim rs as Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM MyTable", dbOpenDynaset)
rs.FindFirst "Option = '" & Me!MyListBox & "'" 'I'm assuming option
is text
rs.Edit
rs!Rank = rs!Rank - 1.5
rs.Update
rs.Close
The next step will be to resequence your options so they are again ranked 1
to 5. This will allow the user to change the rankings as many times as
he/she needs to.
Dim x as integer
Set rs = db.OpenRecordset("SELECT * FROM MyTable ORDER BY Rank",
dbOpenDynaset)
Do While Not rs.EOF
x = x + 1
rs.Edit
rs!Rank = x
rs.Update
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db = Nothing
Finally, requery the listbox to redisplay the options in the correct order.
There are probably better ways to do this. You could use an array rather
than a table to hold your data. You might be able to manipulate the
ListIndex property of the list box to move your options around. You can
even create a function that acts as the RowSourceType of the list box which
would allow you to manipulate the items.
Hope this helps.
