I am looking for a generic YN Cancel message box that will work on any form.
I am looking for a generic YN Cancel message box that will work on any form.
Something like this?
Code:Dim Resp As Long Resp = MsgBox("Your Prompt Message Goes Here", vbYesNoCancel + vbQuestion, "Message Box Title Goes Here") Select Case Resp Case vbYes 'Code for Yes Response Case vbNo 'Code for No Response Case vbCancel 'Code for Cancel Response End Select
Linq ;0)>
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007
ok so if the user clicks on yes I want to save the current record
if the user clicks no it will delete current record and cancel to edit the current record.
How do i code this?
this is what I have
Code:Dim strMsg As String Dim iResponse As Integer ' Specify the message to display. strMsg = "Do you wish to save the changes?" & Chr(10) strMsg = strMsg & "Click Yes to Save or No to Discard changes or Cancel to go back." ' Display the message box. iResponse = MsgBox(strMsg, vbQuestion + vbYesNoCancel, "Save Record?") ' Check the user's response. If iResponse = vbNo Then ' Undo the change. DoCmd.RunCommand acCmdUndo ' Cancel the update. Cancel = True ElseIf iResponse = vbCancel Then 'Cancel Close, continue editing Cancel = True End If End Sub
but this is not working for me
Try this. I have this attached to all the Close buttons on my forms.Code:Private Sub cmdClose_Click() If Me.Dirty = True Then 'Check for data changes Select Case MsgBox("Do you want to save changes to this record?", vbYesNoCancel, "Save Changes") Case vbYes 'Save changes, close form DoCmd.Close Case vbNo 'Undo changes, close without saving Me.Undo DoCmd.Close Case vbCancel 'Cancel close, continue editing DoCmd.CancelEvent End Select Else 'No data changes, close form DoCmd.Close End If End Sub
~Matt
You cannot Cancel a Command Button's action! Code asking whether or not to Save a Record or changes to a Record simply has to be in the Form_BeforeUpdate event, otherwise it will not work!
Linq ;0)>
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007
The code I posted above does work. I use it on all my forms. If you edit data and press close, you can cancel the close action. You can undo the changes (main form not any subforms) or you can save and close.
~Matt
When you hit 'Cancel,' the code
DoCmd.CancelEvent
is not doing anything, because you cannot Cancel the OnClick event of a Command Button! If you could, the Sub Header, instead of being
Private Sub cmdClose_Click()
would be
Private Sub cmdClose_Click(Cancel As Integer)
But that's OK! It works for you, because when you select 'Cancel' from your Messagebox, Access does nothing, and that is exactly what you want to happen when you hit 'Cancel!' You're not 'canceling' the 'Close event,' because the Close Command has never been issued! It's only issued if 'Yes" or 'No' is selected!
And yes, in this case, you can use the OnClick event, because you're not actually 'canceling' an Update (which does have to be in the Form_BeforeUpdate event); you're actually not 'canceling' anything! You're just leaving everything status quo, which works for you, here.
The only thing I'd think about, with this code, is making sure the users clearly understand the three options here! You wouldn't want them to make a dozen changes to a Record, hit 'Close,' see the message "Do you want to save changes to this record?" then realize that they left out one change they meant to make, and hit 'No,' only to see the dozen changes already made disappear! Of course, that all depends on the computer savvy of your users! Sadly, all end-users are not created equal! Or maybe that should be 'happily,' all end-users are not created equal!
Linq ;0)>
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007