How to circumvent a Form_Unload event after issuing an Application.Quit command

Consider the attached file.
I has a form with the following code behind it
Option Compare Database
Option Explicit
Const timeToRun As Date = #11:17:00 AM#
Const DelayFromLastActivity As Long = 1
'minutes of delay
Public TheInterval As Long
Public db As Database
Public rs As Recordset
Dim SkipMessage As Boolean

Private Sub Form_Activate()

If DateDiff("s", Time(), timeToRun) < DelayFromLastActivity Then
    TheInterval = DelayFromLastActivity * 60 * 1000
Else
    TheInterval = DateDiff("s", Time(), timeToRun) * 1000
End If
Me.TimerInterval = TheInterval

End Sub

Private Sub Form_Current()
'this should only fireon opening because this form is unbound
'MsgBox "Current Event fired"
'MsgBox GetLocalTimeFromGMT(timeToRun)
'MsgBox ConvertLocalToGMT(timeToRun)

If DateDiff("s", Time(), timeToRun) < DelayFromLastActivity Then
    TheInterval = DelayFromLastActivity * 60 * 1000
Else
    TheInterval = DateDiff("s", Time(), timeToRun) * 1000
End If
Me.TimerInterval = TheInterval

End Sub



Private Sub Form_Timer()

Set db = CurrentDb
Set rs = db.OpenRecordset("select *  from tblTimes where 1=2;", dbOpenDynaset, dbSeeChanges)
With rs
    .AddNew
    !thedatetime = Now()
    !theuser = ReturnUserName
    !thecomputer = ReturnComputerName
    .Update
    Me.txtTimer.Value = Now()
    Me.txtTimer.Requery
End With
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
SkipMessage = True
Application.Quit acQuitSaveAll
SkipMessage = True
End Sub


Private Sub Form_Unload(Cancel As Integer)
Dim response As Integer
If SkipMessage = False Then
    response = MsgBox("Did you really intend to quit The Program? Yes to Quit, no to remain in The Program", vbYesNo + vbCritical, "Quit?")
    If response = vbNo Then
        Cancel = True
    End If
End If
End Sub

Open in new window


The application is designed to close itself based on two constants
Const timeToRun As Date = #11:17:00 AM#
Const DelayFromLastActivity As Long = 1
Adjust these in your tests as needful.

Now, I want the application to close DelayFromLastActivity minutes after the last form activity after timeToRun time.
It does this swimmingly.

If the Timer event is NOT the cause of the form's closure, I need a message allowing the user to bail on the closure.
This, too, works swimmingly.

However, you can watch this go down in the Timer event


SkipMessage = True 'skip the prompt in the unload
Application.Quit acQuitSaveAll
SkipMessage = True ' dammit I said skip the prompt in the unload


SkipMessage gets set to False at the end of the Timer event, regardless.
So, when I want the Timer to provide an automated close, instead I get a message box whose response gets ignored.
I don't want the message box to fire if the Timer event is the cause of the Unload()
Making SkipMessage a Public variable of  a code module doesn't help the issue, either.

So, what does Application.Quit do to module-level variables and how can I get clean, automated shutdown?
AutoClose.mdb
LVL 26
Nick67Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

omgangIT ManagerCommented:
Nick, a simple idea (maybe?) is to reverse the logic on the Boolean SkipMessage, i.e. change it so that your conditional is
If SkipMessage Then
.....
You'd need to change the process a bit but in this way when Application.Quit sets all variables to False your code will handle it as expected.

OM Gang

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BitsqueezerCommented:
Hi,

a simple way of using a not destroyable variable is to use the TempVars collection (available in >= A2007).

In Form_Load write:

TempVars("SkipMessage") = False

Open in new window


This will create the variable and initialize it.

In Form_Unload:

If TempVars("SkipMessage") = False Then

Open in new window


And in the Timer event:

TempVars("SkipMessage") = True
Application.Quit acQuitSaveAll

Open in new window


TempVars keeps the value of all variables in it even if your VBA runs into an error which normally destroys all common variables.

In former Access versions I would use a value in a table instead.

Cheers,

Christian
omgangIT ManagerCommented:
hmmm, couldn't edit it immediately after posting.

Should be
when Application.Quit sets all  Boolean variables to Default/False your code will handle it as
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Nick67Author Commented:
Access 2003 is still in play.
No TempVars permitted.
Sorry, my bad.
Nick67Author Commented:
Nick, a simple idea (maybe?) is to reverse the logic on the Boolean SkipMessage, i.e. change it so that your conditional is

If life gives you lemons, make lemonade
If Access gives you false, make falsenade.

That works.
Sample attached

This Q was prompted by this one
http://www.experts-exchange.com/questions/28917587/Do-MS-Access-VBA-form-TImer-events-occur-for-all-users-presently-logged-in-or-only-the-last-active-desktop-session-or-only-active-desktop-sessions.html
AutoClose.mdb
Nick67Author Commented:
It is odd that the variables get canned before the app actually quits but making 'falsenade' was a workable solution for the specific issue at hand.
Thanks to all who contributed.

Nick67
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.