Hi -
We both got caught making the same asuumption, i.e. that you always moved records "up" in the list. That works, but not correctly. When you move a record "up", changes are required only in the range between where it WAS, and where it is NOW; my code changes everything below where it is NOW, and that is incorrect.
So, the code has to be revised to deal with both possibilities (I haven't tested this!):
Code:
Private Sub ExerciseID_AfterUpdate()
Dim SQL as String, OldPos as Integer, NewPos as integer
Dim db As Database
Set db = CurrentDb()
OldPos = me!ExercisePos.OldValue ' Original value before you changed it
NewPos = me!ExercisePos ' Value you changed it TO
If NewPos < OldPos then
'
' Record was Moved UP
'
SQL = "Update Exercises set ExercisePos = ExercisePos + 1 where (ExercisePos Between " & _
NewPos & " AND " & OldPos & ") and ExerciseID <> " & me!ExerciseID
else
'
' Record was moved down
'
SQL = "Update Exercises set ExercisePos = ExercisePos - 1 where (ExercisePos Between " & _
OldPos & " AND " & NewPos & ") and ExerciseID <> " & me!ExerciseID
Endif
db.execute SQL, dbfailonerror
me.requery
Me.Requery
Set db = Nothing
End Sub
I think this is best put in the After Update event of the ExercisePos control, to be sure you get the previous value properly.
Let us know how this works out.
John