Application.Quit / DoCmd.Quit Fails

[Keith]

Greetings:

I was looking at the task manager and just noticed that Access isn't
really closing when I exit my application. One copy of Access shows up
in the processes list for each time I open and close the application.
(It doesn't appear in the applications list.)

I've read dozens of posts about the merits of DoCmd.Quit v.
Application.Quit, closing recordsets and setting their variables =
Nothing. No matter what I do, Access remains open in the background.
Here are the significant parts of my code:

Private Sub cmdQuit_Click()
DoCmd.Close acForm, "frmMenuMain", acSaveYes
End Sub

On Error Resume Next
gstrSQL = "DELETE * FROM tblCurrUserProj" _
& " WHERE strUserID = """ & fOSUserName() & """;"
DoCmd.SetWarnings False
DoCmd.RunSQL gstrSQL
DoCmd.SetWarnings True
Dim AnObj As Object

'Close all forms
For Each AnObj In Application.Forms
DoCmd.Close acForm, AnObj.Name
Next
'Close all reports
For Each AnObj In Application.Reports
DoCmd.Close acReport, AnObj.Name
Next
'Close all modules
For Each AnObj In Application.Modules
DoCmd.Close acModule, AnObj.Name
Next

'Close any possible object variables.
Set AnObj = Nothing
Set rs = Nothing

'In case the OS needs to do something...
'Tried with and without
DoEvents

DoCmd.Quit acQuitSaveAll 'Tried prompt, too.

End Sub

Is there any way to tell programatically if I have any latent object
variables? If so, how? (Nothing shows up in the locals window.)

Is it possible to set object variables to Nothing outside their scope.
(I suspect not, but since they persist, I thought I'd ask.)

I have 3 hunks of code that I didn't write, and I'm reluctant to start
messing with them. (MouseHook changes scrolling behavior, GetWinUser
gets the currently logged in user, and QBSDK retrieves data from
QuickBooks.) I assumed they were well behaved, but maybe not.

Isn't there any way to just tell Access, "Whatever you think you're
doing, stop right now and exit?" TIA.

Keith

P.S. Did I mention the boss is expecting this at lunch time?

 

[Allen Browne]

Hi Keith. Some things to try:

What version of Access is this?

To close any open forms/reports, you need to loop *backwards* through the
collection, e.g.:
For i = 0 to Forms.Count -1
Docmd.Close acForm, Forms(i).Name
Next

Do you have any class modules? Do you need to explicitly destroy any
instances of things you instantiated?

Does it make any difference if you temporarily remove those external
libraries? (Substitute a function in a standard module that takes the same
arguments. It doesn't have to do anything.)

If that doesn't work, you're down to the divide'n'conquor approach, where
you eliminate half the things that could be causing the problem, to see if
it still occurs. Continue do add/remove half at a time, until you pin down
the culprit.

 

[Keith]

Hi Allen:

Thanks for the tips. I just finished commenting out all the external
code, one module at a time, and the problem persists. Here are some
things I'm considering:

1. If I create an object variable:

Dim ctrl as Control

and then loop through controls with:

For Each ctrl in Forms!TheForm

do I need to set ctrl = Nothing?

2. Am I wrong to assume that object variables only exist if the module
in which they exist is called? That is, if I have a recordset variable
on the Budget form, but I never load the Budget form, that recordset
variable is not created. (If that's true, then I should only have to
check global variables, and those on the initial form I load.)

3. You suggested using an index to loop through the forms. Will that
work differently than the For Each construct I posted originally?

Thanks

Keith

 

[Keith]

Hi Again:

Forgot to mention, this is Access 2003.

As I continue to test, I've noticed that closing from the initial menu
form does completely close Access. However, any menu item I choose
from one of three list boxes cause Access not to close when I return
to the main menu and click exit. Is there anything about this code
that would cause that:

Private Sub lstBrowse_AfterUpdate()
lstOverviews = Null
lstCreateNew = Null
LoadForm (Forms!frmMenuMain.lstBrowse)
End Sub

Public Sub LoadForm(MenuList As ListBox)
Dim strArg As String
Dim lngMode As Long

Select Case CLng(MenuList.Column(2))
Case 0 'Add mode
strArg = "Add"
lngMode = 0
Case 1 'Edit mode
strArg = "Edit"
lngMode = 1
Case 2 'Read only mode
strArg = "Read"
lngMode = 2
Case Else
End Select

DoCmd.OpenForm MenuList.Column(1), acNormal, , , lngMode, , strArg

End Sub

 

[Keith]

Hi Allen:

Did you mean loop backwards, as in:

For i = Forms.Count-1 to 0 Step -1

K

 

[Allen Browne]

You're right: I intended the loop to be Step -1.

For your example of:
Dim ctl As Control
you should not have to explicitly use:
Set ctl = Nothing
at the end of that procedure. Having said that, I do it anyway: guess I just
don't trust Access to release all objects.

You also asked:
Am I wrong to assume that object variables only exist if the
module in which they exist is called?
It's a bit more involved than that. Basically, if you never open a form,
then it's module is not loaded into memory, and so its objects are not
instantiated. However, there are cases where that may not be true. For
example, this could cause Form1's module to be loaded:
Call Form_Form1.MyProcedure
Also, passing an object from one procedure to another can change its
lifetime. Using the Static keyword can change its lifetime.

Now to this reply. Good: you have isolated what triggers the problem. And
you are, in fact, passing an object to LoadForm(). If LoadForm() is in a
different module, that could be the issue.

 

[Keith]

Hi Allen:

LoadForm is in the same module as the sub that calls it. What's
strange is that if in this sub:

Private Sub lstBrowse_AfterUpdate()
lstOverviews = Null
lstCreateNew = Null
LoadForm (Forms!frmMenuMain.lstBrowse) ...

I replace the LoadForm call with this:
DoCmd.OpenForm "frmBudget", acNormal

I can exit cleanly from Access, even if the menu form and the budget
form are both open. But I obviously want to select from a number of
different forms to open. So why would hard coding a specific form
eliminate the error?

I tried to elminate the call buy copying the LoadForm code directly
into the AfterUpdate event and modifying it to work specificly with
lstBrowse:

....
Dim strArg As String
Dim lngMode As Long

Select Case CLng(lstBrowse.Column(2))
Case 0 'Add mode
strArg = "Add"
lngMode = 0
Case 1 'Edit mode
strArg = "Edit"
lngMode = 1
Case 2 'Read only mode
strArg = "Read"
lngMode = 2
Case Else
End Select

DoCmd.OpenForm lstBrowse.Column(1), acNormal, , , lngMode, ,
strArg


Even so, when I exit after opening the budget form, the Access process
remains in memory. I don't get it.

Allen, thanks so much for your help with this. I really appreciate it.

Keith

 

[Allen Browne]

Okay, I'm not clear about the connection between the current form and
frmMainMenu.

But in any case, you could try:
Dim strDoc as String
strDoc = lstBrowse.Column(1)
Docmd.OpenForm strDoc, , , , lngMode, ,strArg

 

[David W. Fenton]

1. If I create an object variable:

Dim ctrl as Control

and then loop through controls with:

For Each ctrl in Forms!TheForm

do I need to set ctrl = Nothing?

Michael Kaplan always recommended it, when the For Each loop has
completed.

2. Am I wrong to assume that object variables only exist if the
module in which they exist is called? That is, if I have a
recordset variable on the Budget form, but I never load the Budget
form, that recordset variable is not created. (If that's true,
then I should only have to check global variables, and those on
the initial form I load.)

Yes, that's correct.

3. You suggested using an index to loop through the forms. Will
that work differently than the For Each construct I posted
originally?

Well, it's more work, but it doesn't run the danger of creating an
implicit reference, as in the For Each Control question above. But I
*always* use For Each constructs for any collection, and never a
counter, except in the case where I want to loop backwards or using
some interval other than 1.

 

[David W. Fenton]

For your example of:
Dim ctl As Control
you should not have to explicitly use:
Set ctl = Nothing
at the end of that procedure. Having said that, I do it anyway:
guess I just don't trust Access to release all objects.

Michael Kaplan recommended it, just in case the implicit reference
to the last control was not released.

 

[David W. Fenton]

LoadForm is in the same module as the sub that calls it. What's
strange is that if in this sub:

Private Sub lstBrowse_AfterUpdate()
lstOverviews = Null
lstCreateNew = Null
LoadForm (Forms!frmMenuMain.lstBrowse) ...

I replace the LoadForm call with this:
DoCmd.OpenForm "frmBudget", acNormal

I can exit cleanly from Access, even if the menu form and the
budget form are both open.

Is the parameter for LoadForm defined as ByRef (or not declared,
which is implicitly ByRef)? Try doing it with ByVal, or with:

LoadForm (Forms!frmMenuMain.lstBrowse.Value)

 

[Arvin Meyer [MVP]]

Keith,

You might try using a variable fed by the list box:

Dim strFormName As String
strFormName = Forms!frmMenuMain.lstBrowse
DoCmd.OpenForm strFormName

You can also use a friendly name for the form and the actual form name is a
second column where the width of that column is set to 0". The line of code
would then look like:

strFormName = Forms!frmMenuMain.lstBrowse.Column(1)

 

[Marshall Barton]

Keith wrote:
[snip]

3. You suggested using an index to loop through the forms. Will that
work differently than the For Each construct I posted originally?

When you are closing all forms (removing all items from a
collection), it is imperitive that you avoid removing the
items in a formward direction. If you use For Each or For i
= 0 To Forms.Count-1 , you will remove an item, which
rearranges the collection so the next item is no longer in
the same position as it was, resulting in every other item
being removed.

I think Allen meant the code to be:

For i = Forms.Count -1 To 0 Step -1
Docmd.Close acForm, Forms(i).Name
Next

Another way is to just remove (close) the first item in the
collection until the collection is empty:

Do Until Forms.Count = 0
Docmd.Close acForm, Forms(0).Name
Loop

 

[Keith]

Hi David:

I was getting ready to rewrite my code to hardwire all my forms in a
select case statement, something along the lines of what Allen and
Arvin suggested, but your ByVal suggestion was less work, so I thought
I'd try it first. Lo and behold ... it worked! I'm delighted,
confused, but delighted.

My boss commented today on what a supportive user community this is. I
agree. Thank you, and thanks to all who came to my aid in a time of
need.

Keith

 

[Dirk Goldgar]

In

Hi Allen:

LoadForm is in the same module as the sub that calls it. What's
strange is that if in this sub:

Private Sub lstBrowse_AfterUpdate()
lstOverviews = Null
lstCreateNew = Null
LoadForm (Forms!frmMenuMain.lstBrowse) ...

Just out of curiosity -- and I see that you have resolved the problem
already -- would the problem go away if you changed this:

LoadForm (Forms!frmMenuMain.lstBrowse)

to this:

LoadForm Forms!frmMenuMain.lstBrowse

?

 

[David W. Fenton]

I was getting ready to rewrite my code to hardwire all my forms in
a select case statement, something along the lines of what Allen
and Arvin suggested, but your ByVal suggestion was less work, so I
thought I'd try it first. Lo and behold ... it worked! I'm
delighted, confused, but delighted.

It works because you're no longer passing a reference to the object,
but the value stored in that object. It's the difference between a
pointer to something else and the string value that the pointer
points to. In your case, the pointer was surviving beyond its
appropriate lifespan, and the solution was to avoid it by passing
the parameter by value instead of by reference. You could have done
the same thing by passing the .Value property of the listbox, but
then it could cause problems if you someday changed your OpenForm
code to assume it was operating on a reference (not likely, I
agree), so it's better to change your OpenForm code to take a value
instead of a reference.