• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 44
  • Last Modified:

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 Brown
Andy Brown
4 Solutions
Gustav BrockCIOCommented:
Using a temp table is a valid and proven method.
If it works, I would move on.
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"
[ https://msdn.microsoft.com/en-us/library/bb243806(v=office.12).aspx ]

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

"Workspace.BeginTrans Method (DAO)"
[ https://msdn.microsoft.com/en-us/library/office/ff821457.aspx ]

"Workspace.CommitTrans Method (DAO)"
[ https://msdn.microsoft.com/en-us/library/office/ff835985.aspx ]

"Workspace.Rollback Method (DAO)"
[ https://msdn.microsoft.com/en-us/library/office/ff845335.aspx ]
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

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

Join & Write a Comment

Featured Post

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now