"The data has been changed" error when stepping from main form into sub form

Question

I'm migrating an Access database to SQL Server using the SQL Server Migration Assistant (SSMA). The Access application will continue to be used but with linked tables instead of local ones.

I've run into a problem during the post-migration testing with a form that contains several sub forms.

Testing steps:

1) Edit a field in the main form;

2) Shift focus to a field in the sub form;

3) Attempt to edit the field in the sub form.

Result: An error message pops up: "The data has been changed. Another user edited this record and saved the changes before you attempted to save your changes."

Once the error message is dismissed the field in the sub form can be edited. If the field in the main form is not edited the sub form can be edited without the error message.

Any ideas about what could be causing this error?

I've tried saving the main form record in the Enter event handler for the sub form control on the main form (ie this event happens on the main form, when entering the control that contains the sub form, not on the sub form itself). Doesn't make any difference. I tried requerying the main form in the same sub form control Enter event but that doesn't work - requerying the main form moves the focus away from the sub form so it can't be edited.

An MS forum suggested Me.Parent.Requery in the After_Update event of the sub form. That didn't work either.

SQL Profiler shows a single update statement, updating the table underlying the main form, when I step into the sub form. There are no other statements hitting the database that modify data.

One interesting thing I've noticed: The Record Source for the main form is actually a select statement that joins two tables together. The main form contains fields that can update columns in each of the tables in the Record Source. Editing fields in the main form that update the child table in the relationship do not cause the "data has been changed" error. The error only occurs when editing fields that update the parent table in the relationship. I've tried fields that update different columns in each of the two tables. The results are consistent: Editing the record in the parent table causes the error, editing the record in the child table does not.

The link between the sub form and the main form joins a column in the sub form table to a column in the child table in the main form's Record Source.

By the way, the tables in the main form Record Source are actually joined in a 1:1 relationship (one record in the child table for every record in the parent table). The child table is just an extension table for the parent table.

I personally wouldn't design the system like this if I was starting from scratch but it's what I've got to work with and I'm hoping there is some reasonably easy fix that won't require a major redesign of the tables or forms (given the main form and sub form each have over 100 controls).

Unstep

Have you tried creating a View on the SQL Server that returns the same columns as the main form's current Record Source, linking that view as a "linked table" in Access, and then using that as the Record Source for the main form? – Karmenkarna

The 1:1 relationship seems suspicious to me since the error you are getting reflects a concurrent user failure most likely issued by ODBC. Perhaps you can try dropping the 1:1 relationship just to see if that isolates the error. – Nebuchadnezzar

@Gord Thompson: Tried it, using an Instead Of trigger to deal with updates through the view. Found I had to add a unique index to the linked table on the Access side to allow the records to be updateable. Form works exactly the way it did originally, with the error dialog when I update fields that map to one SQL Server table, but no problems when I update fields that map to the other table. When I dismiss the error dialog I can then update the the fields mapping to the problem table, as before. So now I suspect it must be something to do with the properties of the individual form controls. – Unstep

Answer 1

After much trial and error I solved the issue. In the enter event handler for the sub form control on the main form, I requeried the sub form itself.

eg On the main form:

Private Sub Subform1_Enter()
    Me.Subform1.Form.Requery
End Sub

I don't know why this works, only that it does.

Unstep

I had the reverse problem: I got the error when going from the subform to the main form. So in the Subform1_Exit() event I put Me.Requery and it worked. – Blender

Answer 2

This happens when a record is updated in a table, but the record source of the main form has not been refreshed to reflect the change, so Access gets conflicting information and thinks the record has been changed. See also: http://support.microsoft.com/kb/302492

Tolle

Reading that support article it looks like I had almost the reverse problem: I edited the main form first before moving into the subform. I suspect I could have modified the answer from that article to fix my problem: In the AfterUpdate event handler for the control in the main form I could have added Me.Subform1.Form.Requery. I haven't tried it but I suspect it would have worked as well as adding that line to the subform Enter event on the main form (which was what I did to solve the problem). – Unstep

Answer 3

I solve this problem via writing AfterUpdate form-event like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    cSQL = "update UnderlinedTable set Field1=" & Me.Controls("Field1") & _
        ", Field2=" & Me.Controls("Field2") & _ ' and all other fields in your form
        " where PrimaryKey=" & Me.Recordset.Fields("PrimaryKeyField")
    ' here command to SQL server that executes this cSQL string
    Me.Requery
    Cancel = True 'stop Access updating
end sub

It is possible to be wriiten universal BeforeUpdate form-event function that generates automatically Update statement based on form.recordsource and changed form fields that can be invoked from all AfterUpdate form-events passing form as parameter. I have made this for me.

Figment