Access 2010 Write Conflict

I have an Access 2010 database.  I have two forms, FormA and FormB in the database which each point to the same table.  

Scenario...  UserA is editing FormA.  UserB opens FormB, makes changes and closes it.  UserB opens same FormB again and confirms changes were made.  UserA finishes making his changes and when he exits the form gets a Write Conflict error which states "This record has been changed by another user since you started editing it.  If you save the record, you will overwrite the changes the other user made.  Copying the changes...".  The user has the option to Save Record, Copy to Clipboard, or Drop Changes.  

If UserA chooses Save Record, then UserB's changes revert so they are lost.

Under File, Options, Client Settings, Advanced, the Default record locking is set to "No locks".  This is what I want.

Here is the problem.  I cannot force UserA to Drop Changes so UserB's changes aren't lost.  Therefore, I need to write code so whoever gets the Write Conflict popup will get a message from me with instructions on how to handle the write conflict.  I just can't seem to capture the Write Conflict.  

Can anyone help me with this?

Thanks in advance!
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.

Don ThomsonCommented:
A lot depends on what they are working on. Although they can be both using the same Form/Table, They can't have the same record open. That is what is causing the problem
Richard DanekeTrainerCommented:
The conflict occurs because when adding, they seem to be adding against the same primary key.  As much as some developers discourage the use of autonumber keys, if this table was using an autonumber key, you would not have the problem.
One way to address this is to save the record in both forms with a primary key as soon as possible and for this primary key not be the same for both forms.  I cannot provide a clearer example without understanding the table definition involved.
Rey Obrero (Capricorn1)Commented:
have you tried using the On Deactivate event of the form?

private sub form_deactivate()

msgbox err.number, err.description

'to save the record, try using

doCmd.RunCommand acCmdSaveRecord

end sub
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
This generally does not happen when users are ADDING records, but rather when they MODIFY records. If both users try to modify the same record, the first to save their changes "wins", and all others would receive the error stated.

There is no simple fix for this, unfortunately. Some have implemented a "flag" system, where Access will write a flag to a record when UserA opens it. From there, if UserB tries to open the same record your application will alert them, and not allow them to open the record. This works _okay_ except when someone leaves a record open and goes to lunch (or on vacation), and then no body can access that record. You can also have situations where an abrupt program end (power outage, user pressing the power button, etc) can leave records locked, so you have to implement a system to take care of those.

Of course, this does not stop UserB from overwriting changes made by UserA - but that's an entirely different matter.

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
Robert ShermanOwnerCommented:
Have you considered switching to "Edited Record" locking, so that you don't run into the problem in the first place?   If UserA has the record locked, then UserB can contact UserA to ask that they conclude working with the record, etc..

With optimistic locking, your users are going to have to do some amount of manual merging of changes to data if someone else happens to be editing the same record..  out of curiousity, what would your instructions tell the user if you could catch the write error?
Senniger1Author Commented:
DTHConsulting - Re "they can't have the same record open. That is what is causing the problem."  (You are correct.  They do have the same record open.)

Richard Daneke - You are correct they are "adding against the same primary key".  This table is using an autonumber key.

One way to address this is to save the record in both forms with a primary key as soon as possible and for this primary key not be the same for both forms.  (I'm not sure I understand this).

Rey Obrero (capricorn1) - I tried using your "On Deactivate event" suggestion, but I still get the Write Conflict popup.

Scott McDaniel - This generally does not happen when users are ADDING records, but rather when they MODIFY records (THAT IS CORRECT).

Re: "Some have implemented a "flag" system", this might help.  Let me test a little and I'll get back to you.

Robert Sherman - Re: "Have you considered switching to "Edited Record" locking", but I still get the Write Conflict popup.

Re:  "Out of curiousity, what would your instructions tell the user if you could catch the write error", I was hoping if I could capture the error that I could message the user someone else was modifying the record and let them know I was closing the form on them and they could modify later.
Senniger1Author Commented:
Robert Sherman - As I mentioned above, I tried switching to "Edited Record" locking, but still got the Write Conflict popup.  When I did this earlier, I did it at the database level by going to File, Options, Client Settings, Advanced and changing it there.

Since that didn't work, I tried it at the form level changing both FormA and FormB.  This seems to work better because if UserA is modifying FormA and UserB tries to modify FormB, UserB cannot modify FormB until UserA exits FormA.

This might work, however is there a way to let UserB know what is going on an why they cannot modify FormB at this time.  Is there anything I can capture?
Robert ShermanOwnerCommented:
A couple of follow-up questions:

Are you using a split database, where your data tables are in one file on the network and your front-end user forms/reports/etc are in a separate file with links to the data (back-end) file?

Are FormA and FormB different from each other?   Since you've associated UserA with FormA and UserB with FormB I'm wondering if that was because they work with different aspects of the same data?
Senniger1Author Commented:
Robert Sherman - Yes I'm using a split database as you outlined.  FormA and FormB are different forms, but they are using the same table.  UserA enters the data.  Sometimes they have to go back and correct a data.  UserB checks the data and signs off on it.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Be aware that using the Edited Records method can cause multiple other records to be locked as well, depending on various settings in your database. That may not affect you or your users, of course, but you should be aware of it. The only way to be sure that you lock ONLY a single record is to either (a) make sure that a "page" is the same size as a record in your database (which is nearly impossible) or (b) implement the "flag" strategy discussed earlier (which has it's own set of issues or (c) move to a database engine that allows actual record level locking, like SQL Server.
Robert ShermanOwnerCommented:
When UserB checks the data and signs off on it, does he actually edit any of the fields that UserA typically enters?   Or does UserB have separate fields where the signing off is indicated, perhaps with notes etc?   If the latter, perhaps there's a case for separating the two concerns into separate, related tables..   Just a thought, though I realize I'm getting quite far outside the box at this point.
Senniger1Author Commented:
Technically UserA completes different fields on the form than UserB, but I don't want to separate out my tables.

I really just want to make it so if UserA is modifying FormA, then when UserB opens FormB to modify it, they get a prompt letting them know is the record is being modified by someone else and then closes them out of the form.

I would think there would have to be a me.dirty that I could use in the On Open event to catch that the record is being modified and close the user out of the form they are in and providing them with a message letting them know what is happening.

Is there no simple solution to this?

BTW - Thanks to everyone for the help!!!
Robert ShermanOwnerCommented:
Well, there is a me.dirty, but what you're really looking for is a you.dirty (coming from another instance of access on another machine), of which there is not.    This is precisely what Scott is referring to with his suggestion of implementing a "flag" somewhere.   Basically, the first person to open a record would set a flag somewhere that would be checked by both user instances whenever they open a record.. if the flag is there, it means someone else already has the record open, so come back later..

You could create a table for this, and store the ID/Key field in the table.. Then both forms would query that table for the existence of a record with the key field in quesion... If it exists, then the record is "open" somewhere.   The problem is that if the flag gets left behind for some reason, then the record will appear "open" until someone manually clears it out of the table.
Senniger1Author Commented:
I like the idea of a flag, but I'm not sure how to implement it.  Let me see if I can figure out what to do and I'll be in touch.
Senniger1Author Commented:
Thank you to everyone.  I used pieces of both comments.
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
Visual Basic Classic

From novice to tech pro — start learning today.