Alternatives to DoCmd.RunCommand acCmdUndo

The DoCmd.RunCommand acCmdUndo in Access only works on the current form or subform fields, and of course the subforms may have several records related to the main form.  Is there a way to “undo” or cancel changes made throughout the form and all subforms?  Or is this just an intensive coding process?

Thanks!
Coaster_brook_troutAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
One further option is to use Temporary tables that can be filled with data, and presented to the users on the forms. The user would then explicitly click a Save button, and you'd run code that would move the data back from the Temporary tables to the Live tables. This is also quite code intensive, but it's about the only way you can allows users the ability to save changes to multiple related tables in a bound Access application.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Moving from a Parent form to a Subform will trigger a save on the Parent form, and once that's done you cannot undo those changes.

Moving from a Subform back to a Parent form will save the data on the Subform, and the same caveat applies - can't be undone via the RunCommand method.

You can "cascade delete" data by setting this up in Access's Relationships window. Right click on the join line (the line that connects the table in that view) - but be aware that doing this will delete ALL child records if a Parent record is deleted (which should happen anyway). In my view, cascade delete is not a good setting to implement, but everyone has different opinions on this.

Otherwise, you're left with coding the delete process yourself.
0
 
Coaster_brook_troutAuthor Commented:
Perfect!
0
 
Dale FyeCommented:
I frequently use the temp table method mentioned by Scott, when working with subforms where users need the ability to cancel changes.  There are a couple of issues:

1.  Creating a couple of queries to populate the temp tables
2.  Creating a couple of more queries to update the source tables.
3.  In a multi-user environment, determining whether the source record was changed by someone else during the time you were editing data.  When I'm doing this type of thing in a multi-user environment, I include a LastModified field in my source tables.  I copy that field over into my temp table, and before I write my updates to the source table, I check to see whether the LastModified value in the source table is greater than in my temp table.  If not, I write my updates to the source table and set the last modified date/time to Now().  However, if the source record has been updated, then you have to decide how you want to handle the differences between your temp table data and the new source table data.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.