How present message to user if another user has the same record open

I have a split database.  The back-end file is on a server.  The front-end file is on every user's computer and is linked to the back-end.

If user #1 has a record open via a form and user #2 tries to open the same record I want a message to be presented to user #2 indicating that the record is in use and prohibit them from getting the record to display on their form.

How can I do this?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Not as easily as you would imagine.  With what's built in, setting record locks set to Edited record in the form, they will get a message when they go to edit the record, but not before.

To prevent them from seeing the record at all, you'll have to build your own locking.   have a look at this:

Bounce back with any questions you might have.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
I don't have any questions yet regarding this topic because I need to consume the great information you have presented.   But I do have another question closely related but will post another topic.  It'll be named...

"Back-end file locked for second user"
I would suggest leaving the lock settings as the default which is optimistic.  If it is important for you to have a visual clue that a record is being updated by a different user, then always include the record selector on your form.  When a user types the first character into a clean form, a pencil appears in the record selector.  If you navigate to a record that has been dirtied by a different user, there will be a circle with a line in it in the record selector.  If you attempt to save a record that was dirtied by a different user, you will get an error message with three options - discard your changes, overwrite the other user's changes, save your changes to the clipboard.

If this isn't sufficient, you might want to implement something like what I did for a workflow app.  Since the users were all pulling tasks from the same task list, it was important to have a visual clue that someone else was working on the task.  I added a locked by userID and Locked date to the primary table.  When a user selected a record from the task list to open, I updated the record with his userID and the date and time he opened it and refreshed the task list so everyone could see that this task was being worked on.  When the task was done and the record was saved, I removed the locked data values and reset the task flag so it would drop off the list.  Because there was always the possibility of a record getting locked but not unlocked, I added a query that would reset all the locks to handle the issue and also if the same person who had previously locked the record opened it again, the lock would reset.  Other people could open a locked record but they could not update it.

It really depends on your application but in all the apps I have ever developed, it is only the ones with task lists that ever had a problem.  for the most part, a record was only updated if the customer called or if some paperwork showed up that needed to be handled.  In both those cases, there really wasn't any danger of a different user updating the same record at the same time.  All in all, this is rarely an issue and Access does handle contention.  You just need to make your users aware of what happens in the off chance that they do end up conflicting with each other.
SteveL13Author Commented:
Both great advise.    I decided to leave the lock settings as the default.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.