Can the "Close" (x) button event on a form be altered with VBA code? (1 Viewer)

[CranstonSnord]

Note to all responders: This is purely a home learning database and there is no desperate corporate urgency to answer the question.
I have a simple test form that I use to help me determine event order when following a specific set of steps on the form, i.e., a "happy path" for data entry.

I've set up each event for the form with a conditional compilation argument code block that looks like this (of course changing the print statement to match each event):

Private Sub Form_Activate()
#If Testing = 1 Then
Debug.Print "frmTest Activate Event"
#End If
End Sub

I then open the form, enter the data in the order I need to test for, and review what the immediate window tells me as to the order of events. The process I am currently trying to code for is as follows.

1. I enter an album ID, then <Tab> to the next control on the form.
2. I enter a simple title.
3. I click on the "Close" button in the upper right corner of the form.

Here are the events that take place immediately after the "Close" button is clicked.

txtALBUM_TITLE textbox BeforeUpdate Event
txtALBUM_TITLE textbox AfterUpdate Event
frmTest BeforeUpdate Event
frmTest Error Event
frmTest Error Event
frmTest Undo Event
frmSubTest txtTRACK_TITLE Exit Event
txtALBUM_TITLE textbox Exit Event
txtALBUM_TITLE textbox LostFocus Event
frmTest Unload Event
frmTest Deactivate Event
frmTest Close Event
frmSubTest Form Unload Event
frmSubTest Form Close Event

All 3 of the text boxes on the form (ALBUM_ID, ALBUM_TITLE, and RELEASE_YEAR) are required fields in the table that the form is bound to. During a normal data entry event, I rely on the form's BEFORE UPDATE event to test to ensure that these 3 controls are all populated prior to moving on to the sub form. However, in this test case I'm simulating the user (me) abandoning the data entry process. What I would like to have happen is for Access to post a message that says something to the effect of, "You have unsaved content. Select Continue to proceed or Quit to abandon your changes."

My problem is, I can't figure out how to trap for the clicking on the "Close" button. There are no errors thrown until the ERROR event is reached, and although I can trap for those errors there, I still get a message from my BEFORE UPDATE event that the RELEASE_YEAR control hasn't been populated. I need a way to programmatically determine that the "Close" button has been clicked, but none of the form events above (including CLICK, MOUSE DOWN, MOUSE UP, etc.) fire prior to the form's BEFORE UPDATE event.

Is there a way to immediately know that the "Close" button has been selected so that I can divert the program flow past the validations I've built into the BEFORE UPDATE event? For form simplicity sake I would prefer not to have to add a "Close" command button, but if that's the only way around this situation then so be it.

 

[Ranman256]

not the close, but you can CANCEL the close with the Form_Unload event.
or do stuff before the close.

 

[The_Doc_Man]

As Ranman256 suggests, you can trap an Unload, which precedes a Close.

Form.Unload event (Access)

Office VBA reference topic

learn.microsoft.com

If you get past the Unload without stopping it, the form WILL close.

 

[plog]

Make a fake close button.

Add a gray square across the top .25 inches of your form, add a label on the right sight of that with just 'X' in it. Add a label to the right for the form title. Make that area look just like the native form border Access puts around all forms. Then disable the one Access adds (Properties->Border Style = None).

Then make that X label clickable and add whatever code you want to it.

 

[The_Doc_Man]

On re-reading the (rather lengthy) question, I realize there is another way to interpret your question.

IF you are asking how to know that the Form Close button (that is the X in the upper right of a "normal" form) was used then I think you can't know that because there is no _Click event for that particular button that you can see. The reason is that it is a button YOU didn't build. Access itself built it. Strictly speaking, there is no way to know that a form is closing because of that [X] button or because some code ran a DoCmd event to close the form. HOWEVER...

You can build a Form Close button that sets a flag and then issues a Close command. In the Form_Open event, always clear the flag initially. Then put code in the Unload event that checks whether that flag was set. If you trap an Unload and the flag was NOT set, you can tell that it was one of the other ways - and can prevent the form from closing by returning the Cancel parameter set to -1 (or actually, +1 also works).

There is also a property you can set on forms to not show the "standard" navigation controls (.NavigationButtons, a T/F property) and I think you can get rid of the Close flag too, using the .ControlBox function (which governs Close, Minimize, and Maximize). If you then create your own special command buttons, you can assure which control is active because each one can have its own _Click event separate from any other buttons.

EDITED BY The_Doc_Man for clarity.

 

[CranstonSnord]

Thanks to The_Doc_Man and plog for your help! Sorry for the lengthy nature of the question, I couldn't figure out how to pose it in 25 words or less. Based on your responses I think I will mark this question as solved. plog, I think your fix is a great idea, and I'll give it a try.

 

[Isaac]

Make a fake close button.

Add a gray square across the top .25 inches of your form, add a label on the right sight of that with just 'X' in it. Add a label to the right for the form title. Make that area look just like the native form border Access puts around all forms. Then disable the one Access adds (Properties->Border Style = None).

Then make that X label clickable and add whatever code you want to it.

Exactly the type of thing I would usually do. Much easier to control what happens on close.

 

[Pat Hartman]

Why not give the user a positive action with a Cancel button? Then you don't need to guess what he wants to do. If he presses cancel, you undo the changes and close the form. If any other action gets you to the form's BeforeUpdate event, you assume the user intends to save.

Unless you remove the x option, you don't have the ability to put a control over it because the x is in the form's border. You can remove the x option and then make a header that looks like the top of the form with the x on the far right. But I would rather just make a positive option to save or discard changes.

 

[June7]

I disable/remove: form X close, right click menu, File Exit (modify ribbon) and even have code to prevent app X close. Forms have custom buttons for Save, Cancel, etc.

 

[Pat Hartman]

even have code to prevent app X close

I'm sure no one wants to hear old war stories so I won't tell you about how one of my programmers took down the entire CICS network of about 4,000 terminals by not allowing any way out of his particular CICS transaction.

All I can say is - not letting people out just makes them do stupid things like pushing the power off button.

 

[The_Doc_Man]

Absolutely concur, Pat. Which is why in my discussion I pointed out that you would build a special CLOSE command button because you just lost your only other way out.

 

[June7]

User exits db from main menu form which is never closed.

Review https://www.fmsinc.com/microsoftaccess/startup/preventclose.asp