"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).

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.

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