Cancel changes in a subform in Access 2013

Question

I have a subform inside a form. By default, when I change a record in the subform, the record will automatically save when I click outside the subform. I added a button on the main form that manually saves records in the form and subform, so I cancelled saving on subform exit to avoid write conflicts.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.Undo
    Cancel = True
End Sub

With this code, clicking outside the subform will not only cancel saving, but will also clear anything I enter in the subform. This means I can't save the form without first cancelling the subform changes.

How do I cancel automatic saving in a subform while keeping any edits until they exit the main form?

PhilS PhilS · Accepted Answer · 2016-05-10T17:28:38

Me.Undo

will undo any data edits in the (sub) form. Just delete that line.

This:

Cancel = True

is enough to cancel the update.

Now the next part of your requirements is much more difficult to solve. Whenever you leave a data bound form in Access, the changes will be saved.

To prevent this you have got two options.

1.) Either you use an unbound form that is not bound to any recordsource. You need to read the the data with a query/recordset, write it to the form's controls and later write any modified values to the database with VBA. - This will not work with continuous/datasheet forms

2.) Or you create a temp table with the same structure as the main table and bind the the sub form to that temp table. When your (main) form is opened, you copy all the relevant records for the sub form into the temp table. If teh main form is closed/exited you update the real data table with any changes from the temp table.

Cancel changes in a subform in Access 2013

1 Answers