Write conflict - This record has been changed by another user ... (1 Viewer)

[mcalex]

[Solved] - Write conflict - This record has been changed by another user ...

Hi all

I've got a form that shows existing records. The user clicks a button which opens a form allowing the shown record to be edited. When changes have been made, the user clicks save, which saves the data and closes the edit form.

Unfortunately, when the user clicks save, a dialog pops up:

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.

The dialog has 'Save Record', 'Copy to Clipboard', and 'Drop Changes' buttons. Save Record is always disabled.

How do I stop this?

I have tried to shut the form that opens the Edit form, so that it has to be reopened (with the new data) after the user has made changes, but to no avail.

Any thoughts gladly appreciated

cheers

mcalex

 

[RuralGuy]

Try binding your forms to *queries* of tables instead of directly to the tables.

 

[mcalex]

err, it is a query bound form.
I open the form with a query that takes includes a where clause from the id of the record being viewed on the form with the edit button.

When the user hits save, another query updates the table with the new values from the editable controls (I don't check to see which control has a different value, I just update all values in editable controls; there is code to check something has changed.)

. . .

Aha! That seems to be the problem. I'm trying to explicitly save something that access is saving for me. Thanks for switching the lightbulb on for me RuralGuy

But now I've got the opposite problem. How do I cancel changes, if the user makes an error? Since the data has been saved accessmatically, my save button is redundant and the code in my cancel button (Docmd.Close acForm, "frmFormName", acSaveNo) doesn't work - ie the changes are saved. Is there an easy way to cancel a user's changes, or am I going to have to unbind the form?

thank you
mcalex

 

[RuralGuy]

Use a Public variable in the form to authorize saving and set it with your "Save" button. Then check it in the Form's BeforeUpdate event and do a Me.UnDo if not set. You could also ask a question or post a message in the BeforeUpdate event if the variable is not set so users will know their effort will not be saved.

 

[vbaInet]

I would just add to this. The pop-up/edit form should be unbound. This is what is causing the write conflict. You can put some code in the Click event of the Save button to pass the values from the unbound controls to the controls on the main form, then call the save record action of the RunCommand method in the main form.

 

[RuralGuy]

Hmm...I bind both forms all of the time to a query without an issue.

 

[vbaInet]

Oh yeah that works too, but I just tend to do it all in one form. The only advantage of doing it this way is that it's unbound and not pulling from source again. But your way works.

 

[RuralGuy]

Good. I thought I was overlooking something important. I tend to have each form stand on its own as much as it can. Easier for maintenance by someone new (or this old brain) later on.

 

[gemma-the-husky]

it might be this

show the record selectors, and see if the record you are expanding is dirty when you open the popup.

What happens is your main form dirties the record - you then try and change it in the popup - and of course you get a warinng that the record has been changed.

the answer is to save the record before you use the popup.
aletrnatively. if its a single field, try shift-F2, or programme a dble-click to runcommand.accmdzoombox - both of which don;t give you the conflicting write issue

(both of these ideas were courtesy of ruralguy, I think)

 

[mcalex]

Hi guys, cheers for the discussion. Very educational. I'm glad there are different, acceptable methods for this sort of thing.

Have gone with RG's suggested SaveRecord public variable at Form_BeforeUpdate().

Not having a MS background, I'm used to doing it all myself (with sql & connections etc), but I'm learning the accessmatic way of things. I fell over here because I was binding the form but treating the data as unbound.

<rant>
Now I've worked out my problem, I'd like to mention another gotcha. I cannot be convinced that

Me.Dirty = False

should save a record.

A nasty trap for young players, that. If you set Me.Dirty = False as the OnClick event for a cancel button, it will have the opposite effect to what you expect ... imho.
</rant>

cheers & +'s for RG (coz I have to 'share the rep' b4 +ing vbaInet & Gemma again)
mcalex

 

[gemma-the-husky]

there are numerous ways to save a record

eg
- just moving to a new record
- just closing a form
- by using code on a button command (i think its runcommand accmdsaverecord)
- by using the tool bar drop down - records/save record
- it looks like there is a shortcut key for this last one also
- if you have record selectors visible, an edited record will show a pencil, rather than a black triangle. Clciking the pencil will save record
- and setting me.dirty to FALSE in code will DEFINITELY 100% do the same as any of these - although it might seem a strange idea (it did to me! - I thought the dirty property would be read only)

the only caveat is that if the record cannot be saved because some constraint is violated, then the record will not be saved - and you will get a warning message. Depending on your code, this may or may not stop your action. eg - if you close a form, you may just get a message that the record cannot be saved, but the form still closes. However if you intercept and stop the record save, then the form will not close either.

You get a similar number of methods to delete a record, which can make managing saves/deletes tricky, if it is very sensitive. In those cases, possibly using an unbound form becomes a better option.

 

[AOB]

Just solved a similar problem thanks to this thread. Extremely informative. Thanks to all for a very useful discussion!

 

[jerem]

This is an old post but nothing I do seems to work. :banghead:

I have a form with a number of subforms. One of these subforms has other subforms. The backend of my Ms Access application is SQL server 2008. When I use the command button on my form to delete the current record I get the write conflict message. This only happens with one form. I use the same code on other forms and it works fine.

I have tried a zillion of things but none seem to work. The only thing that worked is if I delete the subform that contains other subforms. Obviously I need those subforms. Can someone help? Thank you

 

[jerem]

...Alright... the only way I found was to change the source object of my subform to "" before deleting. Not sure what it indicates though.

 

[The_Doc_Man]

A form with a sub-form really is two forms that are separate, but the "parent" form has a subform control into which you could load a subform. Then Access provides automatic infrastructure to do a filtration of the subform based on a selected field on the parent form. But they are still TWO FORMS. In order to see something in the subform, you MUST choose the parent record in the parent form. If the queries that drive the subform in any way overlap with the query that drives the parent form, the odds are pretty good that you have opened the query for WRITE/UPDATE in more than one place. If the parent can create a new form and the subform can muddle about in the related query, there is your problem.

 

[jerem]

Thank you The_Doc_Man

 

[mjdemaris]

To continue this old post:

Doc, how would you solve this multi-form/query problem?

I created an inventory system that uses:
navigation form --> Search Form -->Subform datasheet.

When the user clicks a field in the datasheet, a details form is opened. The user clicks Continue to open another form which records the removal of the part.

The Write Conflict occurs when I open the Manager's form to edit that same record. The navigation and all subforms are closed via a macro (which also opens the Manager's form), so I figured that the record would be saved.

I've tried a few tips in this post, but have not tried using unbound forms. Mainly because some of the queries are complicated and I am not confident creating complicated record sets yet.

Mike

 

[mjdemaris]

Nevermind, Doc. I put the Me.dirty statement in a few other places and finally found one that worked! It was at the very end of the process, but got it!

Thanks to all the great developers here!

 

[gemma-the-husky]

one thing you can do is include recordselectors in your form (temporarily at least)

you get a visual indication of a dirty/edited record, which is helpful in these circumstances.

 

[mjdemaris]

Dave,
I tried that with the datasheet subform, but there was no indication of the record edit.

I did not try that with the other forms, however. In the future, I may try using unbound forms.

Thanks.