CODEWITHSUNDEEP
ms-accessvba
Carl
Carl

Reputation: 5779

Message box that confirms if a user wants to leave a form

I had a piece of VBA running, and for some reason it has suddenly stopped working.

On my form unload event I have the code:

if isnull(me.field) then
    ans=MsgBox("Warning you have not entered all the data. Do you want to exit the form", vbYesNo, "Error")
    If ans=vbNo then
        Cancel=True
    end if
end if

This worked for a couple months, when the user exited the warning message would appear, and if they select no the form would not exit. Now when I click no I get an error:

Run time Error 3270. Property not Found

I changed the code to:

if isnull(me.field) then
    ans=MsgBox("Warning you have not entered all the data. Do you want to exit the form", vbYesNo, "Error")
    If ans=vbNo then
        docmd.cancelevent
    end if
 end if

Now I get error:

Runtime Error '2001' You Canceled The Previous Operation

Which is what I want.

How do I get a messagebox to confirm that a user wants to exit a form?

Edit: I realize that the exit warning works when I exit the form by pressing x in the upper right, but when I exit using a button with the docmd.close I get the errors. Any way around that?

Upvotes: 0

Views: 3955

Answers (2)

Newd
Newd

Reputation: 2185

I feel like I am missing something because the path you chose seems a little over complicated.

Firstly, with my database I always make sure to set all of my forms Close Button property to "No" this way you always have control of when the user closes the form.

So from that point you just need this code attached to your close button:

Private Sub btnClose_Click()

    Dim blnClose As Boolean
    Dim strResponse As String

    'Default to true so it always closes unless one or more future checks fail
    blnClose = True

    If IsNull(Me.Field) Then
        strResponse = MsgBox("Warning you have not entered all the data. Do you want to exit the form", vbYesNo, "Error")

        'User wants to cancel close toggle Boolean to false
        If strResponse = vbNo Then
            blnClose = False
        End If
    End If

    'If nothing has toggled to false then close the form
    If blnClose = True Then
        DoCmd.Close , ""
    End If

End Sub

Upvotes: 1

MatthewD
MatthewD

Reputation: 6761

Simply use the button click event.

Where CommandButton14 is the name of your exit button.

EDIT for users comment.

Have your exit button call the UserForm_QueryClose event.

Private Sub CommandButton14_Click()
    'UserForm_QueryClose 0, 0
    Unload Me
End Sub

Ask your question in that event. If they say yes end the app or unload the form. If they say no, cancel.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Dim Response As Integer

' Displays a message box with the yes and no options.
Response = MsgBox("Warning you have not entered all the data. Do you want to exit the form?", vbYesNo)

' If statement to check if the yes button was selected.
If Response = vbYes Then
   'Cancel = False
   End
Else
   Cancel = True
End If
End Sub

You can take everything out of your unload event.

Upvotes: 2

Related Questions