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

Nick67
Nick67 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
IT Manager
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 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
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 Manager

Commented:
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2014

Author

Commented:
Access 2003 is still in play.
No TempVars permitted.
Sorry, my bad.
Most Valuable Expert 2014

Author

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
Most Valuable Expert 2014

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial