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