Access 2010 Write Conflict

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

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.

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

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?

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.

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?

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?

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.

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.

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.

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

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.

Thank you to everyone.  I used pieces of both comments.