Write Conflict

[bigukfan]

I have been getting a message about a 'Write Conflict' when moving between forms, and am struggling to understand the issue.

I am using Access 2007, and have a relational database with around 10 tables of data and around 40 tables used as LookUp for ComboBox controls. I have more than one form bound to some tables, but only one form loaded at a time. after adding or editing data, I click a command button which opens the next form then closes the current form. That is when I get the message "This record has been changed by another user since you started editing it" I then have three options: Save record, Copy to clipboard and Drop changes.

When I did some research on this issue, it seems the explanation is record locking, as the first form is editing data on 'part' of the table which the second form reads/writes to as well.

The solution from Microsoft was to change the record locks at form level to 'Edited Record', on both forms. I did this and still receive the same message. The second suggestion, also from Microsoft was to add the following code in the On deactivate event:

DoCmd.RunCommand acCmdSaveRecord

Again, this did not solve the issue.

To complicate matters I also get this message when I switch between forms that are NOT bound to the same table, which can't be down to record locks.

Has anyone else experienced this?

[TheSmileyCoder]

Try to change your code so that it closes the form first before opening the next form. Then there shouldnt be a write conflict at all.

[bigukfan]

TheSmileyCoder,

Thanks for taking the trouble to reply. I did try closing form 1 before opening form 2, but still received the same message. I also get the same write conflict message when opening a second form not bound to the same table, so the issue, I suspect, is nothing to do with locks, this may be a confounding error masking something else.

I never used to get this on earlier versions of the same database which used the same method of moving between forms bound to the same table, which also suggests this is not strictly an issue of locks.

Any further ideas would be appreciated!

[bigukfan]

I seem to have found a solution to the 'Write Conflict. I use the following code before opening the second form:

RunCommand acSaveRecord

That seems to avoid any conflicts with multiple forms bound to the same table.

[TheSmileyCoder]

If the form is dirty (I.e. a edit/new record is in progress) then it will automatically save when you close the form, which is why I suggested to close Form 1 before opening form 2. However without a full posting of your code, its hard to be sure exactly what it is your doing, in which event and in which order.


If you are the only user of the database then you don't need to have record locks at all. If you have read up a bit on recordlocks you will probably have realised that locking the "Edited Record" is actually a bit more complicated then simply locking the edited record as it will also lock any records located in the same filepage.

[bigukfan]

TheSmileyCoder

Thanks for your reply, all useful information.

[Peymanrs]

@bigukfan
Thank you so much bigukfan!
It was over a week headache until I found your solution which helped me a lot.
As you mentioned above, none of the Microsoft solutions worked.
just I used the following code I/O yours.
BTW, Thanks again for your help :-)

DoCmd.RunCommand acCmdSave