Record locking
Is there a simple way to check if someone else on the network is editing the current record.
I run an on current routine that I do not want to run if the user moves to a record on a form that is already being edited by another user.
I run an on current routine that I do not want to run if the user moves to a record on a form that is already being edited by another user.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Jeffrey Coachman
Set your record locking like this:
I should add that rs is the recordsetclone, and that you silently can ignore the error if Edit fails:
Dim rs As DAO.Recordset
Or Error Resume Next
Set rs = Me.RecordsetClone
rs.Edit
If rs.EditMode = dbEditInProgress Then
' rs is ready for edit.
' Carry out your edits.
rs.UpDate
Else
' Exit.
End If
/gustav
Dim rs As DAO.Recordset
Or Error Resume Next
Set rs = Me.RecordsetClone
rs.Edit
If rs.EditMode = dbEditInProgress Then
' rs is ready for edit.
' Carry out your edits.
rs.UpDate
Else
' Exit.
End If
/gustav
Attempting to edit the record and trapping the resulting error is the only method. The below code is very similar to gustav's comment, so no points please.
Just shows how to return the user that's locking the record.
Jim.
Function IsLocked(rs As Recordset, UserName As String, MachineName As String)
' Accepts: a recordset and two string variables
' Purpose: determines if the current record in the recordset is locked,
' and if so who has it locked.
' Returns: True if current record is locked (and sets UserName
' and MachineName to the user with the lock). False if the
' record isn't locked.
' From: Building Applications Chapter 12
Dim ErrorString As String
Dim MachineNameStart As Integer
10 IsLocked = False
20 On Error GoTo IsLockedError
30 rs.Edit 'Try to edit the current record in the recordset.
40 rs.MoveNext
50 rs.MovePrevious
60 Exit Function 'No error, so return False.
IsLockedError:
70 If Err = 3260 Then 'Record is locked -- parse error string.
80 ErrorString = Error$
90 UserName = Mid$(ErrorString, 44, InStr(44, ErrorString, "'") - 44)
100 If UserName = "" Then UserName = "(unknown)"
110 MachineNameStart = InStr(43, ErrorString, " on machine ") + 13
120 MachineName = Mid$(ErrorString, MachineNameStart, Len(ErrorString) - MachineNameStart - 1)
130 If MachineName = "" Then MachineName = "(unknown)"
140 IsLocked = True
150 End If
160 Exit Function
End Function
Just shows how to return the user that's locking the record.
Jim.
Function IsLocked(rs As Recordset, UserName As String, MachineName As String)
' Accepts: a recordset and two string variables
' Purpose: determines if the current record in the recordset is locked,
' and if so who has it locked.
' Returns: True if current record is locked (and sets UserName
' and MachineName to the user with the lock). False if the
' record isn't locked.
' From: Building Applications Chapter 12
Dim ErrorString As String
Dim MachineNameStart As Integer
10 IsLocked = False
20 On Error GoTo IsLockedError
30 rs.Edit 'Try to edit the current record in the recordset.
40 rs.MoveNext
50 rs.MovePrevious
60 Exit Function 'No error, so return False.
IsLockedError:
70 If Err = 3260 Then 'Record is locked -- parse error string.
80 ErrorString = Error$
90 UserName = Mid$(ErrorString, 44, InStr(44, ErrorString, "'") - 44)
100 If UserName = "" Then UserName = "(unknown)"
110 MachineNameStart = InStr(43, ErrorString, " on machine ") + 13
120 MachineName = Mid$(ErrorString, MachineNameStart, Len(ErrorString) - MachineNameStart - 1)
130 If MachineName = "" Then MachineName = "(unknown)"
140 IsLocked = True
150 End If
160 Exit Function
End Function
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@PatHartman
Most top experts here are already familiar with what you posted...
;-)
But your post may provide a deeper insight into these settings for other members here searching for the same issue.
;-)
I simply suggested that setting as a way to lock the record, in case that is what they wanted.
JeffCoachman
Most top experts here are already familiar with what you posted...
;-)
But your post may provide a deeper insight into these settings for other members here searching for the same issue.
;-)
I simply suggested that setting as a way to lock the record, in case that is what they wanted.
JeffCoachman
ASKER
Thank you Pat that was indeed clear and helpful
I have managed to get Gustavs code to work but it is locking the entire record set when I only want to lock the current record in the subform.
Can I restrict the record set clone to just the subforms record.
Actually I'm not sure if this is causing the problem. But the whole form should not be locked, should it.
It's a main product form with items in a single items (not continuos) subform
I have managed to get Gustavs code to work but it is locking the entire record set when I only want to lock the current record in the subform.
Can I restrict the record set clone to just the subforms record.
Actually I'm not sure if this is causing the problem. But the whole form should not be locked, should it.
It's a main product form with items in a single items (not continuos) subform
It is probably not locking the recordset but the current page which is a set of records of which the current record is one. A page holds that many records that fill 2 or 4K (can't recall).
Jim Dettman knows all about this.
/gustav
Jim Dettman knows all about this.
/gustav
ASKER
The problem has now vanished. Weird. I am sure it will reappear but I cannot see where the discrepancy is. I think that I have covered the page locking as opposed to record locking by adding a number of text filled fields. (The Alphabet 10 times as a default in each field). This must fill up the 2 or 4K and that works.
Thank you all for your help
Thank you all for your help
Yes, not so elegant, but is known to work.
/gustav
/gustav