Access Cookbook - Ken Getz [221]
' form's current record
Set rst = frm.RecordsetClone
rst.Bookmark = frm.Bookmark
' If the current record is locked, then the next
' statement should produce an error that we will trap
rst.Edit
ExitHere:
' Display either the default message or one specifying
' the user and machine who has locked the current record.
MsgBox strMsg, , "Locking Status"
Exit Function
HandleErr:
' Pass the error to acbGetUserAndMachine which will attempt
' to parse out the user and machine from the error message
If Err.Number = 3188 Then
' Locked on this machine.
strMsg = "Some other part of this application " _
& "on this machine has locked this record."
Else
blnMUError = acbGetUserAndMachine(Err.Description, _
strUser, strMachine)
' If the return value is True, then acbGetUserAndMachine
' was able to return the user and machine name of the user.
' Otherwise, assume the record was not locked.
If blnMUError Then
strMsg = "Record is locked by user: " & strUser & _
vbCrLf & "on machine: " & strMachine & "."
End If
End If
Resume ExitHere
End Function
This function accepts a single parameter: a pointer to a form. Using this form object, acbWhoHasLockedRecord clones the form's recordset, synchronizes the clone's current record with that of the form, and attempts to lock the current record. One of two things can happen as a result of this locking attempt:
The attempt will succeed, meaning that the record was not locked by another user.
The attempt will fail with an error message stating who has the record locked.
By parsing this error message, we can determine who has locked the record. Parsing the error message is accomplished by the acbGetUserAndMachine function, which is shown here:
Public Function acbGetUserAndMachine(ByVal strErrorMsg As String, _
ByRef strUser As String, ByRef strMachine As String) As Boolean
' Parse out the passed error message, returning
' -True and the user and machine name
' if the record is locked, or
' -False if the record is not locked.
Dim intUserPos As Integer
Dim intMachinePos As Integer
Const USER_STRING As String = " locked by user "
Const MACHINE_STRING As String = " on machine "
acbGetUserAndMachine = False
On Error Resume Next
intUserPos = InStr(strErrorMsg, USER_STRING)
If intUserPos > 0 Then
intMachinePos = InStr(strErrorMsg, MACHINE_STRING)
If intMachinePos > 0 Then
strUser = Mid$(strErrorMsg, _
intUserPos + Len(USER_STRING), _
intMachinePos - (intUserPos + Len(USER_STRING) - 1))
strMachine = Mid$(strErrorMsg, _
intMachinePos + Len(MACHINE_STRING), _
(Len(strErrorMsg) - intMachinePos - _
Len(MACHINE_STRING)))
End If
acbGetUserAndMachine = True
End If
End Function
This function accepts as its argument the Description property of the Err object, which was generated by acbWhoHasLockedRecord. If it can successfully parse the error message and determine at least the username (and hopefully the machine name), it returns a True value to the calling routine with the names of the user and machine as the second and third parameters of the function call. There's nothing magic about this function—it uses the InStr function to locate certain landmarks in the passed error message.
Record-level locking makes the use of pessimistic locking much more practical than it has been in the past. However, you still run the danger of allowing a user to monopolize the record being edited. This solution shows how you can identify the guilty user, but it doesn't really solve the problem. The next solution enables you to prevent users from tying up records for longer than a set period of time.
10.10. Set a Maximum Locking Interval for a Record
Problem
You've employed pessimistic locking on your application's forms to prevent two users from making changes to the same record at the same time. Sometimes, a user will lock a record for an excessive period of time; for example, he might start to edit a record and then get a long phone call or leave for lunch without saving or canceling his edits. Is there any way to limit