Run VBA Code When Exiting Access (2003 (11.5614.5703))

Topic

New Reply

Seem’s like this ought to be easy, but I’m stuck…

I’d like to execute some VBA code (to restore some application settings I changed at startup) when the user exits the program — I want to make sure it gets run regardless of the method they use to quit the application (File…Exit, X control on the application window, right-click-close on the Task Bar, etc.). This would be analogous to the autoexec macro for startup (except for shutdown) or the Workbook_BeforeClose() event in Excel.

Is there a way to do this?

Reply | Quote

Replies

1. Declare a public variable in a standard module:

Public blnCanClose As Boolean

2. You must have a form (for example a switchboard form) that is open all the time (perhaps hidden when not needed). In the On Unload event of this form, prevent closing it if blnCanClose is False:

Private Sub Form_Unload(Cancel As Integer)
Cancel = (blnCanClose = False)
End Sub

3. Determine how you want to allow the user to quit the database, for example using a Close command button on the switchboard form, and/or using a custom menu item, toolbar button etc. In the code for each of these, call the code you want to execute before closing the database, then set blnCanClose to True, and quit Access.

Reply | Quote

Thanks (once again!), Hans.

Just for my continuing adult education, is the purpose of your Step 2 to prevent closing Access via some means that’s not “trapable” (e.g., the Application window’s close control or the Task Bar Icon right-click-close scheme)? Otherwise, it makes sense — I’ll give it a go.

Thanks again.

Reply | Quote

Whichever way the user tries to close the database, whether using something you provided or not, it will always invoke the On Unload event of the form. If you haven’t set blnCanClose to True (the default value is False), the closing of the form and hence of the database will be canceled.

By the way, it is possible to disable the close button (the x) in the upper right corner of the Access application window – see How to disable the Close button on the Application window and the Exit command on the File menu.

Reply | Quote

Hans,

I tried the scheme you suggested and it works just fine (of course!). Just what I needed.

While coding this, it occurred to me (actually, it occurred to my wife, when I was discribing this to her) why couldn’t I just put my “close down code” in the Unload event of the hidden form? I chose to use the “Splash” form for this (it’s loaded on start up and I use its events to initialize some stuff) — now with your guidance, I just hide it after 5 seconds (using the Timer event) rather than closing it and just leave it there hidden for the duration. It seems like if I put my code in this form’s Unload event, it will get run regardless of which means is used to close the application; the close button, task bar icon, etc. as well as my own controls, command bars, etc. I just need to make sure that no means is provided to unhide or prematurely close that form.

Do you see any downside in this alternative?

Reply | Quote

I sometimes use a hidden form (opened hidden on startup) to compact & backup the back end db file upon application exit, using the hidden form’s Unload event. This works w/o any complications. You would need to be using custom menus/toolbars, with Allow Toolbars/Menus Changes option disabled, otherwise user can invoke the “Unhide…” command from standard Window menu, and open “Unhide Window” dialog, as illustrated. The dialog lists any hidden windows by the window caption – in example, “DB: Database” is hidden database window, while the selected item is caption of a hidden “Backup” form (actual name is “frmBackup”). If you are already taking precautions by disabling Startup options, compiling front end as .MDE, etc, then there should be no way for end user to unhide the hidden form, inadvertently or otherwise.

HTH

Reply | Quote

I sometimes use a hidden form (opened hidden on startup) to compact & backup the back end db file upon application exit, using the hidden form’s Unload event. This works w/o any complications. You would need to be using custom menus/toolbars, with Allow Toolbars/Menus Changes option disabled, otherwise user can invoke the “Unhide…” command from standard Window menu, and open “Unhide Window” dialog, as illustrated. The dialog lists any hidden windows by the window caption – in example, “DB: Database” is hidden database window, while the selected item is caption of a hidden “Backup” form (actual name is “frmBackup”). If you are already taking precautions by disabling Startup options, compiling front end as .MDE, etc, then there should be no way for end user to unhide the hidden form, inadvertently or otherwise.

HTH

Reply | Quote

Yes, that should work too, as Mark notes.

Reply | Quote

Mark & Hans,

Thanks for your help. Science (or whatever this is) marches on…

Reply | Quote

Mark & Hans,

Thanks for your help. Science (or whatever this is) marches on…

Reply | Quote

Yes, that should work too, as Mark notes.

Reply | Quote

Hans,

I tried the scheme you suggested and it works just fine (of course!). Just what I needed.

While coding this, it occurred to me (actually, it occurred to my wife, when I was discribing this to her) why couldn’t I just put my “close down code” in the Unload event of the hidden form? I chose to use the “Splash” form for this (it’s loaded on start up and I use its events to initialize some stuff) — now with your guidance, I just hide it after 5 seconds (using the Timer event) rather than closing it and just leave it there hidden for the duration. It seems like if I put my code in this form’s Unload event, it will get run regardless of which means is used to close the application; the close button, task bar icon, etc. as well as my own controls, command bars, etc. I just need to make sure that no means is provided to unhide or prematurely close that form.

Do you see any downside in this alternative?

Reply | Quote

Whichever way the user tries to close the database, whether using something you provided or not, it will always invoke the On Unload event of the form. If you haven’t set blnCanClose to True (the default value is False), the closing of the form and hence of the database will be canceled.

By the way, it is possible to disable the close button (the x) in the upper right corner of the Access application window – see How to disable the Close button on the Application window and the Exit command on the File menu.

Reply | Quote

Thanks (once again!), Hans.

Just for my continuing adult education, is the purpose of your Step 2 to prevent closing Access via some means that’s not “trapable” (e.g., the Application window’s close control or the Task Bar Icon right-click-close scheme)? Otherwise, it makes sense — I’ll give it a go.

Thanks again.

Reply | Quote

1. Declare a public variable in a standard module:

Public blnCanClose As Boolean

2. You must have a form (for example a switchboard form) that is open all the time (perhaps hidden when not needed). In the On Unload event of this form, prevent closing it if blnCanClose is False:

Private Sub Form_Unload(Cancel As Integer)
Cancel = (blnCanClose = False)
End Sub

3. Determine how you want to allow the user to quit the database, for example using a Close command button on the switchboard form, and/or using a custom menu item, toolbar button etc. In the code for each of these, call the code you want to execute before closing the database, then set blnCanClose to True, and quit Access.

Reply | Quote