none
MS Access Form Is Record Editable RRS feed

  • Question

  • How to determine in MS Access 2013 via VBA that the current form recordset is updateable or editable when locked for editing by another user.

    If the record is locked for editing then I would like to pop the message that record is locked by another user.  Also if possible the PC name and user name to show.

    Thursday, May 25, 2017 7:10 PM

All replies

  • Hi Indukhan,

    you can try to use "optimistic" or "pessimistic" locking provided by the MS Access.

    you just need to set Form property in design view. no need to do any code in VBA.

    or

    Reference:

    RecordLocks Property

    if you want to perform same using VBA code then you can refer code below.

    Sub LockEditsX()
    
       Dim dbsNorthwind As Database
       Dim rstCustomers As Recordset
       Dim strOldName As String
    
       Set dbsNorthwind = OpenDatabase("Northwind.mdb")
       Set rstCustomers = _
          dbsNorthwind.OpenRecordset("Customers", _
          dbOpenDynaset)
    
       With rstCustomers
          ' Store original data.
          strOldName = !CompanyName
    
          If MsgBox("Pessimistic locking demonstration...", _
                vbOKCancel) = vbOK Then
    
             ' Attempt to modify data with pessimistic locking 
             ' in effect.
             If PessimisticLock(rstCustomers, !CompanyName, _
                   "Acme Foods") Then
                MsgBox "Record successfully edited."
    
                ' Restore original data...
                .Edit
                !CompanyName = strOldName
                .Update
             End If
    
          End If
    
          If MsgBox("Optimistic locking demonstration...", _
                vbOKCancel) = vbOK Then
    
             ' Attempt to modify data with optimistic locking 
             ' in effect.
             If OptimisticLock(rstCustomers, !CompanyName, _
                   "Acme Foods") Then
                MsgBox "Record successfully edited."
    
                ' Restore original data...
                .Edit
                !CompanyName = strOldName
                .Update
             End If
    
          End If
    
          .Close
       End With
    
       dbsNorthwind.Close
    
    End Sub
    
    Function PessimisticLock(rstTemp As Recordset, _
       fldTemp As Field, strNew As String) As Boolean
    
       dim ErrLoop as Error
    
       PessimisticLock = True
    
       With rstTemp
          .LockEdits = True
    
          ' When you set LockEdits to True, you trap for errors 
          ' when you call the Edit method.
          On Error GoTo Err_Lock
          .Edit
          On Error GoTo 0
    
          ' If the Edit is still in progress, then no errors
          ' were triggered; you may modify the data.
          If .EditMode = dbEditInProgress Then
             fldTemp = strNew
             .Update
             .Bookmark = .LastModified
          Else
             ' Retrieve current record to see changes made by
             ' other user.
             .Move 0
          End If
    
       End With
       
       Exit Function
       
    Err_Lock:
    
       If DBEngine.Errors.Count > 0 Then
          ' Enumerate the Errors collection.
          For Each errLoop In DBEngine.Errors
             MsgBox "Error number: " & errLoop.Number & _
                vbCr & errLoop.Description
          Next errLoop
          PessimisticLock = False
       End If
       
       Resume Next
    
    End Function
    
    Function OptimisticLock(rstTemp As Recordset, _
       fldTemp As Field, strNew As String) As Boolean
    
       dim ErrLoop as Error
    
       OptimisticLock = True
    
       With rstTemp
          .LockEdits = False
          .Edit
          fldTemp = strNew
    
          ' When you set LockEdits to False, you trap for errors 
          ' when you call the Update method.
          On Error GoTo Err_Lock
          .Update
          On Error GoTo 0
    
          ' If there is no Edit in progress, then no errors were
          ' triggered; you may modify the data.
          If .EditMode = dbEditNone Then
             ' Move current record pointer to the most recently
             ' modified record.
             .Bookmark = .LastModified
          Else
             .CancelUpdate
             ' Retrieve current record to see changes made by
             ' other user.
             .Move 0
          End If
    
       End With
       
       Exit Function
       
    Err_Lock:
    
       If DBEngine.Errors.Count > 0 Then
          ' Enumerate the Errors collection.
          For Each errLoop In DBEngine.Errors
             MsgBox "Error number: " & errLoop.Number & _
                vbCr & errLoop.Description
          Next errLoop
          OptimisticLock = False
       End If
       
       Resume Next
    
    End Function
    
    

    This example demonstrates pessimistic locking by setting the LockEdits property to True, and then demonstrates optimistic locking by setting the LockEdits property to False. It also demonstrates what kind of error handling is required in a multiuser database environment in order to modify a field. The PessimisticLock and OptimisticLock functions are required for this procedure to run.

    Reference:

    Recordset.LockEdits Property

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 26, 2017 1:56 AM
    Moderator