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

[The_Doc_Man]

You have to remember that if there is an autonumber involved ANYWHERE, your record becomes instantly dirty based on wherever that record was first opened with a new record. Ditto if there is ANY code in the Form_Current routine that has even the slightest chance of doing something semi-permanent i.e. permanent enough to write it.

In my big-kahuna database, I have two types of forms - bound (because they are maintenance forms to single-table entities like servers, users, projects, security reports) and unbound (because they represent activities based on a JOIN query and due to the complexity of relationships, from the form that query is not updateable.)

For bound forms, your friends are the Form_BeforeUpdate event and some intelligence in the button that I use to close the form, plus your really best friend ever, the control_LostFocus event that can set a flag that says, "I'm dirty now" - and it can do something useful like make the form's navigation controls go away until you resolve the changes. Then, the Form_Close(Cancel As Integer) event becomes another friend because it can prevent you from closing a dirty form until you click some OTHER button that says, "Undo" (discard ALL changes). It takes a lot to set up, but trust me when I say that it is worth it.

As to the unbound forms, they are my worst nightmare - and my most common requirement - since you are almost guaranteed to get caught on SOMETHING or another being open at the time and therefore susceptible to record locking. Making it unbound means you have to be picky about whether you will allow an update after a delay, which is why I have "Date/Time of Last Update" and "Updated" and a couple of other flags to let the queries detect any record conflict.

 

[RuralGuy]

You have to remember that if there is an autonumber involved ANYWHERE, your record becomes instantly dirty based on wherever that record was first opened with a new record.

Hi Doc,
Are you sure this statement is correct. I'm the last one to want to disagree with you but I'm pretty sure an AutoNumber by itself will not make a record Dirty. Go ahead and let me have it.

 

[mjdemaris]

Doc is saying that when a record is first opened, by any form any where, with an autonumber, then that record is now dirty, in the process of being edited.

This does not apply to simply viewing a table/query/form without interacting with it.

As soon as anything in that new/old record changes, it is dirty. Since a new number is created as soon as that record is edited - dirty.

 

[mjdemaris]

What does Close(Cancel As Integer) do?

 

[RuralGuy]

I agree with "once you make any other change" but I believe unless you make that change the record is not Dirty yet and the Recordset can be closed without any prompt.

 

[RuralGuy]

What does Close(Cancel As Integer) do?

Usually keeps a Form from closing if you set Cancel = True.

 

[mjdemaris]

Thanks RG.

So, Doc, bound forms are better for more simple operations?
And bound forms, then, will do the bulk of the end user work?

What things could/should I check in Form_BeforeUpdate?
Can you post an example of "intelligence on close button"?

So, Cancel as Integer would be used as in a dialog box that says "Are You Sure?"?

And for the Date/Time of last update, do you set this for each table/query/record that is being accessed? How do you implement that?

Mike

 

[RuralGuy]

@Mike:
Rarely is there a need for an unbound form. Tread on this concept lightly as it can involve a lot of code to duplicate what MS Access is already doing. Still, Doc knows his stuff.

 

[mjdemaris]

@RG:
Well, I have found that the main user here really likes the Excel way of changing data - click, type, Enter. Next row.

So I have had to come up with some crazy queries - which are not updateable. So i've had to do some record set work and use temp vars and stuff. I think I could clean it up a bit, so I'm working on mapping out the variables and how data is transferred.

 

[RuralGuy]

You have to do what you have to do. Good luck with the project.

 

[The_Doc_Man]

Form_BeforeUpdate is what you use to see if you are ready to write back a complete record based on the contents of a bound form. It is here that you would check for values that don't somehow match your expectations. You should consider these as cases where the "Field Validity" rules that you define in your table definitions simply cannot be written with enough complexity to know if you are ready to do something.

I rarely do this, but there are cases where I have to look up something via DLOOKUP to recover data from a table OTHER THAN the table bound to the form. Static field-validity rules like ">0" don't need this - but if you have to compute something based on multiple controls on the form or otherwise validate some combination of choices, you have to have a place to do that - and Form_BeforeUpdate becomes significant.

As to the question of whether a form is dirty because of an autonumber: If you bind a control to the autonumbered PK, then do whatever you do to create a new record, the form becomes dirty. Of course, the implicit test for that is that the relevant control's .Value and .OldValue now differ when Access changes focus. Whether you do something in the control_LostFocus routine or not, we have to remember that Access brought along some baggage, and that implicit comparison of the current and old values is one such bit of baggage.

Unbound forms are actually not that unusual. The big issue for me is when I am trying to implement multi-function forms where all sorts of filters have to be applied before you control a specific action. Also, I noted that if I bound my primary work table, I would sometimes get phantom updates that I simply could NEVER allow. Therefore, the only other way to stop that (since I could not find and plug all the "leaks") was to unbind the form and only do the updates in SQL transactions. As a side effect, my exposure to cross-user table locking went down to darned near zero. It ain't for the faint of heart, I'll admit, and I have regretted the need more than once, but the problem is that Access is by its nature DESIGNED to make it easy for a user to update something. However, for the problem I'm addressing with my big-ugly DB, that easy update is NOT wanted.

 

[jameshill1]

Awesome!

 

[pervezjee]

Very easy solution is Delete the command button and and make the new command button to save and exit the record.

 

[Hiba87]

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

I have faced a problem; I cannot delete, save or edit data in one of the tables of my MS access under-construction database. The latter is connected to SQL. To beat around the bush, I resorted to the query design feature. But, the problem was not completely solved. I could edit some fields of that cursed table. Therefore, I created a query including the editable fields, only. But the sooner I add the un-editable fields to the query, I cannot delete, save or edit the query anymore, and I need all the fields to be included in the query. One more point, there is a relationship between this cursed table and another table within the database.

Please help.

"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."

Thanks in advance,

 

[isladogs]

This can be one of the trickiest issues to track down.
I know from experience...!
None of the suggestions I found online worked for me

If you are very lucky, it's the same issue as I had.

In my case, using a SQL Server backend, I finally realised that the cause was boolean fields for which I had neglected to set a default value.

Using local or linked Access tables, this works OK but not with linked SQL tables.
Access can't handle null values in these fields when running an update query, throws a wobbly & displays the messages you've been getting

When I finally realised the common factor, I set default values for all boolean fields in all SQL tables. I've never had this message since

HTH

 

[The_Doc_Man]

Hiba - when appending to old threads, that is technically OK but you might get faster response by starting a new thread. Just sayin', not scolding.

Just for point of reference, I will for the record correct a mistake I stated in this thread. Form_Close doesn't have a Cancel option. It is Form_Unload that has Cancel, and the Unload event precedes the Close event in firing sequence.

Back to Hiba's question:

The question is going to be WHY the fields in a table are selectively not capable of being updated or edited. What are the field types that resist change like that?

Since you said it is connected to SQL, WHICH type of SQL? There is SQL Server, MySQL, ORACLE's SQLNet, ... quite a few more.