Avatar of Juan Ocasio
Juan Ocasio
Flag for United States of America asked on

Preventing users from modifying a record that's being viewed in a bound form

Hello all:

I am trying to remedy a situation where users can access the same record on a bound form.  Basically I have a form with a sub form (so a header and detail table).  On the form a user can create a new record (clicking a button).  This generates a new id and also creates a new record in the database.  Likewise, the user can use a drop down on the form to select an existing record.  What I would like is for when a user is on a record (either a newly created one or an existing on they recently selected), if another user tries to access that record, via the drop down, they will get be told that record is locked.  I tried to set the Record Locks property to edited record, but this doesn't seem to work.

Any help would be greatly appreciated.

Thanks!

Juan
DatabasesMicrosoft Access

Avatar of undefined
Last Comment
PatHartman

8/22/2022 - Mon
SOLUTION
Jim Dettman (EE MVE)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Jim Dettman (EE MVE)

and by the way, if you really want to prevent viewing of a record by the second user, then you will need to build it yourself.  The database locking won't do it for you.   You can do that with the idea/code here:

https://www.experts-exchange.com/articles/5328/Resource-locking-in-your-applications.html

  User 1 would place a lock on the record, and in the afterupdate of the dropdown, you'd try to place a "lock", and if it fails, deny the second user from seeing the record.

Jim.
Juan Ocasio

ASKER
Thanks Jim:

It's weird, but it doesn't seem to work.  I'll have to revisit this then.  We ran tests yesterday, where I had an end user select a record, then I selected the same record and it allowed me to.  I'll retry today.
John Tsioumpris

Maybe you could add to the drop down a simple indicator that the records is being processed...
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
PatHartman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Jim Dettman (EE MVE)

Juan,

<<We ran tests yesterday, where I had an end user select a record, then I selected the same record and it allowed me to.  I'll retry today.>>

  The built in database locking only deals with updating records, not viewing.    Until users actually start to edit a record, nothing happens.

  If you want to prevent viewing, you'll have to do your own locking as I commented on above.

Jim.
Juan Ocasio

ASKER
Thanks John:  Interesting idea.  How would I know the record is currently opened?  Is there an indicator at the form level?  Does something happen when a user selects an item on a bound form, and does it persist to a different instance of Access so the second person can get a notification of the record being processed?
John Tsioumpris

You could put a flag to show this
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Juan Ocasio

ASKER
Thanks All:

I think I am going to go with Pat's solution on this one.  Was hoping there was an easier  way, but Jim's point about only edited records being locked is well taken.

As always, many thanks for your assistance!

Juan
Jim Dettman (EE MVE)

<<I think I am going to go with Pat's solution on this one.  >>

 I guess you didn't read the article :(

 While Pat's solution will work, it's not ideal and I point out why in the article, namely if you have something like a power failure, you've got a lock field in every record that needs to be checked/cleared.   If you don't clear them all, then your going to drive someone crazy with clearing locks every time someone hits one.

 Far better to use a centralized table.   Read the article, drop in the code, and your off an running.

Jim.
PatHartman

I did mention that locking the records was a really bad idea except in the one situation I mentioned and I also mentioned that you have to deal with stray locks.  I agree with Jim that if you feel the need to lock everything, then a single table is easier to recover from but I nave never encountered a situation other than the task list process that I mentioned where locking was actually necessary even when I was creating CICS transactions used by thousands of simultaneous users and I've been doing this for a very long time.  Yes, there are occasional conflicts.  Yes, the Access message is confusing.  But, showing a record selector on a form will give a visual indication that some other user is editing the record and the app can also detect that situation to prevent the second user from editing.  I used the locking technique because in my case, the queue table was separate from the record actually being edited so they were actually both locked.  The queue table so that no one else would pick that item to process and the client record because the task list was a batch process that was generated by processing documents received via email or fax but we also locked the client record on the off chance that the client would call at the exact moment someone was working on his record in the task list or in case there was more than one action for the client in the task list.

If this is so important to the process, the BE should be something other than ACE where you can define permissions that control how the RDBMS locks records.  For example - Repeatable Read prevents a record from being read if someone else has dirtied it.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Juan Ocasio

ASKER
Thanks Jim:  I did read the article and it is very insightful.  The lock I need is not a strict one, so I and clearing all locks by userID on form load.

Pat: understood. We do need to lock as we can only have one person at a time on any given record.  This came up because 2 users were on the same record at one time and very unexpected behavior occurred.

Thanks again all! Greatly appreciate the assistance!

Juan
PatHartman

Juan, as I mentioned, there are better ways to do this.