How to Cancel with option Yes No

[bluemoon9]

on my form, I have a "Cancel" button which allows user to cancel updates and here is my code:

Dim db As Database
    Dim frm As Form
    Set db = CurrentDb
    Set frm = Forms!frmReview
 
    If Me.Dirty Then 'Check to see if record has been updated.
        DoCmd.RunCommand acCmdUndo
        DoCmd.Close acForm, "frmReview"
    Else
        DoCmd.Close acForm, "frmReview"
    End If
End sub
 

the above code will close the form without save the form when user click on the 'cancel' button. What I would like to do is to add option Yes or No when user click on Cancel button. When user chooses Yes, form will be closed without saving the changes. When user chooses No, message box close and form stay openw with the changes. Here is my code, but it doesn't work properly

Dim db As Database
    Dim frm As Form
    Set db = CurrentDb
    Set frm = Forms!frmReview_V2
    MsgBox "Cancel? Select Yes or No", vbYesNo, "Cancel Update!"
    if vb=Yes then
        DoCmd.RunCommand acCmdUndo
        DoCmd.Close acForm, "frmReview"
    elseif vb=No then
        DoCmd.RunCommand acCmdUndo
    End If
end sub
 

any help would be grealy appreciated.

bluemoon

[patjones]

I think you're just a little confused about how the VB constants work. Try this...

Dim intCancel As Integer
 
intCancel = MsgBox ("Cancel? Select Yes or No", vbYesNo+vbQuestion, "Cancel Update!")
 
If intCancel = vbYes Then
   DoCmd.RunCommand acCmdUndo
   DoCmd.Close acForm, "frmReview"
ElseIf  intCancel = vbNo Then
   DoCmd.RunCommand acCmdUndo
End If
 
End Sub

[bluemoon9]

@zepphead80
Hi,
I tried the code, but it doesn't seem to work properly as well. it worked the first round, but second clicks do not perform the way I want.
In addition, the No part seems to be wrong because even though I select No, the updates that I've made to the records did not stay.

thanks!

bluemoon

[bluemoon9]

@bluemoon9
I've tried this code, and it worked well with the No, but did not work for the Yes. I made some updates to the record, then click on Cancel, then choose yes, but the form still keep the updates even though I've assigned the form to be 'acSaveNo'

Dim db As Database
    Dim frm As Form
    Set db = CurrentDb
    Set frm = Forms!frmReview
    Dim intCancel As Integer
 
intCancel = MsgBox("Cancel? Select Yes or No", vbYesNo + vbQuestion, "Cancel Update!")
 
If intCancel = vbYes Then
   DoCmd.Close acForm, "frmReview", acSaveNo
ElseIf intCancel = vbNo Then
End If
end sub
 

bluemoon

[patjones]

You are sort of contradicting what you first posted when you started the thread. There you were using DoCmd.RunCommand acCmdUndo to throw out the changes before closing the form. Why aren't you using it now?

Pat

[bluemoon9]

Thanks for the advice, i thought I could just use the command acSaveNo, then I do not need to use command acCmdUndo. I've placed the undo command back and it worked.
thanks!

bluemoon

[nico5038]

Hi bluemoon,

I wonder why you add a confirmation popup to the Cancel button as I regard them as annoying.
Normally I use an [OK] and a [Cancel] button and when my form has many fields I also add a [Reset] button to restore the initial field values.

Only a "risky" [Delete] button will justify additional confirmation in my view.

Nic;o)

[bluemoon9]

@nico5038
I know, I would prefer the same way. That was how I programed the button in the first place; Once the user click on cancel, the form just close itself. However, my users have requested to add a code with the Y or N option because they said that sometimes they hit cancel button by mistake.

Bluemoon

[nico5038]

I know this "user problem" all too well Bluemoon :-)
Guess within half a year they'll get bored and ask you to remove it ..... <LOL>

Success with your application !

Nic;o)