Yes/No message box - undo on 'No' response

[Abby]

Hi, I have a few fields on a database (which is used by a number of people) which keep getting accidentally altered - I would like to put in an 'after change' message box which would confirm whether the change was intentional, allow the change on a 'yes' response but undo the change on a 'no' so that the data had not been altered if the user didn't mean to. The bit I would be grateful for input/advice on is the undo function after a No response as at the moment that's the bit that isn't working!

I have got as far as:

Private Sub Text16_AfterUpdate()
Dim lngValue As Long
lngValue = MsgBox("Do you want to change the surname?", vbYesNo + vbQuestion, "Confirmation")


Select Case lngValue
Case vbYes = MsgBox("Thank you")
Case vbNo = fn.Undo
End Select
End Sub

[pbaldy]

Most of us use the before update event:

http://www.baldyweb.com/BeforeUpdate.htm

[Missinglinq]

As Paul said, for validation code you'd usually use the BeforeUpdate event. To validate that data has actually been entered in a given Control or Controls, you'd use the Form_BeforeUpdate event.

To check data that has been entered in a Control, for appropriateness, or as in this case, where you want to verify that the change was intentional, and want the message to pop as soon as the data is changed, you'd use the BeforeUpdate event of that Control:

Private Sub Text16_BeforeUpdate(Cancel As Integer)

Dim lngValue As Integer

If Not Me.NewRecord Then

 lngValue = MsgBox("Do you want to change the surname?", vbYesNo + vbQuestion, "Confirmation")

  Select Case lngValue

   Case vbYes
  
    MsgBox ("Thank you")

   Case vbNo

    Cancel = True
    Me.Undo

  End Select

End If

End Sub

I've included a check to only pop this message if the Record is not a New Record; no use in aggravating the users when entering data for the first time in a Record.

The Control's AfterUpdate event fires too late to do this kind of thing; by the time it executes the Value is already committed and can't be undone.

Also, you'd do well to get into the practice of giving your Controls significant names, such as txtSurname. You'll thank yourself, later, when you go to modify your app! Six months from now Text16 will mean nothing to you.

Linq ;0)>