vbYesNoCancel

[JamesJ]

I'm using the following code to confirm data changes.

Public Function ConfirmDataChange()

Dim frm As Form
Dim Msg, Style, Title, Response

Set frm = Screen.ActiveForm

Msg = "Data has been added or changed," & vbCrLf & "do you want to
save the changes?"
Style = vbYesNo + vbExclamation + vbDefaultButton2
Title = "Data Change Confirmation"

Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then

Else

frm.Undo

End If

End Function

This is called form the Before_Update of the form. The code
works fine. But, I want to add a Cancel button to the code that will cancel
the move to another record or closing of the form.

Any help will be appreciated.

James

 

[Allen Browne]

I think this is what you are after:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Select Case MsgBox("Save?", vbYesNoCancel)
Case vbYes 'go ahead and save.
'do nothing
Case vbNo 'undo the changes.
Cancel = True
Me.Undo
Case vbCancel 'Leave the user editing with their changes.
Cancel = True
End Select
End Sub

BTW, if the user does attempt to close the dirty form, and they choose
Cancel, they will then receive another message from Access itself asking
whether the close anyway. It is possible to trap that message with
Form_Error, but it's not straightforward to offer anything better.

 

[JamesJ]

Thank, it works fine. And yes, Access does pop up another
message about closing the object. I'll look into trapping
the error.

Thanks,
James