Problem with Cancelling a Form

[Petrol]

I have a form with which users can create a new record in the underlying table. A subform on it creates a corresponding entry in another table. On the main form there is a Cancel button which allows the user to abort the entry at any point in the process.

I need to perform some consistency checks on entries in the subform. At present this is done in a BeforeUpdate event procedure on the subform. The problem is that when the user clicks the Cancel button on the main form, the subform loses focus and its BeforeUpdate event fires before I can do any processing of the Cancel button (such as setting an "Ignore errors" flag). So the consistency checks are made and an error message produced even though the user is trying to abort the whole operation.

Is there any way I can avoid this?
(I can't put the subform consistency checks into the main form's Before/AfterUpdate events because these fire before the subform has been filled in; and I can't put them into the main form's Close event because then it's too late to allow the user to make any correction. I tried the main form's Unload event, but it couldn't find the subform's controls.)

[NeoPa]

It seems to me that this could be one of those very rare occasions when an unbound form might prove beneficial. It's certainly true that Subform records will save to table before the Cancel button can be processed.

Maybe I can lay out some alternatives to explore :

1. Bound Form/Subform as you currently have it but attempt to determine, within the Form_BeforeUpdate() event procedure of the Subform, if the Cancel button has been clicked.
2. Create a temporaray table, or tables, that mirror the main tables currently in use. Bind the Form & Subform to these temporary tables and copy the data across when, and only when, they've completed successfully. NB. This would work easily only for adding new records. Editing existing ones would have to be handled differently; Perhaps by changing the record sources of the two Forms based on Me.NewRecord of the main Form.
3. Design everything as unbound and build the records from the Controls. Doesn't work well with multiple records within the same main entity.

I hope that's some help. This is a very rare situation which I've never had to think about in anger.

[Petrol]

Thank you, NeoPa. It's obviously a pretty complex case. (At least that makes me feel a bit better about not being able to solve it myself!)
Suggestion 1 sounds easiest, neatest and cleanest - but I haven't found any way of doing it. When the user clicks the Cancel button on the main form, the subform loses focus and its BeforeUpdate event fires before the Cancel button Click event :(. Is there another way to find out about the Cancel?

Suggestion 2 sounds draconian - the main form is bound to the principal table of the project, with nearly 4000 records of 45 fields each. However, this form is only used for new entries - I don't need to update existing ones here. I'll have to look at this, but I quail at the thought!

Solution 3 is a bit beyond me at present. The system has been in production for months and I've only just had to add the consistency checks, so I'm hesitant to embark on a major redesign. I'd need to investigate how that could be done.

I did think about a fourth solution, to provide a separate Cancel button on the subform and tell users they must be careful to click on the button in the bit of the form which has the focus, but that's inconvenient for users and fraught with danger.

Do you think there's any future in pursuing the option I tried, of putting the consistency checks in the Unload event of the main form and returning Cancel=True if the user is cancelling? The idea would be to stop the main form Close procedure and return to editing. Would that work? I tried it, but I wasn't able to reference the relevant subform controls from the main form Unload procedure. Maybe I was not doing this correctly.

[NeoPa]

Petrol:
Do you think there's any future in pursuing the option I tried, of putting the consistency checks in the Unload event of the main form and returning Cancel=True if the user is cancelling?

No. By then the record's already been saved. What could you do about it at that point?

You could try the Cancel button on the Subform option I suppose. You'd also need code in the Subforms Form_BeforeUpdate() event procedure to cancel the update unless previously set to be allowed. IE. The Cancel button would cancel the changes to the record and a separate Save button would be needed to set that the update be allowed. Something like a Form level flag.

[Petrol]

Um. Yes, the whole thing is much more difficult than I originally envisaged.
I'll be away from the project for a few days and then will have to carefully look at all the options - most of which you have provided. So thanks again. I'll repost when I finally get it sorted.
Peter

[NeoPa]

Good luck anyway Peter. Nothing trivial about this one.