Taking control of a record

[Mark Reed]

Hi All,
I have created a multi-user application at work which is working
perfectly apart from a small problem which I believe to more a of a user
issue (maybe some will set me straight on that one). I have set the 'Default
Record Locking' option to 'Edited Record' in the hope that if someone tries
to edit a record someone has open, they would not be able to do so.

After a little testing, I found this not to be the case. Instead, if 2 users
are editing a record at the same time and one of them moves to another
record, the user moving to a new record is presented with a 'Write Conflict'
message giving them 3 options of:
* Save Record
* Copy to clipboard
* Drop changes

I wish to eliminate this happening by making the user "Take Control" of the
record before they can begin to edit it.

I have started this process by adding a text field to the underlying table
called 'LockedBy'. I have also set the AllowEdits option of the form to
False.

To edit the record, the user must first click a command button which checks
if the LockedBy field is Null. If it is, it updates the field with their
UserID (I have a custom login form combined with Access built in security)
and sets the AllowEdits option to True.

Now, should another user try to take control of the record, a check is
performed to see if the field is null (combined with a check to see if the
LockedBy field matches the current user), if it isn't, they receive a
message box stating that they cannot edit the record until xxxxxxx releases
control of it.

This works perfectly except for one problem. Once the user that has control
of the record is finished with it, I need to make sure they release control.
This is what I can't figure out how to do.

I am not a programmer and have only recently started to dabble. Below is the
code I am using. If anyone has any better suggestions on how to achieve the
same result, I'd be very grateful.

TIA,

Mark

Private Sub cmd_TakeControl_Click()

'If the record is locked by someone other than current user,
If Me.Locked.Value = True And Me.LockedBY.Value <>
Displayname(Forms!frm_userlogin!txt_UserID) Then
MsgBox "This record is currently locked by " & Me.LockedBY.Value &
vbCrLf & _
"You cannot edit this record until they release control of it",
vbInformation, "Record Locked"
Me.cmd_TakeControl.Caption = "Take Control"
Me.AllowEdits = False

'If the record is lockedby the current user, determine the command caption
ElseIf Me.Locked.Value = True And Me.LockedBY.Value =
Displayname(Forms!frm_userlogin!txt_UserID) Then
'the current user has the record locked, determine if they clicked to
release or take control
If Me.cmd_TakeControl.Caption = "Take Control" Then
Me.AllowEdits = True
Me.Locked = True
Me.LockedBY.Value = Displayname(Forms!frm_userlogin!txt_UserID)
Me.cmd_TakeControl.Caption = "Release Control"
Else
Me.AllowEdits = False
Me.Locked = False
Me.LockedBY.Value = Null
Me.cmd_TakeControl.Caption = "Take Control"
End If

Else 'No-one has control of the record

Me.AllowEdits = True
Me.Locked = True
Me.LockedBY.Value = Displayname(Forms!frm_userlogin!txt_UserID)
If Me.Dirty = True Then
Me.Dirty = False
End If

Me.cmd_TakeControl.Caption = "Release Control"
End If
End Sub

[Rick Wannall]

Good news and bad news, I think.

Good: Use the form's afterupdate event to clear whatever you set up in the
takecontrol code.

Bad: Your problem may be due to the fact that in Access (actually in the Jet
Engein) record locking is not exactly record locking. Up through Access
2000 what is called "record locking" was actuall "page locking". All the
records on a single page were locked, rather than being able to truly lock a
single record, as you can in SQL Server/Oracle/etc. I don't find any
information that says this has changed in later editions.

---------------------------------------------------------------------------------------------

Our solution was to move to unbound forms, which meant using a function to
create a recordset, load the data into the unbound controls on a form, then
close the recordset. A Save button fires the inverse function to write data
out of the controls back into a writeable recordset created only for the
instant it takes to write the data.

The whole library of routines was less than 400 lines of code, including
logging changes to our own table of changes to keep track of who changed
what field from what to what when.

[Mark Reed]

Hi again,
I thought I would post the solution I have come up with which may be
ugly but it works.....

I created an unbound text box and check box on the form footer which are
both set to Visible = False.
When the user takes control of a record, the check box value is updated to
True and the text box value is updated to the records ID.
Using the OnCurrent event of the form, I have a check to see if the value of
the check box is True. If it is, the current Record ID is compared with the
value of the unbound textbox. If they differ, the user is alerted that they
have left a record locked and an ApplyFilter command is run using the
unbound textbox value as the criteria.

I also have the same checks in the form's Unload event should they just
close the form after an edit.

As I said, it may be ugly but it works!!!!

Many thanks,

Mark
"Mark Reed" <ma*********@ntlworld.com> wrote in message
news:he*************@newsfe2-win.ntli.net...
Hi All,
I have created a multi-user application at work which is working
perfectly apart from a small problem which I believe to more a of a user
issue (maybe some will set me straight on that one). I have set the 'Default
Record Locking' option to 'Edited Record' in the hope that if someone tries
to edit a record someone has open, they would not be able to do so.

After a little testing, I found this not to be the case. Instead, if 2 users
are editing a record at the same time and one of them moves to another
record, the user moving to a new record is presented with a 'Write Conflict'
message giving them 3 options of:
* Save Record
* Copy to clipboard
* Drop changes

I wish to eliminate this happening by making the user "Take Control" of the
record before they can begin to edit it.

I have started this process by adding a text field to the underlying table
called 'LockedBy'. I have also set the AllowEdits option of the form to
False.

To edit the record, the user must first click a command button which checks
if the LockedBy field is Null. If it is, it updates the field with their
UserID (I have a custom login form combined with Access built in security)
and sets the AllowEdits option to True.

Now, should another user try to take control of the record, a check is
performed to see if the field is null (combined with a check to see if the
LockedBy field matches the current user), if it isn't, they receive a
message box stating that they cannot edit the record until xxxxxxx releases
control of it.

This works perfectly except for one problem. Once the user that has control
of the record is finished with it, I need to make sure they release control.
This is what I can't figure out how to do.

I am not a programmer and have only recently started to dabble. Below is the
code I am using. If anyone has any better suggestions on how to achieve the
same result, I'd be very grateful.

TIA,

Mark

Private Sub cmd_TakeControl_Click()

'If the record is locked by someone other than current user,
If Me.Locked.Value = True And Me.LockedBY.Value <>
Displayname(Forms!frm_userlogin!txt_UserID) Then
MsgBox "This record is currently locked by " & Me.LockedBY.Value &
vbCrLf & _
"You cannot edit this record until they release control of it",
vbInformation, "Record Locked"
Me.cmd_TakeControl.Caption = "Take Control"
Me.AllowEdits = False

'If the record is lockedby the current user, determine the command caption
ElseIf Me.Locked.Value = True And Me.LockedBY.Value =
Displayname(Forms!frm_userlogin!txt_UserID) Then
'the current user has the record locked, determine if they clicked to
release or take control
If Me.cmd_TakeControl.Caption = "Take Control" Then
Me.AllowEdits = True
Me.Locked = True
Me.LockedBY.Value = Displayname(Forms!frm_userlogin!txt_UserID)
Me.cmd_TakeControl.Caption = "Release Control"
Else
Me.AllowEdits = False
Me.Locked = False
Me.LockedBY.Value = Null
Me.cmd_TakeControl.Caption = "Take Control"
End If

Else 'No-one has control of the record

Me.AllowEdits = True
Me.Locked = True
Me.LockedBY.Value = Displayname(Forms!frm_userlogin!txt_UserID)
If Me.Dirty = True Then
Me.Dirty = False
End If

Me.cmd_TakeControl.Caption = "Release Control"
End If
End Sub