MS access VBA - me.undo stops afterupdate event

Question

I'm using a combobox in MS Access to navigate to a data entry and want a msgbox to ask the user whether changes made to the current entry should become saved, undone, or whether the update process should be canceled. The code I run as BeforeUpdate looks as this:

If Me.Dirty = True Then
Select Case MsgBox("Keep changes?", vbYesNoCancel)    
Case vbYes
DoCmd.Save

Case vbNo
Me.Undo

Case vbCancel
Cancel = True
End Select
End If

The options Yes and No are working as intended. Though, for some reason I just can't wrap my head around, the Me.undo prevents the event to update as if Cancel would be set to True - meaning: The form stays at the current data and is not going to the select data.

The AfterUpdate event for the combobox simply runs

DoCmd.SearchForRecord , "", acFirst, "[ID] = " & Str(Nz(Screen.ActiveControl, 0))

I also tried to manually control Cancel = False without having any effect.

First of all only two options. Either save or don't. 2> use form_before_update event. — Krish
Well, i think this is a matter of personal taste. My first idea WAS to do exactly that and add an "undo"-button to the form. Still, it should be possible the way I now try to do it. — Thuro G
I expect because if you UNDO there is no change to data therefore nothing to update and the event aborts. Apparently AfterUpdate does not trigger if BeforeUpdate does not follow through. Why do you think 'should' be possible? There can be only two paths - as @krish KM said - save or don't save. — June7

Thuro G Thuro G · Accepted Answer · 2017-04-11T09:44:51

I expect because if you UNDO there is no change to data therefore nothing to update and the event aborts

Apparently, this was exactly the mistake in my approach. I now simply moved the whole thing to the AfterUpdate and it is now working smoothly.

If Me.Dirty = True Then
Select Case MsgBox("Keep changes?", vbYesNoCancel)
Case vbYes
DoCmd.Save
DoCmd.SearchForRecord , "", acFirst, "[ID] = " & Str(Nz(Screen.ActiveControl, 0))
Case vbNo
Me.Undo
DoCmd.SearchForRecord , "", acFirst, "[ID] = " & Str(Nz(Screen.ActiveControl, 0))
Case vbCancel
Cancel = True
End Select
Else
DoCmd.SearchForRecord , "", acFirst, "[ID] = " & Str(Nz(Screen.ActiveControl, 0))
End If

Might be not the cleanest solution, but it is doing what it should.

MS access VBA - me.undo stops afterupdate event

1 Answers