Is DoCmd. RunCommand acCmdUndo reliable?

[Jon Lewis]

I've always used something like below as standard practice in the Before
Update event of forms:

Dim Response As Integer
Response = MsgBox("Save record changes - Confirm?", vbYesNoCancel +
vbQuestion)
If Response = vbNo Then
DoCmd.RunCommand acCmdUndo
ElseIf Response = vbCancel Then
Cancel = True
End If

However whilst developing an A2K app with several layers of subforms I've
noticed that occasionally when No is chosen when changes have been made to a
nested subform record, acCmdUndo just doesn't work. Changes are saved. I
haven't yet been able to figure out exactly what sequence reproduces this
but it is definitely periodically happening. It may be when first of all
Cancel is chosen and then the Before Update is triggered again with No
chosen. Anyone noticed this before?

TIA

 

[aaron.kempf]

I don't trust anything like that; I use 'working tables' that are
filtered for a particular SPID or SUSER_SNAME so that it's easy and
scalable and multi-user

Access MDB isn't reliable enough for real world usage

 

[Arvin Meyer [MVP]]

I use:

Me.Undo

just because it is simpler, and as such, probably more reliable. The problem
you are experiencing is not your code, it is the existence of the subforms.
As you move to or from a subform, data in the form or subfrom that you are
moving from is saved. Your code yries to block that, but will only succeed
if you also put it in the BeforeUpdate event of all the forms and subforms.

 

[Jon Lewis]

Good suggestion Arvin. I suppose Me.Undo is being specific about what you
want to undo. I'll see if it is more reliable.

Many thanks

Jon