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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Coaster_brook_troutAuthor Commented:
Perfect!
0
Dale FyeOwner, Developing Solutions LLCCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.