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

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.

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.

Maybe you could add to the drop down a simple indicator that the records is being processed...

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.

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?

You could put a flag to show this

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

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

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.

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

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