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.
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.
9 20025
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.
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.
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 32,556
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.
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 32,556
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.
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
-
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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?
|
by: Daniel Sélen 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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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))
{
}
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |