How to Resolve Write Conflict w/ Unbound Access Form

Question

Have searched around and not yet found a solution that seems to fit. Perhaps someone here can offer some ideas.

Background

Application built in MS Access 2010 with tables linked to Access 2010 database, may eventually link.

Form contains an 'edit' section (unbound) and a 'list' section (bound sub-form).

Many forms of this type are used throughout the application.

All forms use a common UpsertRecord() method to insert/update records based on their respective fields.

Issue

When the 'Save' button is clicked, the Write Conflict error is displayed.

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 line triggering the error is where the form recordsource is set to a SQL string.

.RecordSource = strSQL

My Interpretation

Although the form could directly update the bottom subform list without clicking 'Save', this is not desirable to avoid users inadvertently changing data.

So, I'm attempting to force the 'Save' through the click event which in turn calls the common public UpsertRecord() method for saving the data. That method works fine, however triggers the Write Conflict since the form believes it is also changing the data by itself (w/o the 'Save' click).

Solutions Attempted

I've attempted to set a private variable m_SaveOK to prevent saves w/o the button click event by using the Form_BeforeUpdate() event with the code below however that also triggers the Write Conflict.

If Not m_SaveOK Then
    Cancel = True
End If

Also tried to save the form data prior to doing the qry.SQL direct save, however this too failed. That code was basically a check for dirty and forcing a form save using frm.Dirty = False prior to the direct save.

If frm.Dirty Then
    frm.Dirty = False
End If

Form Image

This is what the form looks like. Clicking the pencil (edit) button of a record in the subform populates the top controls.

When the 'Save' button is clicked, the record is saved to the database and the form is requeried to refresh the subform list.

Anyway, I'd appreciate some ideas on how to prevent the form from attempting to save the record vs. the Save button click event.

Thanks in advance for your help!

UPDATE

Subform properties are set to not allow additions and edits (delete is okay since that's what the 'X' button is for in the list), and no locks is set.

After adding some debug.print statements evaluating frm.Dirty and frm.subform.Form.Dirty I've narrowed it down to the main form versus the subform being Dirty and causing the conflict.

Underlying tables are properly updated using the 'Save' button, however the form remains Dirty. So although I'm not currently triggering the Write Conflict error, the Dirty state causes other errors & undesirable behavior on form exit -- You can't save this record at this time... and 2101 The setting you entered isn't valid for this property. when I add a frm.Dirty = False after the button triggered save.

So I'm still working through the issue and would appreciate any other ideas.

Answer 1

For the subform the properties Allow Additions, Allow Deletions, Allow Edits should be set to No and Record Locks to No Locks. This way the subform should not be able to interfere anymore.