Multi Level Form Requery

[DSolisATX]

I'm trying to resolve a problem with a form displayed as follows

MainForm
-->SubForm1 embedded in main form
--->SubForm2 embedded in SubForm1

When I edit subform2, then return to the mainform and edit a field on the mainform, I get an error.

'The data has been changed. Another user edited this record.... Re-edit the record.'

My understanding to resolve this is to do a requery. So I added:

Me.Parent.Requery
Me.Parent.Parent.Requery

as part of the LostFocus event for subform2. However, I now get a Write Conflict error when returning to a field in the main form. I get a dialog box with the option to Copy to Clipboard or Drop Changes. I can select Drop Changes and it will let me edit the main form afterwards.

This is Access 2010 over SQL. Any thoughts on how to get the main form to accept the changes made to the underlying subform?

Thanks in advance.

 

[burrina]

Are the subforms not linked?

 

[DSolisATX]

Are the subforms not linked?

Yes, subform2 is linked to subform1 and subform1 is linked to the main form.

I have no issues moving from subform1 back to main. The issue is only when I edit subform2 and move straight back to the main form.

 

[burrina]

Try this:

Me.Dirty = False

Just asking, does the table have a primary key?
Are any of the fields bit fields?
Are your forms bound to queries?

HTH

 

[DSolisATX]

Try this:
Just asking, does the table have a primary key? --Yes
Are any of the fields bit fields? --Yes, but all NULLs are set to 0
Are your forms bound to queries? --All forms are bound to separate queries.

HTH

I tried the Dirty save, but it didn't work.

 

[burrina]

You may find this thread a interesting read. http://stackoverflow.com/questions/13993301/editing-record-issues-in-access-sql-write-conflict

And also. https://support2.microsoft.com/kb/280730?wa=wsignin1.0


HTH

 

[DSolisATX]

Thank you for that info. After reading through all that and some other forums. I found the bits of code that I need. Here's how I solved it:

 'SQL update happens here
 DoCmd.RunCommand asCmdSaveRecord
  
 Me.Refresh
 Me.Parent.Requery
 Me.Parent.Parent.Requery

The combination of the save record command and the requery for the parent forms did the trick. However, the last requery on the main form leaves the focus on the first field of the main form and I would like it to return to the last control on the subform. But, I'll make this another question on a separate post.

Thank you, for your help!

 

[Frothingslosh]

For moving the focus back to the subform, this may be of help:

http://access.mvps.org/access/forms/frm0031.htm

 

[DSolisATX]

For moving the focus back to the subform, this may be of help:

I had this saved to favorites. Very handy.