Swapping data between records

[gaidheal]

I am working with a main form and a subform that is set to continuous. The underlying table for the subform contains an integer field that is populated when a record is created and is used to sort the records in the subform. So the first record will have a 1 in the field, the next one a 2, and so on. What I want to be able to do is to swap values between records so I can change the order in which the records are listed. I have created buttons called "Move Up", "Move Down", "Move First" and "Move Last".

So in a group of records I might have data like this:
Name -------Date--------- Address------ SortNum
Fred ------10-6-08 -----123 Dutch Elm -----1
Ginger -----9-23-06 ----543 Maple ---------2
Barney -----2-2-07 -----1 Rubble Plaza -----3
Betty ------6-1-08 ------234 Oak ----------4

If I want to change the order so that Ginger lists before Fred, I need to change the sort numbers.

Name -------Date--------- Address------ SortNum
Fred ------10-6-08 -----123 Dutch Elm -----2
Ginger -----9-23-06 ----543 Maple ---------1
Barney -----2-2-07 -----1 Rubble Plaza -----3
Betty ------6-1-08 ------234 Oak ----------4

Is there a reasonably straightforward method to swap those values between the two records?

Thanks in advance for your help.

 

[gemma-the-husky]

most solutions for stuff like this use a real number to sort

so having records 1,2,3,4

instead of trying to get 2,1,3,4

sort to 2.5, 2, 3, 4 - which reduces the number of writes needed, to achieve the same thing - not my idea, but theres some examples here - not sure exactyl where