Dreaded 'Write Conflict' error after adding a new field to Access form

[ricardocpa]

I am working on a local copy of an Access file that only operates with tables linked to a SQL server.

This file works with no issue, but recently a new table 'Subbasin' table has been added, and a related field has also been added to an existing parent table 'Facility':

My goal is to add, in design view, the new field 'Subbsasin_code' in an Access form so that the value for Subbasin_Code can be seen/modified by the user. This form displays one Facility record at a time:

The issue is that after the text box for Subbasin has been added to the form, in Form View, making modifications to any field in the form will trigger a "Write Conflict" error (the kind where the option 'Save Record' is greyed out) when trying to save the modification. Please note that the buttons in yellow all work with underlying VBA scripts.

Things that I have already checked/tried to do based on other threads found here in AccessWorld, without same 'Write Conflict' outcome:

1. Checked that the new child table 'Subbasin' is properly defined, meaning:
   • There is a unique identifier field defined as primary key (PK)
   • There are a modified by (user) & modified at (datetime) fields
   • There are no Boolean fields - besides the stated above, there is only nvarchart-type fields
2. Refreshing (Home > Refresh All)
3. Relink Tables from Access:
   • Tried relinking only these 2 tables
   • Tried removing and re-link all tables
4. Reviewed the VBA script set for the 'Save button' - this is the part I am the least familiar with:
   • How can I detect which step of the script is triggering the error, if any?
   • In the script, it establishes a variable in order to compare the access record being modified against the SQL database

     Dim rs As DAO.Recordset

     how can I evaluate the value or list of values in this variable, to see if there is any field/value missing?
   • Does DAO.Recordset collect all field/value pairs by default or is that defined somewhere else?
   • I assume an error is taking place because a error dialogue appears after the "Write Conflict" one, which matches the Else condition set for errors ≠ 2291, am I correct to assume that?

anErrMod8:    If err.Number = 2291 Then
        MsgBox "An Error Occurs During Save Record... @@ " & err.Description & vbCrLf & " For example Address may not match Owner", 16, "Error..."
    Else
MsgBox "An Error Occurs During Save Record... @@ " & err.Description & vbCrLf, 16, "Error..."
    End If

.

Anyway, I am at a dead end, any help or guidance will be so appreciated - let me know if I can expand on any of the issues at hand. Thank you!

 

[theDBguy]

It's not clear how exactly you added the new column to the form. To which table is it bound? Are you using any code behind the new textbox? If so, that shouldn't be necessary. Also, when exactly are you getting the error message? Was it every time you make a change to a textbox or only when you click on the Save button?

 

[Mark_]

Please note that the buttons in yellow all work with underlying VBA scripts.

First question would be "Why are you not using a bound form"?

 

[ricardocpa]

It's not clear how exactly you added the new column to the form. To which table is it bound? Are you using any code behind the new textbox? If so, that shouldn't be necessary. Also, when exactly are you getting the error message? Was it every time you make a change to a textbox or only when you click on the Save button?

Thank you @theDBguy - The form is bound to the Facility table. I am not using any code behind the new textbox, as you guessed with your question the error only appears when clicking on the button 'Save'

 

[ricardocpa]

First question would be "Why are you not using a bound form"?

This is a great question - although I believe the form is bound to the Facility table

 

[Mark_]

This is a great question - although I believe the form is bound to the Facility table

If the form is bound to Facility, what code does the "Save" button run?

 

[ricardocpa]

If the form is bound to Facility, what code does the "Save" button run?

Mark, the Save button runs a private sub that checks some mandatory fields have a value and then performs a Save Record command if the record state is dirty, as follows. My guess is that an error occurs during DoCmd.RunCommand acCmdSaveRecord. Again, this sub runs fine in the previous version before the new field 'Subbasin_code' was added to the form

Private Sub btnSave_Click()
Dim RecordNum, j As Integer
Dim rs As DAO.Recordset

On Error GoTo anErrMod8
showStatus "Updating Record..."
 
  If RequiredFieldEmpty(reqField) Then
    MsgBox "Required Field can't be NULL or EMPTY... @@ Enter a correct value and click SAVE again.", vbCritical + vbDefaultButton1, "Error..."
    GoTo anExitMod8
  End If
   
  If Dirty Then
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.RunCommand acCmdRefresh
  Else
    showStatus "Nothing was saved."
    Exit Sub
  End If
   sCriteria = sPK & Me!FACILITY_CODE
 
 
anExitMod8:
    Locked = False
    showStatus "Ready."
    Exit Sub
anErrMod8:
    If err.Number = 2291 Then
        MsgBox "An Error Occurs During Save Record... @@ " & err.Description & vbCrLf & " For example Address may not match Owner", 16, "Error..."
    Else
        MsgBox "An Error Occurs During Save Record... @@ " & err.Description & vbCrLf, 16, "Error..."
    End If
    Resume anExitMod8
End Sub

 

[Mark_]

Pat pointed me to a cleaner way of doing this. Validate in the BeforeUpdate event.

 

[ricardocpa]

I apologize, @Mark_ , I am not versed in VBA at all, could you please expand on "Validate in the BeforeUpdate event"?

 

[Mark_]

I'd suggest at looking how events work in Access
After reading that, I'd suggest reading MajP's post that give a much better understanding of what to do with them.

In short, before you let a user actually update data, you make sure it is correct. BeforeUpdate happens BEFORE the value is UPDATED. In a bound form the changes are saved in an existing record as you make them.

 

[Gasman]

Review this thread.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

 

[The_Doc_Man]

A "Write Conflict" error says that the record you were trying to write no longer matches what is in the underlying table.

Editing Record issues in Access / SQL (Write Conflict)

a problem has come up after a SQL DB I used was migrated to a new server. Now when trying to edit a record in Access (form or table), it says: WRITE CONFLICT: This record has been changed by another

stackoverflow.com

Here's how that works. Your bound table has a recordset underneath it, identified by form's .RecordSet property, which normaly gets reloaded by navigating to the current record, whatever it is. The recordset looks at a record by copying it from the DB file - and it keeps track of what was in that record because a recordset, mechanically, is like a snapshot. It is a picture at a point-in-time corresponding to the time when you navigated to that record.

But now you go changing something in that record through an alternate path and the table gets updated. Remember that the Access form holds a copy of what the recordset USED to contain. When you go to update the bound record through standard update methods, it has been changed out from under you. Again, speaking mechanically, I believe that the snapshotted record gets invalidated by the non-standard update.

Therefore, the real problem is that by changing the underlying table in TWO different ways - one bound and one unbound, or one standard way and one non-standard way - you have what appears to be someone overwriting the bound record. But in this case, the someone is YOU.

Note that, in theory, it really COULD be someone else overwriting the record, but from the context, I don't think that is what is happening in your case.