469,330 Members | 1,329 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,330 developers. It's quick & easy.

How to cancel a close event

reginaldmerritt
201 100+
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.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.CancelEvent
  2.  
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub OwnCloseButton_Click()
  2. DoCmd.Close
  3. End Sub
  4.  
  5. Private Sub Form_Unload(Cancel As Integer)
  6. Missinginfomessage = MsgBox("Information Missing" & vbNewLine & _
  7.                      "Accept Defult Values", vbOKCancel)
  8.  
  9. If Missinginfomessage = vbOK Then
  10. 'set defult values and close
  11. End If
  12.  
  13. If Missinginfomessage = vbCancel Then
  14. DoCmd.CancelEvent
  15. End If
  16.  
  17. End Sub
  18.  
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.
Mar 7 '11 #1
9 18173
TheSmileyCoder
2,321 Expert Mod 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub OwnCloseButton_Click() 
  2. On Error Goto Err_Handler
  3.   DoCmd.Close 
  4.   Exit Sub
  5.  
  6. Err_Handler:
  7.   If Err.Number=2001 Then
  8.     'Don't do anything, since getting this error just means the form validation failed
  9.   Else
  10.     'Unexpected Error
  11.     Msgbox "An unexpected error occured" & vbnewline & Err.Number & " - " & err.Description
  12.     Exit Sub
  13.   End If
  14. 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.
Mar 7 '11 #2
reginaldmerritt
201 100+
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub Booked_Courses_Click()
  2.     CloseAllFormsAndReports
  3.     DoCmd.OpenForm "FRMBookedCoursesList"
  4. End Sub
  5.  
  6.  
  7. Private Sub Clients_Click()
  8.     CloseAllFormsAndReports
  9.     DoCmd.OpenForm "FRMClientList"
  10. End Sub
  11.  
  12. Private Sub Courses_Click()
  13.     CloseAllFormsAndReports
  14.     DoCmd.OpenForm "FRMCoursesList"
  15. End Sub
  16.  
  17.  
  18. Private Sub BookedEvents_Click()
  19.     CloseAllFormsAndReports
  20.     DoCmd.OpenForm "FRMBookedEventsList"
  21. End Sub
  22.  
Code in Public Module
Expand|Select|Wrap|Line Numbers
  1. Public Sub CloseAllFormsAndReports()
  2.  
  3. On Error GoTo Err_Handler
  4.  
  5. 'Close all open Forms
  6. Do While Forms.Count > 0
  7. DoCmd.Close acForm, Forms(0).Name
  8. Loop
  9. Exit Sub
  10.  
  11. Err_Handler:
  12. If Err.Number = 2001 Then
  13.     'Don't do anything, since getting this error just means the form validation failed
  14.      'MsgBox ("Skipping Error 2001")
  15. Else
  16.     'Unexpected Error
  17.     MsgBox "An unexpected error occured" & vbNewLine & Err.Number & " - " & Err.Description
  18.     Exit Sub
  19. End If
  20.  
  21. End Sub
  22.  
Code on Main Form
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Unload(Cancel As Integer)
  2. CheckMissingData
  3. End Sub
  4.  
  5. Dim MissingData As String
  6.  
  7. If Not IsNull(CourseID) Or CourseID <> "" Then
  8.  
  9.     If IsNull(CourseName) Or Not IsDate(ValidFrom) Or Not IsDate(ValidUntil) Then
  10.  
  11.         ' belwo works out what data is missing for the message to the user
  12.         MissingData = ""
  13.         If IsNull(CourseName) Then MissingData = MissingData + "Course Name" & vbNewLine
  14.         If Not IsDate(ValidFrom) Then MissingData = MissingData + "Valid From Date" & vbNewLine
  15.         If Not IsDate(ValidUntil) Then MissingData = MissingData + "Valid Unitl Date" & vbNewLine
  16.  
  17.         DataChangeMsg = MsgBox("The current course is missing mandatory information" & vbNewLine & _
  18.                         "The missing information is:" & vbNewLine & vbNewLine & MissingData & vbNewLine & _
  19.                         "Default values will be used for this missing information", _
  20.                         vbOKCancel + vbInformation, "Automatic Change of Details")
  21.  
  22.         If DataChangeMsg = vbOK Then
  23.             'Update query used to replace any required missing data
  24.             DoCmd.SetWarnings False
  25.             DoCmd.OpenQuery "QYUPDARTENullCourseNameCourseDetails"
  26.             DoCmd.OpenQuery "QYUPDARTENullValidFromCourseDetails"
  27.             DoCmd.OpenQuery "QYUPDARTENullValidUntilCourseDetails"
  28.             DoCmd.SetWarnings True
  29.         End If
  30.  
  31.  
  32.     End If
  33.  
  34.     If DataChangeMsg = vbCancel Then
  35.         'don't close
  36.         DoCmd.CancelEvent
  37.     End If
  38.  
  39.  
  40. End If
  41.  
  42. End Sub
  43.  
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.
Mar 7 '11 #3
TheSmileyCoder
2,321 Expert Mod 2GB
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.
Mar 7 '11 #4
NeoPa
32,181 Expert Mod 16PB
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.
Mar 7 '11 #5
TheSmileyCoder
2,321 Expert Mod 2GB
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.
Mar 7 '11 #6
NeoPa
32,181 Expert Mod 16PB
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.
Mar 8 '11 #7
MMcCarthy
14,534 Expert Mod 8TB
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Unload(Cancel As Integer)
  2. Dim rslt As Integer
  3.  
  4.     rslt = MsgBox("Testing", vbYesNo)
  5.     If rslt = vbYes Then
  6.         Cancel = False
  7.     Else
  8.         Cancel = True
  9.     End If
  10.  
  11. End Sub
  12.  
