VBA - How to run code on closing application?

[Robin]

Is there a way to trap this event (however caused - close button, file menu,
quit etc.), so that vb code or a macro can run before Access closes?

The database in question is already set to compact on close, so Access knows
how to do it!

Thanks for any ideas.

Robin

 

[Guest]

Create a form that opens on startup and is invisible. Put your code in the
Close event of that form. If you already have a form that opens on startup,
change it to start the invisible form, and open you current startup form from
the new one.

 

[Robin]

Excellent! Thank you for the quick response. And it works.

I realised from what you said that closing the database application creates
close events for any form that's open, so have used the main form that is
normally open rather than creating a hidden one. Closing the main form
creates the same event (which may not be appropriate in all cases of course,
so a hidden form would be better in that case) but this is fine for what I
needed.

Again, thanks!

Robin

 

[Guest]

Your observation is correct. That is why I suggested a hidden form. That
way, any other form that is closed will not affect what you are doing.
Now, another cute trick that works off this hidden form thing. It has been
observed and stated by some Access Gurus that an Access application performs
faster if there is a persistent connection to the back end database. That
means, when you start up, open some table and keep it open as long as the
application is running. So, if you are using a hidden form, then you can
open a table (I use a one row meaningless table), and close it in your form's
Close event.

Now let's get really crazy!!! Say you want the ability to force all users
out of your application. Put a timer event in the hidden form (say once
every minute or so) and when the timer fires (in the form Timer event) read
the one field in the one record in the table and if it says "LOGOFF" or
whatever value you want to use, Warn the user you are shutting down the
application. Then you can have a counter in the timer event that keeps track
of how many mintues it has been since LOGOFF was detected, and when that time
expires, close the application.

 

[Robin]

Now, another cute trick that works off this hidden form thing...

I'll try that - the backend is really slow so anything that might help is
worth a try.

Now let's get really crazy!!! Say you want the ability to force all users
out of your application. Put a timer event in the hidden form (say once
every minute or so) and when the timer fires (in the form Timer event)
read
the one field in the one record in the table and if it says "LOGOFF" or
whatever value you want to use, Warn the user you are shutting down the
application. Then you can have a counter in the timer event that keeps
track
of how many mintues it has been since LOGOFF was detected, and when that
time
expires, close the application.

I don't really need a log off process but... I can think of other uses for
this, so thanks for the crazy bit!

 

[John Mishefske]

I'll try that - the backend is really slow so anything that might help is
worth a try.

There is one great advantage is that this is the first form opened and thus
will be the last form closed so this hidden form's Unload event is perfect
for running those final tasks.