none
Pessimistic Record Locking Notification to Other Users That Record Is Unavailable for Editing RRS feed

  • Question

  • I have record locking set to Edited Records and would like other users to be notified that a record is being edited by a user. 

    Is there a way to do this?

    Thank you.


    Nath

    Tuesday, June 13, 2017 8:15 PM

Answers

  • Hi NaPazz,

    below is an example of Recordset.

    Sub demo()
    Dim strSQL1 As String
    Dim dbs1 As Database
    Dim rst1 As Recordset
    Dim tmpStr As String
    Set dbs = CurrentDb
    tmpStr = "Company | Last Name | "
    tmpStr = tmpStr& "First Name | "
    tmpStr = tmpStr& "Job Title | "
    tmpStr = tmpStr& "Business Phone"
    Debug.PrinttmpStr
    strSQL1 = "SELECT Customers.Company, Customers.[Last Name], "
    strSQL1 = strSQL1& "Customers.[First Name], "
    strSQL1 = strSQL1& "Customers.[Job Title], Customers.[Business Phone]"
    strSQL1 = strSQL1& "FROM Customers;"
    Set rst1 = dbs1.OpenRecordset(strSQL)
    rst1.MoveLast
    rst1.MoveFirst
    Do While Not rst1.EOF
    tmpStr = rst1.Fields(0).Value
    tmpStr = tmpStr& " | " & rst1.Fields(1).Value
    tmpStr = tmpStr& " | " & rst1.Fields(2).Value
    tmpStr = tmpStr& " | " & rst1.Fields(3).Value
    tmpStr = tmpStr& " | " & rst1.Fields(4).Value
    Debug.PrinttmpStr
    Rst1.MoveNext
    Loop
    rst1.Close
    dbs1.Close

    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.

    • Marked as answer by NaPazz Thursday, September 14, 2017 8:18 AM
    Thursday, September 14, 2017 6:28 AM
    Moderator

All replies

  • Hi Nath,

    On possible way is to add an "edit" flag to the record and set it once the form is dirty. If it's already set, then notify the user the record is locked by another user and Undo the change.

    Just a thought...

    Tuesday, June 13, 2017 8:31 PM
  • The usual means of doing this is to ensure that the record selector is displayed in the form.  When a record is locked its icon changes from the usual pencil to a barred circle.

    Ken Sheridan, Stafford, England

    Wednesday, June 14, 2017 9:16 PM
  • As pointed out by others here, if you display the record selector, then it WILL display a lock icon when someone else is editing the record. It looks like this:

    However, keep in mind that the lock icon usually does NOT appear until you actually start/try editing the record. So this is a code free way to achieve this goal. Not that visible of a indication, but once users are informed about this, they in general will see and realize the record is in use by someone else.

    I should also point out that other users simply viewing the record do not cause locks - the lock only occurs when one of the users starts typing and "dirties" the record.

    Regards,

    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada


    Thursday, June 15, 2017 1:11 AM
  • Thanks Albert.

    I have created my own set of record selector buttons that look far better than the default. They were too small. So I have turned them off.

    I would also like an improvement on the barred circle in your screenshot, because I would like to present the other user with an indication that the record is being edited when they view a record and before they start to edit it too.



    Nath

    Thursday, June 15, 2017 8:25 AM
  • Hi Ken,

    I have created my own set of record selector buttons that look far better than the default. They were too small. So I have turned them off.

    I would also like an improvement on the barred circle in your screenshot, because I would like to present the other user with an indication that the record is being edited when they view a record and before they start to edit it too.


    Nath

    Thursday, June 15, 2017 8:26 AM
  • Hi NaPazz,

    you can try to check code below may help you.

    Function IsLocked(rs As Recordset, UserName As String, MachineName As String)
    
            Dim ErrorString As String
            Dim MachineNameStart As Integer
    
          IsLocked = False
         On Error GoTo IsLockedError
          rs.Edit                       'Try to edit the current record in the recordset.
          rs.MoveNext
          rs.MovePrevious
          Exit Function                 'No error, so return False.
    
    IsLockedError:
         If Err = 3260 Then            'Record is locked -- parse error string.
            ErrorString = Error$
            UserName = Mid$(ErrorString, 44, InStr(44, ErrorString, "'") - 44)
           If UserName = "" Then UserName = "(unknown)"
           MachineNameStart = InStr(43, ErrorString, " on machine ") + 13
           MachineName = Mid$(ErrorString, MachineNameStart, Len(ErrorString) - MachineNameStart - 1)
           If MachineName = "" Then MachineName = "(unknown)"
           IsLocked = True
         End If
         Exit Function
    End Function
    

    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.

    Wednesday, June 28, 2017 8:24 AM
    Moderator
  • Thanks Deepak,

    How would I call this function and get it to return the username of the person that has the record locked?

    Thanks

    Nath


    Nath

    Tuesday, September 12, 2017 1:29 PM
  • Hi NaPazz,

    you need to pass 3 arguments.

    first you need to set record set and assign the record in it you want to check.

    then pass username and machine name and it will check that the record is locked from which machine and by which user.

    you need to call this function in your sub and need to pass these parameters.

    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.

    Wednesday, September 13, 2017 5:32 AM
    Moderator
  • Hi Deepak,

    Thanks for this. I'm still unsure how to set record set and assign the record in it I want to check.

    Do you have an example, please?

    Nath


    Nath

    Wednesday, September 13, 2017 9:47 AM
  • Hi NaPazz,

    below is an example of Recordset.

    Sub demo()
    Dim strSQL1 As String
    Dim dbs1 As Database
    Dim rst1 As Recordset
    Dim tmpStr As String
    Set dbs = CurrentDb
    tmpStr = "Company | Last Name | "
    tmpStr = tmpStr& "First Name | "
    tmpStr = tmpStr& "Job Title | "
    tmpStr = tmpStr& "Business Phone"
    Debug.PrinttmpStr
    strSQL1 = "SELECT Customers.Company, Customers.[Last Name], "
    strSQL1 = strSQL1& "Customers.[First Name], "
    strSQL1 = strSQL1& "Customers.[Job Title], Customers.[Business Phone]"
    strSQL1 = strSQL1& "FROM Customers;"
    Set rst1 = dbs1.OpenRecordset(strSQL)
    rst1.MoveLast
    rst1.MoveFirst
    Do While Not rst1.EOF
    tmpStr = rst1.Fields(0).Value
    tmpStr = tmpStr& " | " & rst1.Fields(1).Value
    tmpStr = tmpStr& " | " & rst1.Fields(2).Value
    tmpStr = tmpStr& " | " & rst1.Fields(3).Value
    tmpStr = tmpStr& " | " & rst1.Fields(4).Value
    Debug.PrinttmpStr
    Rst1.MoveNext
    Loop
    rst1.Close
    dbs1.Close

    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.

    • Marked as answer by NaPazz Thursday, September 14, 2017 8:18 AM
    Thursday, September 14, 2017 6:28 AM
    Moderator