Access Subform, Stop automatic save to linked table when selecting next record

Question

I have a main form and a subform within access linked to a table and query respectively.The subform narrows down a list of results based on the current record in the main form as it is linked by a master and child field. My problem is that I want users to edit the data in the subform only and then press a save button when they are happy with all the edited records. Currently it automatically saves to the table it relates to when switching to the next record in the subform. This is not ideal as I would like users to be able to revert to the previous data or not save if they're not content with the changes.

https://drive.google.com/file/d/0B5BV2rpgshPJSG1jS0lzUVNnWlU/view?usp=sharing

Unfortunately, that is a default functionality of MS Access forms. The other way is to use unbound forms, populate form fields from say a recordset and on the save button updates table. A tedious process with quite a bit of VBA code. — Parfait

Andre Andre · Accepted Answer · 2016-08-26T05:10:26

As Parfait wrote, this is how bound forms work. Moving to a different record saves an edited record, no exceptions.

With a continuous subform (= showing multiple records), you can't make it unbound - unbound forms are single forms by definition.

Your only option is to copy the records to a local temp table with the same structure as the original table, and bind the subform to the temp table. Then saving the changes means copying the temp data back to the original table (but watch out for colliding edits from other users!), discarding the changes is simply clearing the temp table.

Or better: explain to your users how it works, and they will get used to it.

Access Subform, Stop automatic save to linked table when selecting next record

1 Answers