Derek Brown
asked on
Record being edited by other user
I have a form that has an on current procedure that locks the form so data cannot be accidentally changed. The user has to click on an "Edit" button to change data.
How do I make the lock conditional on there being no other user currently editing the same record.
I.e. Lock the form only if not being edited by other user.
The data is on a server and can be accessed by 10 users and the problem usually (but not always) arises because on start of the application all users open the form on the same record.
How do I make the lock conditional on there being no other user currently editing the same record.
I.e. Lock the form only if not being edited by other user.
The data is on a server and can be accessed by 10 users and the problem usually (but not always) arises because on start of the application all users open the form on the same record.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No points please.
I'll also add that opening an editable form to the first record isn't a good practice. That poor "alphabetically-challenged " record will get many mistaken edits, may cause locking issues, and can hurt performance too (since Access is opening a large updatable recordset and managing more locks.)
Instead, the way we approach this is to create a read-only continuous form that allows the user to sort, select and search records. When they drill down, they see one editable record.
You can see example screenshots here:
http://www.jstreettech.com/CaseStudy/WSHFC.asp
Cheers,
Armen Stein
I'll also add that opening an editable form to the first record isn't a good practice. That poor "alphabetically-challenged
Instead, the way we approach this is to create a read-only continuous form that allows the user to sort, select and search records. When they drill down, they see one editable record.
You can see example screenshots here:
http://www.jstreettech.com/CaseStudy/WSHFC.asp
Cheers,
Armen Stein
ASKER
Thank you All
Fyed. Can you let me have an example of how you, "use the current event of the form to test whether the current record is already locked, and if so, disable the edit button and display a label that indicates the record is locked, and who it is locked by."
Armen. I try to avoid forcing my clients to start their program having to find a record. To my mind when I open an form it should open where I left off. I have arranged to have the form, not the record, locked so it takes a deliberate action to start editing the first record. I hate forcing my customers to "Drill down" when they clearly want to continue where they left off. However in a multi user environment this may cause me problems. So all I want to do is say "Lock the form only if not being edited by other user."
Fyed. Can you let me have an example of how you, "use the current event of the form to test whether the current record is already locked, and if so, disable the edit button and display a label that indicates the record is locked, and who it is locked by."
Armen. I try to avoid forcing my clients to start their program having to find a record. To my mind when I open an form it should open where I left off. I have arranged to have the form, not the record, locked so it takes a deliberate action to start editing the first record. I hate forcing my customers to "Drill down" when they clearly want to continue where they left off. However in a multi user environment this may cause me problems. So all I want to do is say "Lock the form only if not being edited by other user."
Simple Example:
Private Form_Current
Dim strCriteria as string
Dim varLockedBy as Variant
'Determine whether the record is already locked
strCriteria = "[TableName] = 'ABC' AND [RecID] = " & me.ID
varLockedBy = DLOOKUP("LockedBy", "tblRecordLocks", strCriteria))
if isnull(varLockedBy) Then
me.cmd_Edit.Enabled = True
me.lbl_Locked.visible = False
else
me.cmd_Edit.Enabled = False
me.lbl_Locked.Caption = "This record is locked by: " & varLockedBy
me.lbl_Locked.Visible = True
End If
End Sub
ASKER
Great code sample in that URL
Why does fyed not want points?
Many thanks
Derek
Why does fyed not want points?
Many thanks
Derek
because in my original post, I was just endorsing a solution from Jim's article.
Generally, if an expert is just "endorsing" a previous solution, we will indicate "no points please", attributing the answer to the previous post.
If we provide a different perspective, or a completely different solution, you generally will not see that.
Generally, if an expert is just "endorsing" a previous solution, we will indicate "no points please", attributing the answer to the previous post.
If we provide a different perspective, or a completely different solution, you generally will not see that.
ASKER
Well thank you for that last snipit of code it was most usefull.
Derek
Derek
For what it is worth, I use a technique similar to Jim's "A Better Approach" section in several of my applications.
I use the current event of the form to test whether the current record is already locked, and if so, disable the edit button and display a label that indicates the record is locked, and who it is locked by.
I also use the Form_Current and the Form_Close events (or my Save or Cancel buttons) to remove locks for the current user, table combination, so that all previous locks for this combination of user/table get removed when I close the form or move to a new record.
When the user clicks the Edit button, I write a record to my ResourceLocks table.