Call Form_Unload From BeforeUpdate event

[Melissa]

I have the following code in a form's BeforeUpdate event:
If <Condition> Then
MsgBox ........
Cancel = True
Call Form_Unload(True)
End If

And this code for the form's Unload event:
Public Sub Form_Unload(Cancel As Integer)
Cancel = True
End Sub

1. Why doesn't this code prevent the form from closing when <condition> is
true?

2. Do I need to put Cancel = True in the Unload event since the Call
statement specifies Cancel as True?

Thanks!

Melissa

 

[Stewart Allen]

Hi Melissa,

Don't call the Unload event from the BeforeUpdate event. The Cancel event in
your true part of your If statement prevents the record from being updated
but won't stop the form from being closed down. To stop the form from being
closed until all criteria has been met use the form's dirty property in
conjunction with your condition in the form's unload event.

Private Sub Form_Unload(Cancel As Integer)
If Me.Dirty And <Condition> Then
MsgBox ......
Cancel = True
End If
End Sub

Stewart

 

[Melissa]

When there is both a BeforeUpdate procedure and an Unload procedure, both
procedures run when someone tries to close the form. You get two messages about
the <condition> being true. That's why I'm trying to run the Cancel Unload in
the BeforeUpdate.

You state "....but won't stop the form from being closed down...." Why won't
calling the Unload procedure from the form's BeforeUpdate event keep the form
from closing?

Melissa

 

[Wayne Morgan]

You will notice that Cancel is one of the parameters of the Unload
procedure. When you call this procedure from another procedure, the code
gets run but the parameter's value doesn't sent to where it needs to be.
When the form starts to close, it calls the Unload event procedure and then
checks the value of the Cancel parameter when the procedure is finished.
This is what cancels the close.

As far as getting both messages, since both events will fire, just put the
message in the BeforeUpdate event and leave it out of the Unload event. You
will still need the rest of what Stewart mentioned. When the user tries to
close the form, the BeforeUpdate will fire and give the message then the
Unload event will fire and silently cancel the close, but the user still got
the message because of the BeforeUpdate event. Another option would be to
Undo the changes and let the form close. If you do this, you will be
abandoning the changes that were made to the record.

 

[Melissa]

Wayne,

<< If Me.Dirty And <Condition> Then >>

This is always false because the current record is a new record and does not get
dirty no matter how many fields are entered. Therefore, the form always closes!

Still need help!

Melissa

 

[Wayne Morgan]

I just did some testing. What I'm actually finding is that before the Unload
event runs, Access generates an error that says you can't save the record at
this time and do you want to close the form anyway. The error has a Yes and
No button on it. If you say Yes, Access clears the changes to the form and
closes it. This is why the form isn't dirty, the changes have been cleared.
If you say No, the form stays open and, since the Update was cancelled, you
get to go back and make the changes. Either way, the record won't be saved
with the bad data. I tried to find where this error is being generated so
that I could intercept it, but I couldn't. It isn't in the form's Error
event, nor is it in the BeforeUpdate or Unload event.

One way around this is to remove the close button from the form (form's
Properties sheet, Format tab) and place your own command button on the form
to close it. You could do the checks for valid data and if everything is ok
then save the record and close the form; otherwise, popup a message box
explaining what needs to be done and skip the close.

 

[Wayne Morgan]

Melissa,

I did some more checking. The error I mentioned is Error Number 2169 in the
form's Error event. You could use this to set a flag variable that you check
the value of in the Unload event and cancel the unload. However, by
canceling the error message in the event, the Yes option is assumed by
Access and it clears the changes so even though you don't close the form,
all the changes are gone and will have to be reentered by the user.