me.Dirty confirmation message issues

[RSW]

A thread in a different forum went off on a tangent, and I think my resulting question would be better asked here.

I have a form that contains a bunch of fields all related to one database record at a time. I would like to have a confirmation message box appear before moving to a different record. I added this code in the Before Update section of the form properties:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty Then
If MsgBox("This record has been changed. Are you sure it's okay to proceed?", vbYesNo) = vbNo Then

Cancel = True
End If
End If
End Sub​

My problem is this: Right now, if someone inadvertently makes a change to one of the fields and doesn't know what it was, he or she cannot just discard the changes and move to another record--rather, the user must go through a clumsy way of exiting the database without saving.

Is there any way to set up code like the above such that, once a change is made, a Yes moves to the next record while saving changes, but a No moves to the next record without making the changes?

Thanks in advance.

 

[RuralGuy]

The only time you have a form BeforeUpdate event is when Me.Dirty is true so testing for if is redundant. Me.UnDo will undo *everything* changed on the form and allow you to move to the next record without saving anything. Post back if you need additional assistance.

 

[RSW]

Thanks for the assistance,

Are you saying the code would be something like this?

Private Sub Form Before_Update(Cancel As Integer)
If MsgBox ("This record has been changed. Are you sure you want to update?", vbYesNo) = vbNo Then
Me.Undo
EndIf​

 

[boblarson]

Thanks for the assistance,

Are you saying the code would be something like this?

Private Sub Form Before_Update(Cancel As Integer)
If MsgBox ("This record has been changed. Are you sure you want to update?", vbYesNo) = vbNo Then
Me.Undo
EndIf​

Almost - you need to cancel the update first with Cancel = True (actually believe it or not, you can say Cancel = vbRed and it works too. You can substitute anything that isn't zero (0) and it will act as cancel = true (that was a side-note)

Private Sub Form Before_Update(Cancel As Integer)
If MsgBox ("This record has been changed.  Are you sure you want to update?", vbYesNo) = vbNo Then
   Cancel = True
   Me.Undo
End If

 

[RuralGuy]

RSW,
Your code would allow the user to move to the next record without saving any changes. I would probably reverse the logic and ask the user if they would like to ignore any changes made to the current record, but that just me.

 

[RSW]

Great. Thanks again for the help gentlemen.

 

[RuralGuy]

Glad we could help.