How to cancel a close event

[reginaldmerritt]

I want to check the form for missing data when the form is closed. I've done this by adding a message on the Form_Unload event, the user can either choose to use defult values for missing data or cancel the close event.

From searching online i have found the the following should allow cancelation of the close event.

DoCmd.CancelEvent
 

However, this doesn't if the close event is via VBA code rather than the forms inbuilt close button.

For example, the following code does not work:

Private Sub OwnCloseButton_Click()
DoCmd.Close
End Sub
 
Private Sub Form_Unload(Cancel As Integer)
Missinginfomessage = MsgBox("Information Missing" & vbNewLine & _
                     "Accept Defult Values", vbOKCancel)
 
If Missinginfomessage = vbOK Then
'set defult values and close
End If
 
If Missinginfomessage = vbCancel Then
DoCmd.CancelEvent
End If
 
End Sub
 

If the OwnCloseButton is clicked the cancel event cancels the DoCmd.Close statment and i get a run time error.

Run-time Error '2001'

You canceled the previous operation


Does anyone know how i can cancel the close event without getting this error?

Many Thanks.

[TheSmileyCoder]

You need to modify the click event to handle the error. Seen from a logic point of view, its actually desirable that when we request an action, that if it fails we get informed. Most good code will inform somehow, if it fails.

Anyways, adding error handling:

Private Sub OwnCloseButton_Click() 
On Error Goto Err_Handler
  DoCmd.Close 
  Exit Sub
 
Err_Handler:
  If Err.Number=2001 Then
    'Don't do anything, since getting this error just means the form validation failed
  Else
    'Unexpected Error
    Msgbox "An unexpected error occured" & vbnewline & Err.Number & " - " & err.Description
    Exit Sub
  End If
End Sub 

And thank you for making a well explained question with relevant code presented in the Original post, and for remembering to include and highlight the error message.

[reginaldmerritt]

Thanks SmileyCoder, much appreciated.

That does indeed work on the code i provided. But yet again i have simplified things to much.

My actual code runs the close event from a subform which loads the close event in a public subroutine. I hope that makes sense.

I have a form with a subform holding buttons for a menu. Therefore the form can be closed in many different ways, depending on which button the user clicks on.

I have incorporated the code provided by SmileyCoder into the public close sub routine. I no longer get the error message but the form still closes.

Code on Sub Form

Private Sub Booked_Courses_Click()
    CloseAllFormsAndReports
    DoCmd.OpenForm "FRMBookedCoursesList"
End Sub
 
 
Private Sub Clients_Click()
    CloseAllFormsAndReports
    DoCmd.OpenForm "FRMClientList"
End Sub
 
Private Sub Courses_Click()
    CloseAllFormsAndReports
    DoCmd.OpenForm "FRMCoursesList"
End Sub
 
 
Private Sub BookedEvents_Click()
    CloseAllFormsAndReports
    DoCmd.OpenForm "FRMBookedEventsList"
End Sub
 

Code in Public Module

Public Sub CloseAllFormsAndReports()
 
On Error GoTo Err_Handler
 
'Close all open Forms
Do While Forms.Count > 0
DoCmd.Close acForm, Forms(0).Name
Loop
Exit Sub
 
Err_Handler:
If Err.Number = 2001 Then
    'Don't do anything, since getting this error just means the form validation failed
     'MsgBox ("Skipping Error 2001")
Else
    'Unexpected Error
    MsgBox "An unexpected error occured" & vbNewLine & Err.Number & " - " & Err.Description
    Exit Sub
End If
 
End Sub
 

Code on Main Form

Private Sub Form_Unload(Cancel As Integer)
CheckMissingData
End Sub
 
Dim MissingData As String
 
If Not IsNull(CourseID) Or CourseID <> "" Then
 
    If IsNull(CourseName) Or Not IsDate(ValidFrom) Or Not IsDate(ValidUntil) Then
 
        ' belwo works out what data is missing for the message to the user
        MissingData = ""
        If IsNull(CourseName) Then MissingData = MissingData + "Course Name" & vbNewLine
        If Not IsDate(ValidFrom) Then MissingData = MissingData + "Valid From Date" & vbNewLine
        If Not IsDate(ValidUntil) Then MissingData = MissingData + "Valid Unitl Date" & vbNewLine
 
        DataChangeMsg = MsgBox("The current course is missing mandatory information" & vbNewLine & _
                        "The missing information is:" & vbNewLine & vbNewLine & MissingData & vbNewLine & _
                        "Default values will be used for this missing information", _
                        vbOKCancel + vbInformation, "Automatic Change of Details")
 
        If DataChangeMsg = vbOK Then
            'Update query used to replace any required missing data
            DoCmd.SetWarnings False
            DoCmd.OpenQuery "QYUPDARTENullCourseNameCourseDetails"
            DoCmd.OpenQuery "QYUPDARTENullValidFromCourseDetails"
            DoCmd.OpenQuery "QYUPDARTENullValidUntilCourseDetails"
            DoCmd.SetWarnings True
        End If
 
 
    End If
 
    If DataChangeMsg = vbCancel Then
        'don't close
        DoCmd.CancelEvent
    End If
 
 
End If
 
End Sub
 

Not quite sure if it is the way that i cancel the event that needs changing or the code in the public module.

Any help welcome.

[TheSmileyCoder]

I tried creating a main form with a sub form in it. I added msgbox "Main form Closing", and msgbox "Main form unloading" and did similar for the subform.

I found that access does the processing in the following order:
Main form Unload
Main Form Close
Sub form Unload
Sub form close

