Priority List

[mschirle]

I have a daily work list. I want to be able to rearrange the order of the
items by in a list. I want to select an item on the list and with up and down
buttons I want to be able to move the item on the list up or down. I then
want to be able to print the list in the new order.

I sure someone has done this already. I'm really looking for a refresher on
this process.

Thanks

 

[Graham Mandeno]

Hi mschirle

You will need a numeric field (say "Priority") and also (if you don't have
it already) a primary key to uniquely identify your records. This could be
an AutoNumber field.

Now, your "UP" button needs to:
1. Find the previous record
2. Save the Priority value from this record in a temporary variable
3. Change its Priority value to that of the current record
4. Set the Priority value of the current record to the saved value
5. Save both records
6. Save the ID field of the current record
7. Requery the form
8. Find the current record again by ID and reposition to it.

The code is exactly the same for the "DOWN" button, except that step 1 moves
to the *next* record.

Your code might look something like this:

Private Function MovePriority( fDown as Boolean )
Dim lTemp as Long
With Me.RecordsetClone
.Bookmark = Me.Bookmark
If fDown then
.MoveNext
Else
.MovePrevious
End If
If .BOF or .EOF then
MsgBox "Can't move past the end of the list"
Exit Function
End If
lTemp = !Priority
.Edit
!Priority = Me!Priority
.Update
End If
Me!Priority = lTemp
Me.Dirty = False
lTemp = Me!TaskID
Me.Requery
With Me.RecordsetClone
.FindFirst "RecordID=" & lTemp
Me.Bookmark = .Bookmark
End With
End Function

[WARNING: This code is typed on the fly and untested!]

Now, set the OnClick properties of your UP and DOWN buttons:

UP: =MovePriority(False)
DOWN: =MovePriority(True)