Cancel close if null

[PHisaw]

Hi,

I have the following code on main form:

Private Sub Form_Unload(Cancel As Integer)

If Not IsNull(Me.fStatusSubform.Form.[PartsOnOrder]) And
IsNull(Me.fStatusSubform.Form.[ExpectedPartsRecdDate]) Then
Cancel = True
MsgBox "Parts Expected Date Is Required!!"
Exit Sub
End If
End Sub

When there is no date in ExpectedParts, the message pops up and then another
message Close action was cancelled. That is all fine. But if user, tries to
close again, the msgbox message appears and then another message box with
following:

"The Microsoft Jet database engine could not find the object ". Make sure
the object exists and that you spell it's name and the path name correctly."

What is making this second error message appear? What can I do to correct it?

Thanks in advance. Your help is greatly appreciated!!
Pam

 

[Anthos]

I believe that you are using the wrong event for this.
The unload event occurs after you have already closed the form (Please
correct me if I am wrong)

Place this code in the On Close event and your code should work fine.

Hope this helps.

Regards
Anthos

 

[ruralguy via AccessMonster.com]

From Access help:
------------------------------------------------------------------------------
----------
When you open a form, the following sequence of events occurs for the form:

Open > Load > Resize > Activate > Current

If there are no active controls on the form, the GotFocus event also occurs
for the form after the Activate event but before the Current event.

When you close a form, the following sequence of events occurs for the form:

Unload > Deactivate > Close

If there are no active controls on the form, the LostFocus event also occurs
for the form after the Unload event but before the Deactivate event.

 

[Anthos]

Thanks for the correction

 

[George Nicholson]

