Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

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

    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
    Attached Images Attached Images

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Yes, that should work too, as Mark notes.

  8. #8
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Mark & Hans,

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •