What procedure runs after a field or form undo?

[twgonder]

This one has me going nuts for a few hours now.

I have a form with some calculated textboxes, each based on what was entered in another textbox.
If the user does an esc or ctrl-z, it undoes the last change.


The value in the associated calculated textbox needs to be recalculated to return the value of the old, restored data.
I tried the textbox control On Undo event, but here I have two problems:

1. The undo procedure doesn't always run for all the text boxes. I've tested it over and over, and I don't see why it runs for one textbox and not another with an undo. The DB is too big to post here, but I've got breakpoints in the procedure, could make a video I suppose, but I doubt anyone would want to watch it without the code. Has anyone seen this problem?

2. I want to recalculate for the affected control, but in the undo, when it runs, it has the value for the control before the undo. I can't find a procedure to use after the undo, that would have the restored value of the textbox to use for the recalculate. The Form_Undo has the same problem of not having the old values restored.

The old value might be stored somewhere to use in the Undo, but then I would have to rewrite all the calculating routines to use either the current value or old value, a rather stupid exercise. I would rather find a way to use the current, restored value.

[Edgar]

Honestly, I've only ever being able to trigger a textbox undo using the escape key in the textbox where the change was made. As for the Form undo event, it triggers with escape key every time. Ctrl Z and Back button only trigger Form undo events when a change is not in the control with focus. I've never tried using these events for that reason, it's unpredictable and the documentation does not help.

[moke123]

if it's the calculated controls at issue, have you tried Form.Recalc method?

[twgonder]

@Edgar post#2
It's been kinda crazy to figure out how all the undos work. On top of that, the undo method works differently too!
The only thing I've found to do so far is clear out the calculated text boxes with the Form_Undo, and then add all the calculating calls to the Got and Lost Focus events. But, this still doesn't display properly if the undo is performed in a control that doesn't trigger the calculating calls. Hence the original question.

A solution for Access would have been (unless I'm missing something) to rename OnUndo to BeforeUndo and give us an AfterUndo event.

[twgonder]

@moke123 post #3
I say "calculated controls" to mean I've got textboxes that get updated with VBA code. But thanks, I'll go look into the controls you mention, if they are different.

Okay, I went and looked at the online help for Form.Recalc. But there isn't an example of how it's meant to work. It sounds like what I need, but I can't see how to link one text box with another for a recalculation.

[moke123]

You could write your own "Undo" procedure.

dim dict as scripting.dictionary

Private Sub RestoreFormValues()

    Dim k As Variant, ctl As Control

    For Each k In dict.Keys
        Me.Controls(k) = dict(k)
    Next

'run whatever other calcs here

End Sub

Private Sub Form_Current()

Dim ctl As Control, k As Variant

    Set dict = New Dictionary
    
    If Not Me.NewRecord Then
    
        For Each ctl In Me.Controls
            If ctl.Tag = "XXX" Then
                dict.Add ctl.Name, ctl.Value
            End If
        Next
        
  End If
    
End Sub

[twgonder]

@moke123, post#6
That's an interesting bit of code, but I don't think it will intercept a ctrl-z or esc, which are what smashes the calculated text boxes that need to be redone.

[Edgar]

Do this, I think this solves that problem. With that, I can get the textbox undo events to work and also I get key events to work. Maybe this is the missing piece. Check it out.

Private Sub Form_Load()
    Me.KeyPreview = True
End Sub

[twgonder]

@Edgar post #8
I read up on it in the MS online helps. I've got to dig into it more.
My second problem is sometimes the field undo doesn't trigger, if I recall correctly the Form_Undo always ran.
The primary problem isn't that the undo doesn't always work (although the whole undo thing in Access seems to be a mess), it's that there is no way to run the code that recalcs on calculated text boxes with the restored value after the undo has finished.

The only solution I've found so far is to ZLS all the calc text boxes and then on each Got and Lost focus, do the recalc. But that only works if the user clicks on each control. This means that sometimes I'm running the same code three times (Got, Lost focus and AfterUpdate) on each control that impacts the calculated text boxes.


The textbox for Name suffix was changed from nothing to "Jr.", and the second gray calculated textbox had Jr. added at the end.

We then press esc to remove the recent change, and we are currently in the Salutation name format text box (or any control could have focus) when we press esc, so we aren't in the Name suffix text box to force a recalc. The two gray boxes for name format have to be recalculated (not just the one for Correspondence name format) for the removal of "Jr.".


Here we are left hanging with the calculated textboxes (shown in gray) being empty (due to code that ran during Undo), and no way to trigger a recalc of all three after the Undo. I'm loathed to add a recalc button simply because I can't find a procedure to run for the recalc after an undo (if Access has one).

How can we do that after an undo (no matter what control we are on, and without having to tab through all the controls that would trigger a recalc with AfterUpdate (but they won't unless a value is changed (or we do Got and Lost focus all over the place), which we might not want to do)?

[Edgar]

I honestly don't know. If I really wanted to know what's going on, I would debug.print the name of every single event name in the event lists of every single control involved, including the form, in order to discard there's absolutely nothing else to do. If there's nothing left to do, I'd either remove the feature or do it in some other way, even if it means adding a recalc button. There are extra things you can try, though, you could store the state of all controls on each control's Got Focus or Enter (or whatever is fit), and compare the state on Lost Focus or Exit (or whatever is fit), and do what corresponds.

There are things Access does and there are things Access does not do. Don't feel bad for patching what is not even documented.

[twgonder]

@Edgar, post #10
Thanks for looking it over. I've been busy, and hoped someone here would have seen this problem before. I've not had the time to go create a simple form with one calculated field, and then build a Stop into every procedure and test, but I guess it's time to do that. If one can't be found to trigger AFTER an undo, it's just another one of those gaping holes in Access.

[Minty]

Are the calculated boxes bound to a calculation in the underlying form query or based on the other controls on the form?
You may find one will simply re-caclulate on its own, whereby the other one won't?

[twgonder]

@Minty, post #12 As shown in post #9, several text and combo boxes combine to fill in two calculated textboxes on the left.
The bottom right calculated textbox shows the full name of the entity in the control above, which contains an ID to the same table.
There is no underlying query. It's all VBA code.

[Minty]

If they were calculated in an underlying query I think they might recalculate automatically, although without testing I can't confirm that for sure.

[twgonder]

@Minty, post#14 There's about 100 lines of intricate code running to fill the calculated text boxes. I don't think a query is going to handle that well. To me, that's why forms have procedures and unbound text boxes. Which is what the original question is all about, getting some after undo logic to run, so those 100 lines of code can do their job.