Skip to main content
Microsoft 365
Subscribe
3 min read

Use code to “undo” things in Access

By

One of the more common tasks on a computer is to undo something you just did by mistake. At least, it’s one of my more common tasks. CTRL+Z is my friend.

But not everyone uses keyboard shortcuts – that’s why there’s an Undo button in the Microsoft Office user interface. Trouble is, sometimes folks just don’t see it. Moreover, in Access it’s possible that they can’t use the toolbar because they are working on a modal form. Fortunately, you can create an “undo” button for any form in Access. And in most cases, you can control what happens when the Undo command is invoked – for example, ask for confirmation before undoing the current record on a huge data entry form.

Create an Undo command button

There are a couple of ways to make an “undo” button, but they all start the same way: add a command button control to your object, and give it an obvious label. Then, add some code to the button’s Click event.

One way is using the DoCmd object’s RunCommand method: 

Private Sub btnUndo_Click()

DoCmd.RunCommand acCmdUndo

End Sub

This code has the same effect as pressing CTRL+Z or clicking the Microsoft Office Undo button. Thanks to your big, obvious button, undoing has never been easier.

Another way is to use the form’s Undo method:

Private Sub btnUndo_Click()

Me.Undo

End Sub

When you use the Undo method, you always trigger an Undo event, giving you another opportunity to control the flow of your UI. For example, you might add a confirmation message to the Undo event for a form that has a lot of data controls, as a safeguard for data entry. (VBA gurus –  can you think of any circumstances where the DoCmd.RunCommand method of “undo” would not trigger the Undo event? What would be some of the merits and drawbacks of these two “undo” methods?) 

NOTE: If you’re working on the design of a web form, you need to use macros instead of VBA. To create an “undo” button for a web form, embed an UndoRecord macro action in the button’s On Click event.

Undo method

The Undo method can be attached to an event for a form or a control. You can apply the Undo method to a form, and to the following controls:

  • CheckBox
  • ComboBox
  • ListBox
  • NavigationControl
  • OptionGroup
  • Textbox
  • ToggleButton
  • WebBrowserControl

To apply the Undo method to a form, use this code:

Me.Undo

To apply the Undo method to a control, use the following code (replace <NameOfControl> with the name of the control):

Me!<NameOfControl>.Undo

The Undo method offers an alternate way to make “undo” command buttons on forms, instead of using the DoCmd method. For example, if you want to put a button on a modal form so there’s an obvious way to undo data changes, the button’s OnClick event procedure might look like this:

If the Undo method is applied to a form, all changes to the current record are discarded. If the Undo method is applied to a control, only the control itself is affected. 

Undo event

Every form has an Undo event, as do the following controls:

  • ComboBox
  • NavigationControl
  • Textbox

The Undo event occurs whenever the user clicks the Undo button, presses the ESC key, or performs an action that calls the Undo method of the object. Note that a control must have focus before this will work.

The Undo event for controls occurs whenever the user returns a control to its original state (while the control has focus) by clicking the Undo Field/Record button on the command bar, clicking the Undo button, pressing the ESC key, or calling the Undo method of the specified control. Notably, the event does not occur if the user clicks the Undo Typing button on the command bar.

Can you think of some good examples of using the Undo method and the Undo event? How about best practices for using them? Please do share.

–Steven Thomas