Locking/Unlocking records, oncurrent, getting value from previous record?

spieh

New member
Local time
Today, 14:07
Joined
Oct 14, 2008
Messages
2
I have a form that multiple users will be using to view/edit records at the same time.

To prevent data from being edited by 2 people at the same time I am using the following method:

I have a table called Tracking that has the following fields

ID (primary key)
Lockstatus = 'Open' or 'Closed'


So when a record in the form becomes the current record I have this function for the Current event. This will check if the current record is open and if it is, change the Form Controls to be uneditable. It it was not open it changed the Lockstatus value in the Tracking table to 'OPEN' so that if another user loads this record they will not be able to edit it.

Code:
Private Sub Form_Current()
If ID.Value <> "" Then
'check if record is open by another user and allow/disallow changes
Dim Lockstatus As String
Dim strSQL As String
Set rs = CreateObject("ADODB.RecordSet")
rs.LockType = 3
Set conn = CreateObject("ADODB.Connection")
Set conn = CurrentProject.Connection

strSQL = "SELECT lockStatus FROM Tracking WHERE ID=" & ID.Value

rs.Open strSQL, conn

If rs.EOF <> True Then
  Lockstatus = rs.Fields("Lockstatus")
  If Lockstatus = "OPEN" Then
    Me.AllowEdits = False
    MsgBox ("This record is open by another user. Data is read-only.")
  Else
    Me.AllowEdits = True
    rs.Fields("lockstatus") = "OPEN"
    rs.Save
  End If
  
End If

End If
End Sub
This actually works well for disallowing concurrent users from editing the same record. The problem is this: Records never unlock.

I haven't figured out a way to access the primary key (ID.value) from the previous record that was viewed when the Form_Current() event fires. By the time you Current() fires you are already getting the values for the current records Controls. There is no reference to the Controls values of the old record.

Users do not have to view records in sequential order so you can't simply just read the value of the left or right record next to the current one.

I already have the subroutine for unlocking the record. And I placed it in the Form_Unload event so when the user closes the current record it will unlock it, only if it was previously locked.

Code:
Private Sub Form_Unload(Cancel As Integer)
If ID.Value <> "" Then
'User closed the form so unlock the current record so other users can edit it -- only if the record was already locked
If Me.AllowEdits = True Then

Dim Lockstatus As String
Dim strSQL As String
Set rs = CreateObject("ADODB.RecordSet")
Set conn = CreateObject("ADODB.Connection")
Set conn = CurrentProject.Connection

strSQL = "Update Tracking set lockStatus = 'Closed' Where ID=" & ID.Value

'MsgBox (strSQL)
DoCmd.SetWarnings (False)
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings (True)

End If

End If

End Sub
I just need to figure out where else I can copy this code so that at currently Locked record becomes unlocked when the record is no longer the active record on the form. I tried the Form_LostFocus event but that event doesn't even fire when moving to a new record.

Am I correct in assuming the only event that triggers when you move to a new record is the Form_Current() (form is not dirty so before/afterupdate do not come into this)? And if so, then back to my previous question, how can I access the ID.value of the previous record that was active after Form_Current() has fired?

I have been reading up on the use of the Me.Bookmark to make a reference to the current record prior to moving to the new record but if you have concurrent users wouldn't the value of Me.Bookmark potentially be changed by the other users before you could reference its value?


Thanks.

-Dave
 
Sorry if I missed something but have you tinkered with the record locking options on the Options->Advanced tab?
 
do you really need this?

how often do two users get the same record?

even with "no" locking what access does, i believe, is reread the record before saving your changes to make sure no-one else changed it in the meantime - if it has, it warns the user this has happened

i am not sure, but locking records may be more drastic, as it possibly prevents other users examining the record while it is locked
 
Using access's default locking mechanism the user is not alerted that someone else has the record open until after they make all the changes and then hit save. It wastes their time to find out at the end. If you check when the record becomes active and alert them it is already open by another user they know they can just read the data.

The record level locking in the advanced tab will shoot a pop-up to the user asking them to save changes anyway, copy to clipboard or cancel. I don't want the user to have the option of overwriting the record even though they know it has been edited by another user.
 
Is there really that much possiblilty of a conflict? I've written many multi-user applications and the conflicts are quite rare. So rare in fact that I've never had to do anything to prevent them. If your form shows the record selector, it changes to a circle with a line through it to indicate that the record is being updated by another user. You could train your users to look for that visual clue.
 

Users who are viewing this thread

Back
Top Bottom