Edit adjacent records for sorting purposes

[SaviorSix]

I am working on my survey application. I have a tblQuestions which contains survey question data. One field, QuestionOrder, determines the sort/display order of the questions in my datasheet form.

example:
1 This is a question
2 This is another question
3 Yet another question
4 You get the idea

I have 'move up' and 'move down' buttons. If the currently selected record is #3, and I hit 'Move Up', I want the QuestionOrder field to change from 3 to 2, and I want the record that previously had a QuestionOrder of 2 to become 3, and then the form recalcs and the sort order changes, creating the 'illusion' of moving the question 'up' in the order.

Is there a better way to do this than doing a bunch of Recordset.MovePrev and .MoveNexts?
I was thinking perhaps using a couple of different recordset objects to save each record's original QuestionOrder value, then updating the respective recordsets to the new value...

 

[Beetle]

Example code for the "Up" button (reverse some of the logic for the "Down" button).

Private Sub cmdUp_Click()
 
Dim iNew As Integer
Dim iOld As Integer
Dim rs As DAO.Recordset
Dim strSQL As String
 
iOld = Me![QuestionOrder]
iNew = Nz(DLookup("QuestionOrder", "tblQuestions", "QuestionOrder=" & iOld - 1), 0)
 
If iNew = 0 Then
    MsgBox "Already at the first question."
Else
    strSQL = "Select QuestionOrder From tblQuestions " _
           & "Where QuestionOrder In(" & iOld & ", " & iNew & ") " _
           & "Order By QuestionOrder"
    
    Set rs = CurrentDb.OpenRecordset(strSQL)
    With rs
        .MoveFirst
        .Edit
        !QuestionOrder = iOld
        .Update
        .MoveNext
        .Edit
        !QuestionOrder = iNew
        .Update
    End With
    Me.Requery
End If
 
End Sub