[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 598
  • Last Modified:

How to overide a Write Conflict

I have 2 forms open at the same time, Form A and Form B.  (Form A is hidden.   Both forms contain the same field.  If I change the field on Form B and close the form and unhide Form A, I get a Write Conflict warning that another form (B) has changed a field, see attached image.

I want to ignore the warning and keep the changes I've just made in Form B.  Is there any way I can prevent this message or trap it and handle it unbeknown to  the user?

Thanks in advance.

CRB        
18-12-2009-1-11-10-PM.jpg
0
CRB1609
Asked:
CRB1609
  • 3
  • 2
  • 2
2 Solutions
 
thenelsonCommented:
How to resolve the write conflict message (or something similar): "This record has a table. changed by another user since you started editing it. ..." when only one person is using the database.

A write conflict in a form used by a single user is created when another process saves to a record while a form bound to that record is dirty.  The other process could be another form (or subform), an action query or a recordset.  For example: a user inputs data into a form bound to a table. This makes the form dirty but does not enter the changes into the table. In the code of the form, an update query changes the same record the form in bound to. With this action, a potential write conflict is created. The user then enters some additional information in the form. When the user moves to another record, the dirty record tries to write to the table but Access doesn't know which information is correct: the information in the form or the information in the table which was changed while the data in the form was being changed (but was not yet saved).  So when the form tries to write to the table, the write conflict message pops up. Notice that the problem was created when the update query ran, not when the message popped up.  

To avoid this error, you need to save the dirty form before writing to the record with another process. The best way to save a dirty form is to use the line
Me.Dirty = False
This line saves the current record and makes the form clean. It runs into fewer problems than other record save statements like RunCommand acCmdSaveRecord.  The hardest part is figuring out where to put that line. As stated earlier, it needs to go BEFORE the other process writes to the same record the form is bound to. Look for action queries and editing recordsets in your code that is running.  If you have two forms open to the same record, then, definitely, you have to save the dirty record before changing the focus to the other form.

There are two techniques that will help figure out where the problem is occurring, 1. If the write conflict message has a save to clipboard option, click on it. Open Notepad and paste in the clipboard information. The information may give you a hint of where to place the Me.Dirty = False statement. 2. Open a select query displaying the record the form is bound to. Single step through your code (or faster, run small blocks of code using Debug, Run to Curser) while watching the query for changes. A select query shows changes immediately while an opened table needs to be refreshed to show changes.

There is no need to use the if statement with Me.Dirty = False like this:
If Me.Dirty = True Then Me.Dirty = False   'NOT NECESSARY
If the form is clean, Me.Dirty = False does nothing.

References:
http://support.microsoft.com/kb/837937
http://support.microsoft.com/kb/304181
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Why do you have two forms with the same data open at the same time? This would seem to be a UI issue - that is, I can't really see any reason to have two bound forms open for the same data at the same time.

That said, you can use code such as that suggested in Nelson's comment above:

Me.Dirty = False

Or you can add an Event to FormA that can be called from FormB before you save FormB:

Function SaveMe()
  Me.Dirty = False
End Fuction

Now call that from FormB, in the BeforeUpdate event of FormB:

Sub FormB_BeforeUpdate(Cancel As Integer)
  Forms("FormA").SaveMe
End Sub

Still, the concept of having two forms open with the same data is sure to cause issues down the road ... if you could explain why this is done, we may have alterantive solutions for you.
0
 
CRB1609Author Commented:
Thank you both.  Very helpful.

Yes, two forms open is a problem.  Form A  has one field to display whereas Form B has all the fields.  The solution was to split the the conflicting information out into a separate table.

.

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
thenelsonCommented:
You're welcome.  Glad to help and thank you very much for the points with "A" grade!

Happy computing!

Nelson
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<Yes, two forms open is a problem. Form A has one field to display whereas Form B has all the fields. The solution was to split the the conflicting information out into a separate table. >

That doesn't seem to be a true "solution", unless the data was NOT normalized to start with.
0
 
CRB1609Author Commented:
LMSConsulting

You're absolutely right.  I'm working on a legacy database and reverse engineering it to find out what it does.   (And fix it.)
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I see ... good luck with that - been there, done that, and it ain't a lot of fun sometimes!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now