473,883 Members | 1,694 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to cancel a close event

reginaldmerritt
201 New Member
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 20172
TheSmileyCoder
2,322 Recognized Expert Moderator Top Contributor
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 New Member
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,322 Recognized Expert Moderator Top Contributor
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,584 Recognized Expert Moderator MVP
You seem to be using the code DoCmd.CancelEve nt. 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,322 Recognized Expert Moderator Top Contributor
I can just add that I tried both the Docmd.CancelEve nt 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,584 Recognized Expert Moderator MVP
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 Recognized Expert Moderator MVP
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 Recognized Expert Moderator MVP
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 New Member
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

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

Similar topics

8
21617
by: MrNobody | last post by:
How do I change a window's behavior so when someone clicks the little 'X' in the upper right corner it actually hides the dialog, instead of disposing it?
1
1221
by: Daniel Slen Secches | last post by:
How can i know if the close event was sent by user or by the windows? Because a use on my appl. it Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing e.Cancel = closeit If not closeit Then
5
11076
by: Stan Sainte-Rose | last post by:
Hi, Which event is called when the user click on the close window icon (X) ? I want, when he clicks on this icon, to display a message before closing the form. If he replys by No, I don't want to close the form. Thks for your help Stan
0
1788
by: Vam$y | last post by:
In asp.net how to catch window close event when a user closes the window explicitly by clicking on “x” button which is on the right top corner of the browser. My requirement is When a user explicitly closes the window I need to pop up a message to user and log information in DB whatever the user entered in the form. I implemented on Body unload event by calling vb script, but it is getting executed when I go out of the form by...
4
13118
by: PW | last post by:
Hi, I want to add code to check if the user wants to save the record (fields are unbound) when they press the form's close (X) button. Is it possible to return the user to the form if there are editing errors? I am using Access 2003
5
34371
by: jimmy | last post by:
Hi all, I want to capture the event when the browser's close button is clicked in an html page. I tried using the event.ClientX and event.ClientY property in the body unload event, and this helps to recognise whether the close button is clicked( also differentiates whethet refresh was clicked). The problem arises whenever i try to close the window by resizing it or by right clicking at the bottom of the page and then closing it. Can...
4
6103
by: bsm | last post by:
Hi, I have googled for my issue in this group. This question was asked many times and many solution was given. But I want clear cut solution for the issue. The issue is: I tried to capture the browse close event by using Body onunload=fn1() statement and it is working fine.
1
6216
by: Frank O'Hara | last post by:
I think I'm losing my mind, granted it is kind of late here so... I'm trying to do some simple validation on the client as keys are pressed. The validation routine works well enough however I can't seem to cancel the event should the validation fail. Well more accurately, I can't cancel the keyup event. When I hook the following routine to the keydown event it works well and an invalidation cancels the event. I'm hooking the...
1
6802
by: baburk | last post by:
Hi, I wants to capture browser tab close event? I got for window close event. if((window.event.clientX<0) || (window.event.clientY<0)) { }
0
11121
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10734
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10836
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10407
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7114
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5793
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4606
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4210
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3230
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.