msgbox VBYesNoCancel - How to cancel and return to form

[wtucker]

I don't know why I can't find the answer to this. I've got the code down for the most part.
I want to ask the user whether they want to save their changes or not, but I also want to let them click "cancel" and return to the form, without saving or deleting the changes.



This is what I have so far:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim response As Integer
response = MsgBox("Would You Like To Save Your Changes?", vbQuestion + vbYesNoCancel + vbDefaultButton3, "Save Changes to Record?")
Select Case response
Case vbYes
DoCmd.Save
Case vbNo
Me.Undo
Case vbCancel
'missing code
End Select
End Sub

[June7]

So if they say Yes or No you want to close the form?

How are users closing? Do you have a command button for that?

[wtucker]

So if they say Yes or No you want to close the form?

How are users closing? Do you have a command button for that?

"Yes" and "No" should both close the form, albeit with different results. But "Cancel" should just close the message box and return to the form. I'm using the vbDefaultButton3, which includes the "Yes", "No", and "Cancel" buttons. The code works perfectly except for the command for the case "Cancel". I don't know what to write.

On a side note, you've posted in just about every single thread of mine. I can't thank you enough for the time you've taken to help me figure things out time and again.

[June7]

I understand you have the Yes/No/Cancel button on the popup but what triggers this code? Closing form from a command button or the X button? I normally disable the X close and create command button for form close, then code in the button Click event:

Private Sub btnClose_Click()
Dim response As Integer
If Me.Dirty Then response = MsgBox("Would You Like To Save Your Changes?", vbQuestion + vbYesNoCancel + vbDefaultButton3, "Save Changes to Record?")
Select Case response
    Case vbYes
        Me.Dirty = False
    Case vbNo
        Me.Undo
    Case vbCancel
        Me.Undo
        Me.somecontrol.SetFocus
End Select
If response <> 2 Then DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

As far as a I can recall, I've never used form BeforeUpdate event.

Glad my contributions have been helpful.

[wtucker]

We use the X-button in the top corner. I don't foresee us getting rid of it. It would confuse too many people, unfortunately.

[June7]

Okay, then try:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim response As Integer
    If Me.Dirty Then response = MsgBox("Would You Like To Save Your Changes?", vbQuestion + vbYesNoCancel + vbDefaultButton3, "Save Changes to Record?")
    Select Case response
        Case vbYes
            'do nothing, closing form commits record
        Case vbNo
            Me.Undo
        Case vbCancel
            Cancel = True
    End Select
End Sub

[wtucker]

This works pretty well, but when I hit Cancel I get that message box from Access saying "You can't save this record at this time". It's asking me if I want to close the form. I want it to always stay open upon hitting cancel.

I'm also using this in conjuction with subforms. I'd rather it not ask me if I want to save changes every time I click away from the main form, or click between subforms. :/

Okay, then try:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim response As Integer
    If Me.Dirty Then response = MsgBox("Would You Like To Save Your Changes?", vbQuestion + vbYesNoCancel + vbDefaultButton3, "Save Changes to Record?")
    Select Case response
        Case vbYes
            'do nothing, closing form commits record
        Case vbNo
            Me.Undo
        Case vbCancel
            Cancel = True
    End Select
End Sub

[davegri]

I get that message box from Access saying "You can't save this record at this time". It's asking me if I want to close the form. I want it to always stay open upon hitting cancel.

Add the red line to get rid of the annoying question:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim response As Integer
    If Me.Dirty Then response = MsgBox("Would You Like To Save Your Changes?", vbQuestion + vbYesNoCancel + vbDefaultButton3, "Save Changes to Record?")
    Select Case response
        Case vbYes
            'do nothing, closing form commits record
        Case vbNo
            Me.Undo
        Case vbCancel
            Cancel = True
            Me.Undo
    End Select
End Sub

Further testing indicates that this change doesn't reliably work. Sorry about that. If I can come up with a solution, I'll re-post.

[June7]

davegri is right, not reliable. I tried moving Me.Undo before Cancel = True. It sort of works if editing an existing record but on a new record the form still closes.

Starting to remember why I avoid the form BeforeUpdate and AfterUpdate events, disable X close, and use command button.

[June7]

Finally found something:

Option Compare Database
Option Explicit
Dim bCannotClose As Boolean

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Response As Integer
    If Me.Dirty Then Response = MsgBox("Would You Like To Save Your Changes?", vbQuestion + vbYesNoCancel + vbDefaultButton3, "Save Changes to Record?")
    Select Case Response
        Case vbYes
            'do nothing, closing form commits record
        Case vbNo
            Cancel = True 'Me.Undo would work as well
        Case vbCancel
            Cancel = True 'Me.Undo would work as well, don't need both
            bCannotClose = True
    End Select
End Sub

Private Sub Form_Error(DataErr As Integer, Response As Integer)
 If DataErr = 2169 Then
    Response = acDataErrContinue
 End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
Cancel = bCannotClose
bCannotClose = False
End Sub

[davegri]

June7, very nice. Form_Unload was the key event. I had checked out Form_Close and that had not worked as it had no Cancel parameter.

[wtucker]

Fantastic! The buttons work!

Now I need to figure out how to use this in conjunction with subforms. I might decide to just keep it as is and add it to each form, but it's a bit of a pain when the user is doing data entry and regularly switching between subforms; the message box would pop up every time they click a different tab. And the code isn't very useful if it's only protecting 10% of the data. I don't imagine an easy solution to this. Any recommendations?