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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
<<but this doesn't seem to work.>>
It won't work until the second user goes to edit the record. If all your trying to do is prevent two users from editing the same record, then setting record locking to edited records will work.
They will get a lock conflict and not be able to update the record.
Jim.
It won't work until the second user goes to edit the record. If all your trying to do is prevent two users from editing the same record, then setting record locking to edited records will work.
They will get a lock conflict and not be able to update the record.
Jim.
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.
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.
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.
I don't recommend this except in one specific situation and that is that you have a queue of work that everyone picks from and you want to prevent two people from picking the same item to process.
In other situations, you should just trap the conflict caused when both parties attempt to update and give the user a meaningful answer. Since except for the first situation, most updates are prompted by calls from a customer or paperwork to process, conflicts are rare.
To lock a record as you requested:
1. Add two columns to the table you want to lock. LockedBy and LockedDT
2. In the current event of the form, check to see if this is a new record. If not, check the LockedDT to see if it is populated. If it is populated, display a message to the user and use the "ALLOW" properties to prevent editing and deleting. If it is null, set both fields and force the record to save using DoCmd.RunCommand acCmdSaveRecord
3. In the AfterUpdate event, set the two fields back to null and close the form or move to a new record.
Due to the potential for records being left locked. you have to create procedures for Admins to force an unlock of a specific record or all records. I use two fields because you may want to talk to the person who has a record locked before doing anything plus you will want to know if the lock is recent or old. I think there's something I'm missing but I have to run. I'll be back in a few hours.
In other situations, you should just trap the conflict caused when both parties attempt to update and give the user a meaningful answer. Since except for the first situation, most updates are prompted by calls from a customer or paperwork to process, conflicts are rare.
To lock a record as you requested:
1. Add two columns to the table you want to lock. LockedBy and LockedDT
2. In the current event of the form, check to see if this is a new record. If not, check the LockedDT to see if it is populated. If it is populated, display a message to the user and use the "ALLOW" properties to prevent editing and deleting. If it is null, set both fields and force the record to save using DoCmd.RunCommand acCmdSaveRecord
3. In the AfterUpdate event, set the two fields back to null and close the form or move to a new record.
Due to the potential for records being left locked. you have to create procedures for Admins to force an unlock of a specific record or all records. I use two fields because you may want to talk to the person who has a record locked before doing anything plus you will want to know if the lock is recent or old. I think there's something I'm missing but I have to run. I'll be back in a few hours.
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.
<<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?
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. 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 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.
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial