Solved Run code AFTER Undo event

[dev_omi]

The Form_Undo event runs before changes to the record are undone. I need code to run after. I found a neat trick here:

Access 2007 form: event AFTER undo - Stack Overflow

Unfortunately, the code doesn't work for me. The value of the afterUpdate variable is not sustained between the two subprocedures. I confirmed this hypothesis in two ways:

1. When I removed the If-statement from the Form_Timer event, retaining the code I wanted to run after the Undo event, it worked fine.
2. I added MsgBox afterUndo to the Form_Timer event and the message box pops up blank.

What am I missing here? In the Stack Overflow example, both subs are Private. No variables are declared within or without the subs. I'm stumped.

My only guess is that they did not test their code and there is a mistake somewhere. Thoughts?

 

[moke123]

The Form_Undo event runs before changes to the record are undone.

You sure about that? I don't think I've ever used that event but it seems to me that it may fire after you use the undo method.

 

[dev_omi]

You sure about that? I don't think I've ever used that event but it seems to me that it may fire after you use the undo method.

I am, both through testing and through the tooltip thing at the bottom of the screen:

 

[The_Doc_Man]

You are correct in that the UNDO runs before undoing anything. Note that there is a CANCEL in the call for this event, so that means you can cancel the UNDO - which in turn means that the UNDO event occurs BEFORE the relevant UNDO action.

Form.Undo event (Access)

Office VBA reference topic

learn.microsoft.com

Here is the UNDO METHOD that would trigger the UNDO event and action:

Form.Undo method (Access)

Office VBA reference topic

learn.microsoft.com

The UNDO method EITHER restores .OldValue to the specific control to which it is applied (when applied to a control) or it restores .OldValue to all bound controls on the form (when applied to the form as a whole). I.e. Me.Ctl.UNDO vs. Me.UNDO

Research I have done online suggests that sometimes an UNDO is followed by update-related events even if the form is no longer dirty and thus needs no update. (Some sources suggest that if you use the generic UNDO, you should also use a .CancelEvents to block off subsequent events.) In my personal experience, I recall that after a general UNDO, the CURRENT event will fire because after that form-wide UNDO, the form matches the underlying record. I.e. the form is now "current" and thus the event.

I also have trouble discerning precisely when the UNDO event fires - other than that it would precede the Form_AfterUpdate event. There is nothing to suggest that UNDO will have ANYTHING to do with a variable's value assignment - but if there is any event that fires after the UNDO, that event could surely have an effect.

 

[moke123]

I'll rephrase that, I think it fires as a result of invoking an undo method in order to cancel the undo.

When you say the "code doesn't work for me" what do you mean?

edit: the code from stack runs fine. you just need to declare a module variable "dim AfterUndo as boolean"

 

[dev_omi]

I'll rephrase that, I think it fires as a result of invoking an undo method in order to cancel the undo.

When you say the "code doesn't work for me" what do you mean?

You can certainly do that, but my aim is not to cancel the undo.

Depending on the value of a combo box, I want the form to change visually. In particular, I want one textbox to change colors and several controls to appear/disappear. I wrote a function that carries out these changes, and I call it in the Form_Current event and the Change event for the combo box. It works great in all cases except when I try to undo a change to the combo box. In that case, the form retains the formatting of whatever was selected in the Dirty version of the record.

The reason the code doesn't work in the Undo event is that, since that event runs before the undo is carried out, the record is still dirty when the format function runs.

In my personal experience, I recall that after a general UNDO, the CURRENT event will fire because after that form-wide UNDO, the form matches the underlying record. I.e. the form is now "current" and thus the event.

Thanks for the detailed response! Unfortunately, I don't think this is right. I already have the formatting code I want to run in the CURRENT event, so if this were the case, I wouldn't be seeing the behavior described above.

However, your mention of the .OldValue property set off a lightbulb. I can just add

Me.Combo = Me.Combo.OldValue

to the UNDO event before I call the format function. That seems to work, so thanks!

 

[dev_omi]

edit: the code from stack runs fine. you just need to declare a module variable "dim AfterUndo as boolean"

I'll be honest, this is the first time I've ever heard of a "module variable". I used context clues to figure out what it is, and you're right, it works! That was the missing piece (since that is not shown on the SO response).

I was just about to post a follow-up response to this thread because, in spite of figuring out my own solution, I wanted to learn how the SO code was making use of one variable in two different subs. Now I know. Thank you!