Block x in Form Closure

[pjgalloway]

{This question was moved from the article How to Close a Main Form Without Saving Changes of any Subforms}

What about when the user clicks the red "X" button to close the form? Is there any way to automatically discard these changes to the form/subform design? Or do I just have to set turn off the "close button" and force the user to use my custom "close" button?

[twinnyfo]

PJ,

I would think that you could call this sub from the OnClose Event of your Form (which is irrespective of any Close Command Buttons).

When I build Forms, I have a tendency to have a set series of actions that are performed in the OnClose event of the Form (i.e. call up the Switchboard), but when I click the Close Button, there may be some other things the Form wants to do befor it actually closes (like saving certain bits of information or downloading reports).

Although I haven't had too many problems of Access redesigning my SubForms, at least now, if I notice any such activity, I will have a way to address it.

Thanks for your insights, NeoPa!

[NeoPa]

Hi PJ.

Twinny has pretty-well answered as I would have. The Form_Close() event procedure is triggered by both clicking on the red "X" and when DoCmd.Close() is used. Another possible event procedure that could be used is Form_Unload().

[pjgalloway]

Access asks to save any changes to form design before any of the form events (including Form_Close) are triggered. So code there is too late. In particular, when a subform is displayed as a datasheet, the user can show/hide columns, rearrange columns, etc. These are all design changes that Access wants to save. I want to avoid that prompt from Access

[twinnyfo]

If you don't want that prompt to pop up to save the form design (which is induced by any changes made in datasheet view), then, when the form closes, turn off the prompt to save:

DoCmd.Close acForm, Me.Form.Name, acSaveNo

That should help in this case.....

[pjgalloway]

When the user clicks the red X, Access prompts for saving design changes BEFORE the Form_Close event.

[twinnyfo]

PJ,

Another reason I always disable that pesky little button. I always recommend that the VBA should control everything that goes on in the form. It may be possible to trap this event, but since I don't build my forms this way, I admit, it is unknown to me.

[pjgalloway]

Thanks. I was just hoping there was a way through Windows API to intercept the click on the red X. I have disabled it on the form with the datasheet subform but wanted some way to have it for consistency with all the other forms.

[NeoPa]

In my previous post (#3) I mention both the Form_Unload() and the Form_Close() event procedures. The major difference between the two is that the Unload event can be canceled, but the Close event can't.

To ensure that no design changes are saved automatically when the form is closed have a Command Button on your form that handles closing. In there call for the close with code similar to :

Call DoCmd.Close(ObjectType:=acForm, _
                 ObjectName:=Me.Name, _
                 Save:=acSaveNo)

It's also important to flag that the close has been called from your trusted routine rather than any other method (Red "X"; Ctrl-F4; etc.) so define a Boolean variable that is Private to the Form module which is typically unset (False). In this routine add a line to set it to True.

In Form_Unload() check this variable and set the Cancel parameter to Not this variable. This will ensure that when any other method of closure is attempted the Unload will cancel and the Close won't proceed. Only your procedure will allow for the form to close, and it will ensure changes are never saved.

How to Close a Main Form Without Saving Changes of any Subforms has information on how to ensure nothing is upset when dealing with subforms.

[NeoPa]

I've just reread post #1 to be reminded that your question originally started from the thread I just relinked you to. As the answer to everything you've asked is already encapsulated within that thread I must conclude that your difficulty is in interpreting what is in there rather than finding the information. Feel free to explain what it is that's confusing you and I'm sure we can help you understand it better.