CancelEvent not working with BeforeUpdate Event

[Andy]

I have a CancelEvent command in the BeforeUpdate event in a single form. The
form happens to be a NewRec form so would normally create a new record if no
Cancel actioned. The CancelEvent command is run only if a pubCancel public
variable is set to True.

This was working fine until I added a Calendar control in a subform on the
main form to set one of my date fields. If I set a date in this way and then
close the main form with the pubCancel variable set to True, the CancelEvent
command is run but a new record gets created (the date field is Null). I need
to stop a new record being created in these circumstances.

Any thoughts?

 

[Jeanette Cunningham]

Hi Andy,
it sounds like there is a new record created in the subform.
Once there is a record in the subform, you can't cancel the main form's
before update.
The main form saves the new record before the new record can be created in
the subform.
Somehow the calendar must be creating a new record in the subform.

Check the code that calls the calendar and saves the result to the subform.
If you are unable to change this code, then another approach is to delete
both the child record and the main form record whenever the public Cancel
event in the main form is called.
So the cancel event code changes from undoing a record to deleting 2
records.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Andy]

Thanks Jeanette - I understand your points about the Calendar subform.

I managed to solve the problem by removing the subform and adding the
Calendar control back in as a control on the main form.

Andy.