Adding a cancel function to a MS Access, settings form

I have an Access database, containing a single table that has all of the settings needed for the application.  This table has several categories (Reports, User preferences etc).

Administrators are able to go into a form and make changes to those settings as required.  However, if they make a mistake and wish to cancel those changes - they need a clean route to reverse.  I am currently using a temporary table, which is OK, but I want to know if there is a better way that doesn't evolve copying data across to the temporary table first.  

I was thinking of something such as setting the forms recordsource when it is opened to a clone of the actual live table but have never worked with clones, so don't know if; a: it's possible or, b: it's a good way to do it.

As always - I'm open to suggestions and any guidance is greatly appreciated.
Andy BrownDeveloperAsked:
Who is Participating?

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

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.

Gustav BrockCIOCommented:
Using a temp table is a valid and proven method.
If it works, I would move on.

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
An alternative, assuming you only have one table is to use the before update event to confirm the data save:
Private Sub Form_BeforeUpdate(Cancel as Integer)
       If msgbox ("Do you really want to save these changes?", vbyesNo) <> vbyes then
              Cancel = true
               MsgBox "Changes cancelled"
            MsgBox "Changes saved"
      end if
End Sub

Open in new window

The advantages of the temp table method over this are that it is more forgiving, and it works well if you have related tables with child records.
[ fanpages ]IT Services ConsultantCommented:
You could enclose the maintenance of the (original, not copied/temporary) table within a Transaction.

If the Administrators wish to confirm the changes (with the provision of a prompt to request confirmation) the Transaction is Committed.
If the changes are to be disregarded/ignored, then a Rollback of the Transaction is performed.

The following article was published when MS-Access 2007 was the latest MS-Access database available, but the principle is the same so should offer an overview of the code statements required:

"How to: Use Transactions in a DAO Recordset"
[ ]

The Microsoft Data Access Objects reference for Workspace Object Methods for MS-Office 2013 onwards is also available.

"Workspace.BeginTrans Method (DAO)"
[ ]

"Workspace.CommitTrans Method (DAO)"
[ ]

"Workspace.Rollback Method (DAO)"
[ ]
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

I would also use the BeforeUpdate event.  This event is the key to controlling updates.  Think of it as the flapper at the end of a funnel.  If you want to block the save of the data, you simply cancel the event.  The undo in mbizip's code backs out the changes and makes the record not dirty.

If you want a way to recover from them saying yes when they meant no, keep a log.  There are several ways to handle this.  Simplest is probably to run an append query in the BeforeUpdate event to copy the existing record and add it to the log before the changes are saved.  Then you have to give the users a way to retrieve either the last set of settings or allow them to pick a set of settings to apply.
John TsioumprisSoftware & Systems EngineerCommented:
maybe you should think "revisions" and have each time you make a change a revision is made so the last is the active...if something goes wrong you just go back and change the active...
Andy BrownDeveloperAuthor Commented:
Thanks guys - I should have mentioned that it is running on a continuous form - so there are multiple entries.  But, upon further research, it still looks like the original idea of using a temporary table is the best one.

Thanks again.
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

From novice to tech pro — start learning today.