MsgBox to confirm exit from form in Access

Question

I have a single record form with contract details and a subform where I enter some additional information. The main form has a command button to close it.

I need to check 2 controls on closing(one in main form and one in subform) and if some conditions are met a Yes/No MsgBox should appear. If the user presses "Yes", the form closes, if he presses "No" the form remains open.

So far I have this:

Private Sub Form_Unload(Cancel As Integer)
Dim Response As Integer

Response = MsgBox("It looks like this contract is fixed. Would you like to edit the final price?", vbYesNo, "Database Information")

If Me![fixed price] = 0 And Me![Fixation Orders Subform1].Form!Text38 = "Final Fixed Price" Then
    Cancel = True And Response
        If Response = vbYes Then
            Cancel = True
         Else
            Cancel = False
         End If

Else
    Cancel = False
End If

End Sub

Problems: Before I defined "response" the MsgBox appeared correctly but the form closed anyway. After I defined it to use it in the second "If":

The message box appears no matter what
The form closes either I press "Yes" or "No"

Additionally, after I solve this, on which event should I put this code to chack the conditions also when I go to next record? Thanks in advance.

maybe Cancel = Response=vbYes instead of Cancel = True And Response the AND with a true, means that cancel will be the same as the other side of the AND. TRUE and today=Wednesday, only ever need to check the day for example. Looking at your if, it doesn't seem logical, you set the Cancel, try to, then base it on the msgbox decision. Before end sub, just put cancel=response=vbYes or which ever way you want the decision — Nathan_Sav
That's one weird piece of code. Of course it always shows the message box, the first thing you do in that sub is show the message box and assign it's result to Response. That's not nested in an If statement. What exactly is the line Cancel = True And Response supposed to do? Do you have Option Explicit on? — Erik A
Hi again Erik..I've just started learning VBA on my own so pls be patient. The line you're referring to is supposed to cancel the unload and pop up the msgbox if the conditions are met. Worked fine until I defined the response. I don't know what "Option Explicit" is. What is the proper way to check the response of the msgbox? — aran0ia
The answer Sergey provided is correct. If you want to continue VBA development, you can read into Option Explicit here, this page gives a quick explanation why you would want to use it. If you're having troubles with something, turning Option Explicit on often helps. — Erik A

Sergey S. Sergey S. · Accepted Answer · 2017-08-23T10:20:55

I believe it should be something like this:

Private Sub Form_Unload(Cancel As Integer)
If Me![fixed price] = 0 And Me![Fixation Orders Subform1].Form!Text38 = "Final Fixed Price" Then
    If MsgBox("It looks like this contract is fixed. Would you like to edit the final price?", vbYesNo, "Database Information") = vbYes Then
        Cancel = True
    End If
End If
End Sub

MsgBox to confirm exit from form in Access

1 Answers