Running an Event Procedure On Form_Close()

B

Byzantine

I've developed a class of forms that it is hoped will be used to edit records
in Access and am trying to run an event procedure when closing the form (the
Form on Close event). I.e when users click on the X at the top right of the
form.

I want to try and give my users the option of leaving the form eg:

Do you want to abort without saving ? If 'yes' is selected the form will
close. If 'no' is selected I'd want the form to remain open rather than
closing. This is easy to do from a button on the form but I can't seem to get
it to work when using the X to close.

Do I have to disable the X and do it with a button on the form, or am I
missing something?

Any help or innovative workarounds are much appreciated. Many thanks for any
help.

Regards

Byz
 
B

Brendan Reynolds

The Close event is to late to prevent the closing of the form, you need to
use the Unload event and set the Cancel argument to True. If you want to
give users the opportunity to abort the saving of the record, though (and
assuming we're talking about a bound form) the Unload event is too late for
that, you need to use the BeforeUpdate event.
 
B

Byzantine

Thanks for this Brendan

The form is an unbound form and I've done as you suggest, but the the form
still closes even when 'No' is selected:

Form_Unload()
If MsgBox("Do you want to close this form ?", vbQuestion + vbYesNo,
"Discard") = vbYes Then
Cancel=True
DoCmd.GoToControl "CompanyID"
End If

I've even substituted the Cancel=True code line with the DoCmd.CancelEvent,
without success.

Any ideas? Many thanks.

Regards

Phil
 
B

Brendan Reynolds

There are two possible problems here.

a) Are you using the form's Unload event procedure? If the code you have
posted is the real code you are actually using, then you are not, because
the signature is not correct. It should look like ...

Private Sub Form_Unload(Cancel As Integer)

b) Your code is setting Cancel = True when Yes is selected instead of when
No is selected.
 
B

Byzantine

Yes, a mistake on my part. Your original advise works perfectly.
Many thanks Brendan.
 
Top