How to overide a Write Conflict

Posted on 2009-12-17
Last Modified: 2013-11-28
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.

Question by:CRB1609
    LVL 39

    Accepted Solution

    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.

    LVL 84

    Assisted Solution

    by:Scott McDaniel (Microsoft Access MVP - EE MVE )
    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)
    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.

    Author Closing Comment

    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.


    LVL 39

    Expert Comment

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

    Happy computing!

    LVL 84
    <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.

    Author Comment


    You're absolutely right.  I'm working on a legacy database and reverse engineering it to find out what it does.   (And fix it.)
    LVL 84
    I see ... good luck with that - been there, done that, and it ain't a lot of fun sometimes!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now