Solved

Record being edited by other user

Posted on 2013-06-16
8
443 Views
Last Modified: 2013-06-24
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.
0
Comment
Question by:DatabaseDek
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 39251190
With the database system itself, you'd need to use pessimistic locking (record gets locked as soon as a user starts to edit a record), which is "Edit locking" in Access and make the record dirty when they click the button.

The problem is that often you cannot get it to lock down to the record level (you end up locking a page of records) and with Access specifically, it's tough to control.

So many build their own locking scheme independent of what the RDBMS does leaving that set at optimistic (only locks when the actual update to the database is made).  This is "No Locks" in a Access form.

To do your own locking, you can do something along the lines of this:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_5328-Resource-locking-in-your-applications.html

Doing locking on your own, you can have a lot more flexability (ie. Locking out a module, such as all of AR) with being able to lock more then just tables or records.  Any type of resource can be locked (printers, a specific function, use of a specific form, etc)>

 The drawback of course is that your really not locking anything this way.  If part of your app doesn't bother checking for contention, then there's nothing stopping it from going ahead and using the resource.

Jim.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39251641
No point please.

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.
0
 
LVL 9
ID: 39258141
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
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:DatabaseDek
ID: 39270727
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."
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39270945
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

Open in new window

0
 

Author Closing Comment

by:DatabaseDek
ID: 39271062
Great code sample in that URL

Why does fyed not want points?

Many thanks

Derek
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39271088
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.
0
 

Author Comment

by:DatabaseDek
ID: 39272168
Well thank you for that last snipit of code it was most usefull.

Derek
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question