Write Errors

[StuartG]

Hi All,
I just wanted to check on something, after the below:

Cause
This behavior occurs when you open two forms that update the same data source at the same time. The first form puts an edit lock on the record or records, and then the second form changes the record or records and saves the changes. When the first form tries to close and write back to the table, the changes that were made by the second form are detected. This causes the error to be returned.

I have two forms that connect to one table, could this be the issue?
So if I move one of the forms to its own table i shouldn't experience the issue?

 

[Uncle Gizmo]

>>> So if I move one of the forms to its own table <<< well, if it's an identical table, then that's not a good idea. You should keep everything that's the same in one table.

 

[StuartG]

>>> So if I move one of the forms to its own table <<< well, if it's an identical table, then that's not a good idea. You should keep everything that's the same in one table.

Just so I get this.
So let’s say I have one table..
tblsalesdata

Connecting to that tbl are:

Frmcomments
frmother

But the frmcomments is on the same as frmother and the users click a box to add a comment, which opens frmcomments leaving frmother open in the background, this is when it errors..

Data for frmcomments is store on tblsalesdata.

Hope that’s not too convoluted?


Sent from my iPhone using Tapatalk

 

[isladogs]

Access is trying to be helpful by protecting your data
You need to get rid of the causes of the write conflict error.

Suggest you make FrmComments a subform of frmOther linked by parent/child field(s) & don't include the same fields on both.
Or close the main form when frmComments is open then reopen the main form when done

You could also use a popup form for frmComments.

Finally you can look at changing the record locking used. If you do so, consider the possible implications first

 

[Minty]

If you wanted to store multiple comments related to a sales order a better way might be to create a second table tblSalesComments - then link that back to your main sales "header" record.

This would remove the problem and give you scope to record who and when made the comments. As the comments would only be added to you won't get a write conflict.

 

[StuartG]

Thanks all.

What I have done is taken the tip from Isla.

I have used Macros that when the users wants to add a comment, they click a button which closes the main form, and takes them to the comments form, once the comment is added they have the option to save and return to the main form or just save and close.

This appeared to work ok, perhaps when I come to complete a re-write i will do it in seperate tables..

 

[isladogs]

You will need separate tables if more than one comment is possible for each record.
However, if you are using column history as indicated in your other thread, that may not be needed.

Recommend you do this using VBA instead of macros!

 

[StuartG]

You will need separate tables if more than one comment is possible for each record.

However, if you are using column history as indicated in your other thread, that may not be needed.



Recommend you do this using VBA instead of macros!

Thanks for the tip Isla..
Column history works currently as the user deletes the previous comment before adding another.. as you know everything the user does in my dB is dictated by the record ID..

I’d love to use VBA to do certain things but when I attempt it, it don’t work, probably because I don’t get it..


Sent from my iPhone using Tapatalk

 

[Pat Hartman]

Having two forms bound to the same record open concurrently can be problematic if you do not understand what you are doing. Usually this happens when you have a list form and a details form. You pick an item from the list form and open the details form so that you can see all the columns and make it easier to make modifications. The key to making this work is to NOT dirty the list form. In fact, I frequently make them not updateable at all so that this conflict doesn't happen. To me it sounds like you have code that is modifying the record in the first form and that is what is causing the conflict. If the first form doesn't dirty the record, the second form opens and the user can make his changes and save them without raising any error.