How to deal with locking system in MS access safely in point of sales environment (1 Viewer)

[nectorch]

In a point of sales environment, you will find that when clients are purchasing some products maybe let say thirty lines now when the total amount to pay is shown to them, they may want to remove some lines not in the correct order but randomly. Doing that mean the line sequence will be distorted and so this causes Json to reject such kindly of lines sequencing.

So, to solve this problem I have put up the code below. It seems to be working ok

Auditing required

Just in case I missed something kindly check and advise. Here I'm also storing the line sequence in the child table which also benefit other reports.

Public Function RecalculateLineNumbers_RunSQL(p_ItemSoldID As Long)
    Dim db As DAO.Database
    Dim rsKeys As DAO.Recordset ' This RS only reads primary keys
    Dim strSQLFind As String
    Dim strSQLUpdate As String
    Dim i As Integer
    On Error GoTo ErrorHandler

    Set db = CurrentDb
    i = 1

    ' 1. Select the records just to iterate through their IDs (Use dbOpenSnapshot/read-only)
    strSQLFind = "SELECT [POSID] FROM [tblPosLineDetails] " & _
                 "WHERE [ItemSoldID] = " & p_ItemSoldID & " ORDER BY [POSID]"

    Set rsKeys = db.OpenRecordset(strSQLFind, dbOpenSnapshot) ' Snapshot is read-only

    If Not rsKeys.EOF Then
        rsKeys.MoveFirst
        Do Until rsKeys.EOF
            ' 2. Execute an immediate update query for the specific POSID using db.Execute
            strSQLUpdate = "UPDATE [tblPosLineDetails] SET [ItemesID] = " & i & _
                           " WHERE [POSID] = " & rsKeys![POSID]
            
            ' db.Execute runs the action query immediately without using the RS lock
            db.Execute strSQLUpdate, dbFailOnError
            
            i = i + 1
            rsKeys.MoveNext
        Loop
    End If

    rsKeys.Close
    Set rsKeys = Nothing
    Set db = Nothing
    Exit Function

ErrorHandler:
    MsgBox "Error " & Err.Number & " during SQL Update loop: " & Err.Description, vbCritical
    If Not rsKeys Is Nothing Then rsKeys.Close
    Set rsKeys = Nothing
    Set db = Nothing
End Function


Child form VBA required and also calling the function above

Private Sub Form_AfterDelConfirm(Status As Integer)
    If Status = acDeleteOK Then
        If Me.Dirty Then Me.Dirty = False
        If Me.Parent.Dirty Then Me.Parent.Dirty = False
        
        ' Call the new SQL function
        Call RecalculateLineNumbers_RunSQL(Me.Parent!ItemSoldID)
        
        Me.Requery
    End If
End Sub