So by the time you cancel the unloading of the subform, its allready to late, since the main form has allready unloaded and closed. The method I posted earlier only works on the main form.

However, that doesn't mean we are out of options. You could write a procedure to loop through the controls of each form, before you close it, to see if any of the controls contain a subform, and then to see if its "safe" to close that record. Its doable, but a bit complicated.

If you want to go that way, I can assist in writing the code, but its not all that simple.

There may of course also be other ways that I haven't thought about.

[NeoPa]

You seem to be using the code DoCmd.CancelEvent. Have you tried setting the value of the Cancel parameter to True instead? That's what it's there for. I suspect, due to the complexities of handling failures of the closes (unloads) of related items, the approach you use is not adequate. Let us know how you get on with this.

[TheSmileyCoder]

I can just add that I tried both the Docmd.CancelEvent as well as setting the Cancel=True for the subform. Neither gave the desired result of preventing the subform from closing.

[NeoPa]

That's a good point you make there Smiley. It doesn't make sense to try to cancel the Unload event of a form used as a subform. What needs to be canceled is the Unload of the form itself. The subform is never closed or unloaded as such. The event is only there available because it is a form, and forms typically are used and opened on their own.

[MMcCarthy]

A subform on a form is an object/control and only has two events available to it. The on enter and on exit event.

Your code will work fine if you have it on the unload event of the main form.

Private Sub Form_Unload(Cancel As Integer)
Dim rslt As Integer
 
    rslt = MsgBox("Testing", vbYesNo)
    If rslt = vbYes Then
        Cancel = False
    Else
        Cancel = True
    End If
 
End Sub
 

[MMcCarthy]

In other words you are trying to effect the main form by using an event on the form acting as the subform. You cannot do this. The only events that will affect the main form are the object control events not the events of the form acting as the subform which only affect that form.

[reginaldmerritt]

Sorry for taking so long to reply.

Thanks for all of your replies.

I can't move the close statment from the subform. So instead i've removed the close statment acting on the specfic form which i need to check for missing data on and added the close statement to the main form.

I have had to edit the public sub i use for closing all open forms quite a lot and also stop forms from opening as they would usally be from the subform.

I don't like it. I hate plugging holes with extra bits of code but it works and thats going to have to be enough for now.

Thanks again for all your help. I'll have to rethink the way i close all open from from the sub form in any future applications as i'm sure i will want to check for missing data in other applications.

Code in Subform

Private Sub Clients_Click()
    CloseAllFormsAndReports
 
    If CancelOpenForm = False Then
        DoCmd.OpenForm "FRMClientList"
    End If
 
End Sub
 
Private Sub Courses_Click()
    CloseAllFormsAndReports
 
    If CancelOpenForm = False Then
        DoCmd.OpenForm "FRMCoursesList"
    End If
 
End Sub
 
 
Private Sub BookedEvents_Click()
    CloseAllFormsAndReports
 
    If CancelOpenForm = False Then
        DoCmd.OpenForm "FRMBookedEventsList"
    End If
 
End Sub
 

Code in Public Module

Public Sub CloseAllFormsAndReports()
 
CancelOpenForm = False
 
 
    Do While Forms.Count > 0
 
       If Forms(0).Name <> "FRMCourseDetails" Then
 
            'close all open forms
            DoCmd.Close acForm, Forms(0).Name
 
        End If
 
        If Forms.Count > 0 Then
             If Forms(0).Name = "FRMCourseDetails" Then
 
                'Cancel any request to open another form
                CancelOpenForm = True
 
                'Call CheckMissingData routine on mainform (FRMCourseDetails)
                Call Forms.FRMCourseDetails.CheckMissingData
 
                Exit Sub
 
            End If
        End If
 
    Loop
 
 
End Sub
 

Code in Main Form

Public Sub CheckMissingData()
Dim MissingData As String
 
If Not IsNull(CourseID) Then
 
    If IsNull(CourseName) Or Not IsDate(ValidFrom) Or Not IsDate(ValidUntil) Then
 
        ' belwo works out what data is missing for the message to the user
        MissingData = ""
        If IsNull(CourseName) Then MissingData = MissingData + "Course Name" & vbNewLine
        If Not IsDate(ValidFrom) Then MissingData = MissingData + "Valid From Date" & vbNewLine
        If Not IsDate(ValidUntil) Then MissingData = MissingData + "Valid Unitl Date" & vbNewLine
 
        DataChangeMsg = MsgBox("The current course is missing mandatory information" & vbNewLine & _
                        "The missing information is:" & vbNewLine & vbNewLine & MissingData & vbNewLine & _
                        "Default values will be used for this missing information", _
                        vbOKCancel + vbInformation, "Automatic Change of Details")
 
        If DataChangeMsg = vbOK Then
 
            'Update query used to replace any required missing data
            DoCmd.SetWarnings False
            DoCmd.OpenQuery "QYUPDARTENullCourseNameCourseDetails"
            DoCmd.OpenQuery "QYUPDARTENullValidFromCourseDetails"
            DoCmd.OpenQuery "QYUPDARTENullValidUntilCourseDetails"
            DoCmd.SetWarnings True
 
             'Close the form
            DoCmd.Close
 
            'Allow other forms to be opened
            CancelOpenForm = False
 
        End If
 
        If DataChangeMsg = vbCancel Then
            'do nothing.. don't close form
        End If
 
    Else
            'Close the form
            DoCmd.Close
 
            'Allow other forms to be opened
            CancelOpenForm = False
 
    End If
 
 
 
End If
 
End Sub