I get three different behaviors using undo. Help?!

[dantebothermy]

In the data entry area of my access application, I give users an opportunity to undo. The same code gives me three different behaviors.

When I first test it, it works fine. But if I edit the field again and click elsewhere, I get an error that "the command or action undo isn't available". And if I cancel the error message dialog box and edit the field again, click elsewhere. This time the undo deletes the entire edited field.

Please help.

All of the data and the code are in a subform:

Private Sub Form_AfterUpdate()
Dim intYesNo As Integer
intYesNo = MsgBox("Yes to Save, No to Delete Record", vbQuestion + vbYesNoCancel, "Enter")
If intYesNo = vbYes Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
ElseIf intYesNo = vbNo Then

'when it crashes, the debugger brings me to this line
DoCmd.RunCommand acCmdUndo
End If
End Sub

In a different iteration, I tried Me.undo in the subform, instead of acCmdUndo. When I test it in the subform as a free-standing form it works fine. But when I test it in the subform as part of the "master form" it does NOTHING.

[puppydogbuddy]

Try changing the event proc:

From this: Private Sub Form_AfterUpdate()

To this: Private Sub Form_BeforeUpdate()

AfterUpdate is after changes have been saved, and you can't undo after the change has been saved.

[dantebothermy]

Try changing the event proc:

From this: Private Sub Form_AfterUpdate()

To this: Private Sub Form_BeforeUpdate()

AfterUpdate is after changes have been saved, and you can't undo after the change has been saved.

Thanks Puppydog, but that can't be it; the undo DOES work once.

And I tried changing to afterupdate and I got an error message that my code was preventing Access from saving data in the field.

Any other ideas?

[missinglinq]

PuppyDogBuddy is correct in saying that this type of code needs to be in the BeforeUpdate event, but that also makes some of your code inappropriate. You don't need the line to save the record, Access will do that automatically if you don't cancel the update. You also want to use vbYesNo, not vbYesNoCance in your messageboxl. You only have two choices here, save or don't save the record. This code should get the job done

Private Sub Form_BeforeUpdate(Cancel As Integer)
 Dim intYesNo As Integer
intYesNo = MsgBox("Yes to Save, No to Delete Record", vbQuestion + vbYesNo, "Enter")
If intYesNo = vbNo Then
DoCmd.RunCommand acCmdUndo
Cancel = True
End If
End Sub

Welcome to Bytes!

Linq ;0)>

[puppydogbuddy]

Thanks Linq,
I forgot to change the code to take out the save.

pDog

[missinglinq]

Four eyes always be better than two!

;0)>

[dantebothermy]

Thank you Missinglinq. It worked -- I don't understand why, but it worked.

PuppyDogBuddy is correct in saying that this type of code needs to be in the BeforeUpdate event, but that also makes some of your code inappropriate. You don't need the line to save the record, Access will do that automatically if you don't cancel the update. You also want to use vbYesNo, not vbYesNoCance in your messageboxl. You only have two choices here, save or don't save the record. This code should get the job done

Private Sub Form_BeforeUpdate(Cancel As Integer)
 Dim intYesNo As Integer
intYesNo = MsgBox("Yes to Save, No to Delete Record", vbQuestion + vbYesNo, "Enter")
If intYesNo = vbNo Then
DoCmd.RunCommand acCmdUndo
Cancel = True
End If
End Sub

Welcome to Bytes!

Linq ;0)>

[missinglinq]

Glad we finally got it working for you!

Linq ;0)>