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

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

Answer 1

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...

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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 [e-mail].

Answer 7

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

Answer 8

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 [e-mail].

Answer 9

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

Answer 10

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 [e-mail].