Write Conflict on Primary Key - Access Tables Linked to SQL Server

[wakeuky]

I am posting in desperation, I have scoured Google and multiple forum boards over the last two days looking for a solution to my problem.

Issue: The primary key to my tables is a user's email address. In the user overview Form (similar to a profile page) the user/admin should have the ability to modify the primary key. Whenever this is attempted the following message is displayed -
"Write Conflict:
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 to the clipboard will let you look at the values the
other user entered, and then paste your changes back in if you decide to
make changes. "

Database Setup:
This particular form is a form with multiple subforms which contain aspects of the participant's profile such as addresses and phone numbers. This is setup this way due to the structure of our SQL server tables. Access is one of our two frontend platforms, the other being a web application.
It is possible to change the primary key directly in the linked table and the change is evident through all of the other tables whose primary keys tie to the primary key of this main table.

I have searched Google extensively. Making the following changes and observing the following checks:

- There are no bit datatypes with null values in any tables
- I have experimented with making sure there is a Timestamp datafield in the main table where I am attempting to change the primary key.
- Only attempting to change the primary key generates this "write conflict"
- I have attempted to add "me.dirty = false" into several of the "on update" actions to no avail.
- All tables use a query as their record source.
- A few other odd fixes that did not work.

I hope someone has a resolution for me while I still have hair, many thank yous in advance!

 

[boblarson]

Make a separate form for editing this one piece. And close the other form when doing so.

 

[wakeuky]

Bob, Thank you very much for the timely reply!

Unfortunately I was looking for a method to force the update while on the same form. In my previous trials I had noticed that it definitely worked using a separate form however I would like to avoid being forced that route!

My boss said screw the users do not allow them to change their primary email address (Primary Key), however I am just too nice of a guy!

 

[boblarson]

The problem comes in that there are records which are linked by the PK in the subforms. So having it open won't let you change it there. All other data would be fine but the one that needs its own little form is the PK field

I'm not a fan of using something other than a surrogate key for a PK as things can and do change. I woud have a PK that is an autonumber and that will associate the records but the user could change their email address all day long and it not matter.

But you have what you have and so you have to live within the confines of what you currently have. So, a separate form I believe is the only real way this will work.

 

[wakeuky]

Okay thank you, I will run the idea by the boss man. I understand what you are saying about the primary key now. I wasn't sure if there was an exclusive way to update it that I was not aware of.

I agree with having a numeric primary key as well. This project started off like that actually. Some obstacles on the PHP Frontend led us to this configuration.

Thank you again for your assistance!