OK, if i understand correctly, the message box from the Unload event
displays properly in both instances. Its the 2nd message ("Close action was
canceled" & "Cannot find object...") that's the problem.

However, neither of those messages are being triggered within the Unload
event, so you will need to find what is triggering them. Its probably
either in the code that triggers/calls the Unload event (once Unload has
been exited & returns control to the caller), or some other code that runs
after the Unload event is cancelled and exited.

Put a breakpoint in your Unload event & run the app. When the breakpoint is
hit, either use View>CallStack or step through the code to find where the
2nd message is generated (and it may be in different places 1st pass vs 2nd
pass, but I sort of doubt it).

(At first I thought it might be a problem with the subform, and that the
subform somehow got closed when the Parent did not and could not be found on
the 2nd go-round. But if that were the case the 2nd Unload would raise an
error rather than display the Msgbox, so its something else).

HTH,

 

[John W. Vinson]

IsNull(Me.fStatusSubform.Form.[ExpectedPartsRecdDate]) Then
Cancel = True
MsgBox "Parts Expected Date Is Required!!"
Exit Sub
End If

I would suggest that the appropriate event for this kind of validation
is the Form's BeforeUpdate event. The Close or Unload events fire
AFTER the record has already been written to disk - so the damage is
done before you even check for it!

The BeforeUpdate event will be fired by an attempt to close the form;
if you cancel the update, it also cancels the close.

John W. Vinson [MVP]

 

[PHisaw]

George,

Thanks for your reply. I did as you said (not sure at first exactly what I
was doing, but stumbled thru) and I think I may have found the problem and am
hoping you can explain what may be happening. I set the breakpoint, closed
the vb window, returned to form and closed (making sure ExpectedPartsRecdDate
field was empty), the first message (Parts Date required) appeared, code
window opened and it went thru each line to the end and then it highlighted a
line of code for a command button that I use to close the form. Code is
below:

Private Sub CloseForm_Click()
On Error GoTo Err_CloseForm_Click
DoCmd.Close

Exit_CloseForm_Click:
Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub

It highlights "MsgBox Err.Description".

Will you please explain what I need to do to correct this?

Thanks again for your help. It is greatly appreciated.
Pam

OK, if i understand correctly, the message box from the Unload event
displays properly in both instances. Its the 2nd message ("Close action was
canceled" & "Cannot find object...") that's the problem.

However, neither of those messages are being triggered within the Unload
event, so you will need to find what is triggering them. Its probably
either in the code that triggers/calls the Unload event (once Unload has
been exited & returns control to the caller), or some other code that runs
after the Unload event is cancelled and exited.

Put a breakpoint in your Unload event & run the app. When the breakpoint is
hit, either use View>CallStack or step through the code to find where the
2nd message is generated (and it may be in different places 1st pass vs 2nd
pass, but I sort of doubt it).

(At first I thought it might be a problem with the subform, and that the
subform somehow got closed when the Parent did not and could not be found on
the 2nd go-round. But if that were the case the 2nd Unload would raise an
error rather than display the Msgbox, so its something else).

HTH,

Hi,

I have the following code on main form:

Private Sub Form_Unload(Cancel As Integer)

If Not IsNull(Me.fStatusSubform.Form.[PartsOnOrder]) And
IsNull(Me.fStatusSubform.Form.[ExpectedPartsRecdDate]) Then
Cancel = True
MsgBox "Parts Expected Date Is Required!!"
Exit Sub
End If
End Sub

When there is no date in ExpectedParts, the message pops up and then
another
message Close action was cancelled. That is all fine. But if user, tries
to
close again, the msgbox message appears and then another message box with
following:

"The Microsoft Jet database engine could not find the object ". Make sure
the object exists and that you spell it's name and the path name
correctly."

What is making this second error message appear? What can I do to correct
it?

Thanks in advance. Your help is greatly appreciated!!
Pam

 

[PHisaw]

John,

Thanks for your reply. I did a search thru the posts and came up with
several that used the UnloadEvent, so I assumed that is where it should go.

Anyway, I tried BeforeUpdate on both the main and subforms and nothing
happened. It allowed the form to close with no messages.

If you have further suggestions, I would be most happy to try them.

Thanks again,
Pam

IsNull(Me.fStatusSubform.Form.[ExpectedPartsRecdDate]) Then
Cancel = True
MsgBox "Parts Expected Date Is Required!!"
Exit Sub
End If

I would suggest that the appropriate event for this kind of validation
is the Form's BeforeUpdate event. The Close or Unload events fire
AFTER the record has already been written to disk - so the damage is
done before you even check for it!

The BeforeUpdate event will be fired by an attempt to close the form;
if you cancel the update, it also cancels the close.

John W. Vinson [MVP]

 

[George Nicholson]

You can easily modify error handlers to handle different errors in specific
ways. In this case, you want to ignore a specific error. Since you know it
will occur under certain conditions, it's really a non-error as far as you
are concerned:

Err_CloseForm_Click:
Select Case Err.Number
Case 2501
' Do Nothing: "Close Action was Cancelled"
Resume Next
Case Else
MsgBox Err.Number & " " & Err.Description
Resume Exit_CloseForm_Click
End Select

Note that the msgbox now includes the Error Number. If you are getting
errors other than #2501, you can easily add those numbers to the Case if you
so chose.

It's possible (but not likely) that you may be getting something other than
2501. That's what I got but your mileage may vary. Simply change or add to
the Case statement as appropriate.

HTH,

George,

Thanks for your reply. I did as you said (not sure at first exactly what
I
was doing, but stumbled thru) and I think I may have found the problem and
am
hoping you can explain what may be happening. I set the breakpoint,
closed
the vb window, returned to form and closed (making sure
ExpectedPartsRecdDate
field was empty), the first message (Parts Date required) appeared, code
window opened and it went thru each line to the end and then it
highlighted a
line of code for a command button that I use to close the form. Code is
below:

Private Sub CloseForm_Click()
On Error GoTo Err_CloseForm_Click
DoCmd.Close

Exit_CloseForm_Click:
Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub

It highlights "MsgBox Err.Description".

Will you please explain what I need to do to correct this?

Thanks again for your help. It is greatly appreciated.
Pam

OK, if i understand correctly, the message box from the Unload event
displays properly in both instances. Its the 2nd message ("Close action
was
canceled" & "Cannot find object...") that's the problem.

However, neither of those messages are being triggered within the Unload
event, so you will need to find what is triggering them. Its probably
either in the code that triggers/calls the Unload event (once Unload has
been exited & returns control to the caller), or some other code that
runs
after the Unload event is cancelled and exited.

Put a breakpoint in your Unload event & run the app. When the breakpoint
is
hit, either use View>CallStack or step through the code to find where the
2nd message is generated (and it may be in different places 1st pass vs
2nd
pass, but I sort of doubt it).

(At first I thought it might be a problem with the subform, and that the
subform somehow got closed when the Parent did not and could not be found
on
the 2nd go-round. But if that were the case the 2nd Unload would raise an
error rather than display the Msgbox, so its something else).

HTH,

Hi,

I have the following code on main form:

Private Sub Form_Unload(Cancel As Integer)

If Not IsNull(Me.fStatusSubform.Form.[PartsOnOrder]) And
IsNull(Me.fStatusSubform.Form.[ExpectedPartsRecdDate]) Then
Cancel = True
MsgBox "Parts Expected Date Is Required!!"
Exit Sub
End If
End Sub

When there is no date in ExpectedParts, the message pops up and then
another
message Close action was cancelled. That is all fine. But if user,
tries
to
close again, the msgbox message appears and then another message box
with
following:

"The Microsoft Jet database engine could not find the object ". Make
sure
the object exists and that you spell it's name and the path name
correctly."

What is making this second error message appear? What can I do to
correct
it?

Thanks in advance. Your help is greatly appreciated!!
Pam

 

[PHisaw]

George,

I was trying different ideas and just completely removed the error portion
of the Click command and now it hangs up on the DoCmd.Close portion of this
event. Can I not use the Close command button on this form with the
Cancel=true portion of the Unload event for the subform?

Pam

You can easily modify error handlers to handle different errors in specific
ways. In this case, you want to ignore a specific error. Since you know it
will occur under certain conditions, it's really a non-error as far as you
are concerned:

Err_CloseForm_Click:
Select Case Err.Number
Case 2501
' Do Nothing: "Close Action was Cancelled"
Resume Next
Case Else
MsgBox Err.Number & " " & Err.Description
Resume Exit_CloseForm_Click
End Select

Note that the msgbox now includes the Error Number. If you are getting
errors other than #2501, you can easily add those numbers to the Case if you
so chose.

It's possible (but not likely) that you may be getting something other than
2501. That's what I got but your mileage may vary. Simply change or add to
the Case statement as appropriate.

HTH,

George,

Thanks for your reply. I did as you said (not sure at first exactly what
I
was doing, but stumbled thru) and I think I may have found the problem and
am
hoping you can explain what may be happening. I set the breakpoint,
closed
the vb window, returned to form and closed (making sure
ExpectedPartsRecdDate
field was empty), the first message (Parts Date required) appeared, code
window opened and it went thru each line to the end and then it
highlighted a
line of code for a command button that I use to close the form. Code is
below:

Private Sub CloseForm_Click()
On Error GoTo Err_CloseForm_Click
DoCmd.Close

Exit_CloseForm_Click:
Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub

It highlights "MsgBox Err.Description".

Will you please explain what I need to do to correct this?

Thanks again for your help. It is greatly appreciated.
Pam

OK, if i understand correctly, the message box from the Unload event
displays properly in both instances. Its the 2nd message ("Close action
was
canceled" & "Cannot find object...") that's the problem.

However, neither of those messages are being triggered within the Unload
event, so you will need to find what is triggering them. Its probably
either in the code that triggers/calls the Unload event (once Unload has
been exited & returns control to the caller), or some other code that
runs
after the Unload event is cancelled and exited.

Put a breakpoint in your Unload event & run the app. When the breakpoint
is
hit, either use View>CallStack or step through the code to find where the
2nd message is generated (and it may be in different places 1st pass vs
2nd
pass, but I sort of doubt it).

(At first I thought it might be a problem with the subform, and that the
subform somehow got closed when the Parent did not and could not be found
on
the 2nd go-round. But if that were the case the 2nd Unload would raise an
error rather than display the Msgbox, so its something else).

HTH,

Hi,

I have the following code on main form:

Private Sub Form_Unload(Cancel As Integer)

If Not IsNull(Me.fStatusSubform.Form.[PartsOnOrder]) And
IsNull(Me.fStatusSubform.Form.[ExpectedPartsRecdDate]) Then
Cancel = True
MsgBox "Parts Expected Date Is Required!!"
Exit Sub
End If
End Sub

When there is no date in ExpectedParts, the message pops up and then
another
message Close action was cancelled. That is all fine. But if user,
tries
to
close again, the msgbox message appears and then another message box
with
following:

"The Microsoft Jet database engine could not find the object ". Make
sure
the object exists and that you spell it's name and the path name
correctly."

What is making this second error message appear? What can I do to
correct
it?

Thanks in advance. Your help is greatly appreciated!!
Pam

 

[George Nicholson]

You *can't* remove the error handler and expect your program to run.

DoCmd Close will cause the Unload event to fire. When you Cancel the Unload
event, this passes a "Action canceled" message/error back to the DoCmd.Close
command. This is desired and unavoidable behavior.

If the error is handled as suggested you should be ok. Removing the error
handler means you now have an unhandled error and the program will hang. If
you don't want to deal with handling the error you'll need to find another
approach to do what you're trying to do.

HTH,

George,

I was trying different ideas and just completely removed the error portion
of the Click command and now it hangs up on the DoCmd.Close portion of
this
event. Can I not use the Close command button on this form with the
Cancel=true portion of the Unload event for the subform?

Pam

You can easily modify error handlers to handle different errors in
specific
ways. In this case, you want to ignore a specific error. Since you know
it
will occur under certain conditions, it's really a non-error as far as
you
are concerned:

Err_CloseForm_Click:
Select Case Err.Number
Case 2501
' Do Nothing: "Close Action was Cancelled"
Resume Next
Case Else
MsgBox Err.Number & " " & Err.Description
Resume Exit_CloseForm_Click
End Select

Note that the msgbox now includes the Error Number. If you are getting
errors other than #2501, you can easily add those numbers to the Case if
you
so chose.

It's possible (but not likely) that you may be getting something other
than
2501. That's what I got but your mileage may vary. Simply change or add
to
the Case statement as appropriate.

HTH,

George,

Thanks for your reply. I did as you said (not sure at first exactly
what
I
was doing, but stumbled thru) and I think I may have found the problem
and
am
hoping you can explain what may be happening. I set the breakpoint,
closed
the vb window, returned to form and closed (making sure
ExpectedPartsRecdDate
field was empty), the first message (Parts Date required) appeared,
code
window opened and it went thru each line to the end and then it
highlighted a
line of code for a command button that I use to close the form. Code
is
below:

Private Sub CloseForm_Click()
On Error GoTo Err_CloseForm_Click
DoCmd.Close

Exit_CloseForm_Click:
Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub

It highlights "MsgBox Err.Description".

Will you please explain what I need to do to correct this?

Thanks again for your help. It is greatly appreciated.
Pam

:

OK, if i understand correctly, the message box from the Unload event
displays properly in both instances. Its the 2nd message ("Close
action
was
canceled" & "Cannot find object...") that's the problem.

However, neither of those messages are being triggered within the
Unload
event, so you will need to find what is triggering them. Its probably
either in the code that triggers/calls the Unload event (once Unload
has
been exited & returns control to the caller), or some other code that
runs
after the Unload event is cancelled and exited.

Put a breakpoint in your Unload event & run the app. When the
breakpoint
is
hit, either use View>CallStack or step through the code to find where
the
2nd message is generated (and it may be in different places 1st pass
vs
2nd
pass, but I sort of doubt it).

(At first I thought it might be a problem with the subform, and that
the
subform somehow got closed when the Parent did not and could not be
found
on
the 2nd go-round. But if that were the case the 2nd Unload would raise
an
error rather than display the Msgbox, so its something else).

HTH,

Hi,

I have the following code on main form:

Private Sub Form_Unload(Cancel As Integer)

If Not IsNull(Me.fStatusSubform.Form.[PartsOnOrder]) And
IsNull(Me.fStatusSubform.Form.[ExpectedPartsRecdDate]) Then
Cancel = True
MsgBox "Parts Expected Date Is Required!!"
Exit Sub
End If
End Sub

When there is no date in ExpectedParts, the message pops up and then
another
message Close action was cancelled. That is all fine. But if user,
tries
to
close again, the msgbox message appears and then another message box
with
following:

"The Microsoft Jet database engine could not find the object ".
Make
sure
the object exists and that you spell it's name and the path name
correctly."

What is making this second error message appear? What can I do to
correct
it?

Thanks in advance. Your help is greatly appreciated!!
Pam