Undo form/subform changes

Question

Hello. So, I have a form and subform for creating a new record, and I want basically a save and exit, and exit w/o save button. I have the save button fine. I was using the delete record command for the exit w/o save button, because if they put anything into the subform, it creates a record in the table the main form is linked to. But deleting a record doesn't work if there's no record of course. I tried Me.Undo for the main form, but that doesn't get rid of the subform stuff, and either I did something wrong or undoing the subform didn't work either.

I also have a form for editing existing records created by the previously mentioned form. And deleting a record wouldn't be good there, because it would delete the record you're trying to edit, rather than just undoing the changes you tried to make. Also, if you change anything in the main form, then go mess with the sub form, it saves those changes (right?) in the main form. Meaning you've lost the original data if you want to exit without saving.

What's the best/right way to handle undoing for both the new record creation form, and the existing record editing form? Did I mess something up with Undo to cause it to not work, or is there another way? I read some post talk about using temporary tables in between saving things to the primary table, but I'm not sure if that's right, or the proper way to set that up.

Answer 1

If you really need to exit a form without saving the entered data then, maybe, an unbound form would be better.

I'm curious, though, about why you are using a main form and subform to enter data. I would be more inclined to hide the subform and activate it only when the record in main is saved.

But if you are going to use an unbound form, then I would not use a subform, but rather hide the controls for the child table until you commit the main record.

If you need further clarification on this please let me know with more info about the tables involved.

Answer 2

Hello Rocky

I’m Adeyemi and I’d be happy to help you with your question.

One way to handle undoing changes in both the new record creation form and the existing record editing form is to use temporary tables. Temporary tables are tables that exist temporarily on the SQL Server and are useful for storing immediate result sets that are accessed multiple times as seen here: https://www.sqlservertutorial.net/sql-server-basics/sql-server-temporary-tables/..

You could use temporary tables which are exact recreations of your live tables, and bind your forms to them. When the user needs to edit data, you fill those temp tables with the data, and then open your form. When the user finishes editing (or adding), you write the data back to the live tables. This gives you complete control over when/how your data is saved, but it comes with a good bit more work as seen here: https://answers.microsoft.com/en-us/msoffice/forum/all/roll-back-main-form-and-subform-changes/071eae0d-8bc2-4bb6-93fb-9e283016b78d.

Another option suggested in the community post is to use the Me.Undo method for both the main form and subform. However, when you're working with subforms, Access will save the main form when you first move into the subform, and you can't undo the main form. You can generally undo the subform - or at least the record you're working on in the subform, if you have a datasheet or continuous form.

I hope this information helps!

Regards Adeyemi