Mar 8 '11 #8
MMcCarthy
14,534 Expert Mod 8TB
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.
Mar 8 '11 #9
reginaldmerritt
201 100+
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub Clients_Click()
  2.     CloseAllFormsAndReports
  3.  
  4.     If CancelOpenForm = False Then
  5.         DoCmd.OpenForm "FRMClientList"
  6.     End If
  7.  
  8. End Sub
  9.  
  10. Private Sub Courses_Click()
  11.     CloseAllFormsAndReports
  12.  
  13.     If CancelOpenForm = False Then
  14.         DoCmd.OpenForm "FRMCoursesList"
  15.     End If
  16.  
  17. End Sub
  18.  
  19.  
  20. Private Sub BookedEvents_Click()
  21.     CloseAllFormsAndReports
  22.  
  23.     If CancelOpenForm = False Then
  24.         DoCmd.OpenForm "FRMBookedEventsList"
  25.     End If
  26.  
  27. End Sub
  28.  
Code in Public Module
Expand|Select|Wrap|Line Numbers
  1. Public Sub CloseAllFormsAndReports()
  2.  
  3. CancelOpenForm = False
  4.  
  5.  
  6.     Do While Forms.Count > 0
  7.  
  8.        If Forms(0).Name <> "FRMCourseDetails" Then
  9.  
  10.             'close all open forms
  11.             DoCmd.Close acForm, Forms(0).Name
  12.  
  13.         End If
  14.  
  15.         If Forms.Count > 0 Then
  16.             If Forms(0).Name = "FRMCourseDetails" Then
  17.  
  18.                 'Cancel any request to open another form
  19.                 CancelOpenForm = True
  20.  
  21.                 'Call CheckMissingData routine on mainform (FRMCourseDetails)
  22.                 Call Forms.FRMCourseDetails.CheckMissingData
  23.  
  24.                 Exit Sub
  25.  
  26.             End If
  27.         End If
  28.  
  29.     Loop
  30.  
  31.  
  32. End Sub
  33.  
Code in Main Form
Expand|Select|Wrap|Line Numbers
  1. Public Sub CheckMissingData()
  2. Dim MissingData As String
  3.  
  4. If Not IsNull(CourseID) Then
  5.  
  6.     If IsNull(CourseName) Or Not IsDate(ValidFrom) Or Not IsDate(ValidUntil) Then
  7.  
  8.         ' belwo works out what data is missing for the message to the user
  9.         MissingData = ""
  10.         If IsNull(CourseName) Then MissingData = MissingData + "Course Name" & vbNewLine
  11.         If Not IsDate(ValidFrom) Then MissingData = MissingData + "Valid From Date" & vbNewLine
  12.         If Not IsDate(ValidUntil) Then MissingData = MissingData + "Valid Unitl Date" & vbNewLine
  13.  
  14.         DataChangeMsg = MsgBox("The current course is missing mandatory information" & vbNewLine & _
  15.                         "The missing information is:" & vbNewLine & vbNewLine & MissingData & vbNewLine & _
  16.                         "Default values will be used for this missing information", _
  17.                         vbOKCancel + vbInformation, "Automatic Change of Details")
  18.  
  19.         If DataChangeMsg = vbOK Then
  20.  
  21.             'Update query used to replace any required missing data
  22.             DoCmd.SetWarnings False
  23.             DoCmd.OpenQuery "QYUPDARTENullCourseNameCourseDetails"
  24.             DoCmd.OpenQuery "QYUPDARTENullValidFromCourseDetails"
  25.             DoCmd.OpenQuery "QYUPDARTENullValidUntilCourseDetails"
  26.             DoCmd.SetWarnings True
  27.  
  28.             'Close the form
  29.             DoCmd.Close
  30.  
  31.             'Allow other forms to be opened
  32.             CancelOpenForm = False
  33.  
  34.         End If
  35.  
  36.         If DataChangeMsg = vbCancel Then
  37.             'do nothing.. don't close form
  38.         End If
  39.  
  40.     Else
  41.             'Close the form
  42.             DoCmd.Close
  43.  
  44.             'Allow other forms to be opened
  45.             CancelOpenForm = False
  46.  
  47.     End If
  48.  
  49.  
  50.  
  51. End If
  52.  
  53. End Sub
  54.  
Mar 8 '11 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by MrNobody | last post: by
1 post views Thread by Daniel Slen Secches | last post: by
5 posts views Thread by Stan Sainte-Rose | last post: by
reply views Thread by Vam$y | last post: by
5 posts views Thread by jimmy | last post: by
4 posts views Thread by bsm | last post: by
1 post views Thread by Frank O'Hara | last post: by
1 post views Thread by baburk | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.