Cancelling an Access Form Close

Suppose you need to add a standard “Are you sure…” confirmation when the user closes an Access form.  It’s easy to add the confirmation message to a button you create, but how do you handle it when the user clicks the form’s standard close button (the “x” in the corner)? Simply adding Cancel = True to the Form_Close() event doesn’t work because the close event can’t be cancelled.

The key is to use the Form’s Unload event, because it can be cancelled.

1. Private Sub Form_Unload(ByVal Cancel As Integer)
2.  
3.     If MsgBox("Are your sure", vbYesNo + vbQuestion, "Close Form") = vbNo Then
4.         Cancel = True
5.     End If
6.  
7. End Sub

If you’ve created your own Close button that uses Access’ standard docmd.close method you’ll also need to modify your button’s err handler because cancelling the form’s close method will raise error 2501 “The Close action was cancelled”.  Here’s a simple example of how to handle the error.

1. Private Sub cmdClose_Click()
2.   On Error GoTo HandleError
3.  
4.     DoCmd.Close(acForm, Me.Name)
5.  
6. ExitHere:
7.     Exit Sub
8. HandleError:
9.     '* Error 2501 will be caused by canceling the form close.
10.  
11.     If Err.Number = 2501 Then
12.         Resume Next
13.     Else
14.         MsgBox("Error: " & Err.Number & " (" & Err.Description & ")", vbOKOnly, "Error")
15.         GoTo ExitHere
16.     End If
17. End Sub