How to rearrange ROW_NUMBER() items in SQL using MS Access as a front end?

I have this table in Microsoft SQL Server:

OrderDetailID (PK IDENTTIY)	OrderID (FK tblOrders)
1	1
2	1
3	1
4	2
5	2

Then I use the ROW_NUMBER() function to display a unique item number for each order, like this:

SELECT ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY OrderDetailID) AS ItemNo, OrderDetailID, OrderID FROM tblOrders

The output is:

ItemNo	OrderDetailID (PK IDENTTIY)	OrderID (FK tblOrders)
1	1	1
2	2	1
3	3	1
1	4	2
2	5	2

I want users in MS Access to be able to rearrange the ItemNo. For example, a user wants to move ItemNo 2 within OrderID 1 one place up. Meaning, this item will become ItemNo 1, and the original ItemNo 1 will become ItemNo 2.

How do I achieve this? Is it better to do on the SQL side or the front end side?

Solution

This can be done with the function RowPriority found at my repository at GitHub: VBA.RowNumbers.

Notice the demo folder with a zip containing a working demo in Access.

' Set the priority order of a record relative to the other records of a form.
'
' The table/query bound to the form must have an updatable numeric field for
' storing the priority of the record. Default value of this should be Null.
'
' Requires:
'   A numeric, primary key, typical an AutoNumber field.
'
' Usage:
'   To be called from the AfterUpdate event of the Priority textbox:
'
'       Private Sub Priority_AfterUpdate()
'           RowPriority Me.Priority
'       End Sub
'
'   and after inserting or deleting records:
'
'       Private Sub Form_AfterDelConfirm(Status As Integer)
'           RowPriority Me.Priority
'       End Sub
'
'       Private Sub Form_AfterInsert()
'           RowPriority Me.Priority
'       End Sub
'
'   Optionally, if the control holding the primary key is not named Id:
'
'       Private Sub Priority_AfterUpdate()
'           RowPriority Me.Priority, NameOfPrimaryKeyControl
'       End Sub
'
'       Private Sub Form_AfterDelConfirm(Status As Integer)
'           RowPriority Me.Priority, NameOfPrimaryKeyControl
'       End Sub
'
'       Private Sub Form_AfterInsert()
'           RowPriority Me.Priority, NameOfPrimaryKeyControl
'       End Sub
'
' 2022-03-12. Gustav Brock, Cactus Data ApS, CPH.
'
Public Sub RowPriority( _
    ByRef TextBox As Access.TextBox, _
    Optional ByVal IdControlName As String = "Id")
    
    ' Error codes.
    ' This action is not supported in transactions.
    Const NotSupported      As Long = 3246

    Dim Form                As Access.Form
    Dim Records             As DAO.Recordset
    
    Dim RecordId            As Long
    Dim NewPriority         As Long
    Dim PriorityFix         As Long
    Dim FieldName           As String
    Dim IdFieldName         As String
    
    Dim Prompt              As String
    Dim Buttons             As VbMsgBoxStyle
    Dim Title               As String
    
    On Error GoTo Err_RowPriority
    
    Set Form = TextBox.Parent
    
    If Form.NewRecord Then
        ' Will happen if the last record of the form is deleted.
        Exit Sub
    Else
        ' Save record.
        Form.Dirty = False
    End If
    
    ' Priority control can have any Name.
    FieldName = TextBox.ControlSource
    ' Id (primary key) control can have any name.
    IdFieldName = Form.Controls(IdControlName).ControlSource
    
    ' Prepare form.
    DoCmd.Hourglass True
    Form.Repaint
    Form.Painting = False
    
    ' Current Id and priority.
    RecordId = Form.Controls(IdControlName).Value
    PriorityFix = Nz(TextBox.Value, 0)
    If PriorityFix <= 0 Then
        PriorityFix = 1
        TextBox.Value = PriorityFix
        Form.Dirty = False
    End If
    
    ' Disable a filter.
    ' If a filter is applied, only the filtered records
    ' will be reordered, and duplicates might be created.
    Form.FilterOn = False
    
    ' Rebuild priority list.
    Set Records = Form.RecordsetClone
    Records.MoveFirst
    While Not Records.EOF
        If Records.Fields(IdFieldName).Value <> RecordId Then
            NewPriority = NewPriority + 1
            If NewPriority = PriorityFix Then
                ' Move this record to next lower priority.
                NewPriority = NewPriority + 1
            End If
            If Nz(Records.Fields(FieldName).Value, 0) = NewPriority Then
                ' Priority hasn't changed for this record.
            Else
                ' Assign new priority.
                Records.Edit
                    Records.Fields(FieldName).Value = NewPriority
                Records.Update
            End If
        End If
        Records.MoveNext
    Wend
    
    ' Set default value for a new record.
    TextBox.DefaultValue = NewPriority + 1
    
    ' Reorder form and relocate record position.
    ' Will fail if more than one record is pasted in.
    Form.Requery
    Set Records = Form.RecordsetClone
    Records.FindFirst "[" & IdFieldName & "] = " & RecordId & ""
    Form.Bookmark = Records.Bookmark
   
PreExit_RowPriority:
    ' Enable a filter.
    Form.FilterOn = True
    ' Present form.
    Form.Painting = True
    DoCmd.Hourglass False
    
    Set Records = Nothing
    Set Form = Nothing
    
Exit_RowPriority:
    Exit Sub
    
Err_RowPriority:
    Select Case Err.Number
        Case NotSupported
            ' Will happen if more than one record is pasted in.
            Resume PreExit_RowPriority
        Case Else
            ' Unexpected error.
            Prompt = "Error " & Err.Number & ": " & Err.Description
            Buttons = vbCritical + vbOKOnly
            Title = Form.Name
            MsgBox Prompt, Buttons, Title
            
            ' Restore form.
            Form.Painting = True
            DoCmd.Hourglass False
            Resume Exit_RowPriority
    End Select
    
End Sub

Full documentation in my article at Experts Exchange:

Sequential Rows in Microsoft Access