Close All Open Objects?

[SgtTurbo]

I would like to Close All Open Objects (Forms, Reports, Tables, Queries...) with a custom code. A quick internet search found an old thread from 2007 on this forum (http://bytes.com/topic/access/answer...rms-tables-etc) in which "@ADezii" provided a great solution which I have placed in a Public Function:

Option Compare Database
Option Explicit

Public Function CloseAllObjects()
On Error GoTo errHandler
'------------------------------------------------------------
'Purpose:       Closes all Access Objects (Forms, Reports, Tables Queries)
'Author:           User: ADezii
'Date:              03/10/2007
'Source:          http://bytes.com/topic/access/answers/614454-possible-use-vba-code-close-all-open-objects-forms-tables-etc
'------------------------------------------------------------
Dim aob As AccessObject

With CurrentData
    ' "Tables"
        For Each aob In .AllTables
            If aob.IsLoaded Then
                DoCmd.Close acTable, aob.Name, acSaveYes
            End If
        Next aob
    ' "Queries"
        For Each aob In .AllQueries
            If aob.IsLoaded Then
                DoCmd.Close acQuery, aob.Name, acSaveYes
            End If
        Next aob
End With

With CurrentProject
    ' "Forms"
        For Each aob In .AllForms
            If aob.IsLoaded Then
                DoCmd.Close acForm, aob.Name, acSaveYes
            End If
        Next aob
    ' "Reports"
        For Each aob In .AllReports
            If aob.IsLoaded Then
                DoCmd.Close acReport, aob.Name, acSaveYes
            End If
        Next aob
    ' "Pages"
        For Each aob In .AllDataAccessPages
            If aob.IsLoaded Then
                DoCmd.Close acDataAccessPage, aob.Name, acSaveYes
            End If
        Next aob
    ' "Macros"
        For Each aob In .AllMacros
            If aob.IsLoaded Then
                DoCmd.Close acMacro, aob.Name, acSaveYes
            End If
        Next aob
    ' "Modules"
        For Each aob In .AllModules
            If aob.IsLoaded Then
                DoCmd.Close acModule, aob.Name, acSaveYes
            End If
        Next aob
End With

errExit:
    Exit Function
errHandler:
    MsgBox "Error " & Err.Number & " " & Err.Description
    Resume errExit

End Function

I've then successfully called the code with a single line on an Event Procedure with the line:

CloseAllObjects

which then successfully closed all open objects.

I'd like to pass an exception to the function to allow a particular form to remain opened but I'm puzzled about how to accomplish this. I was thinking about changing line 4 above to:

Public Function CloseAllObjects(Optional strException As String)

in order to except the exception, but then I don't understand what else to modify in the function to test the exception and skip the closing procedure.

The purpose I'll be using this for is to log out of everything and return to the "frmLogOn" though I may find other uses for this later in life.

Thank you in advance for volunteering your expertise to others!

[zmbd]

Public Function CloseAllObjects(Optional strException As String)

Take the value of strException passed
I usually pull this into the function code so typecast a variable to match and save the value

dim strDontCloseMe as String
strDontCloseMe = nz(strException,"NoValue")

I use the NZ() function just incase nothing was passed.
Now in the loop (say the tables lines 16 thru 20), each control has a "name" property (as you can see in line 18), so you simply enclose the close function within an if...then structure with the condition checking for the control's name and if not that name then run the close code.

You will need to do that for every loop.

easy peasy lemon squeezy
-z

[SgtTurbo]

That's not quite what I was getting at.

I can assign the optional value without a problem; and being that it is optional, I don't think the null value will cause me any problems. I was struggling with how to compare the exception that was passed to what is being closed. It could be that I'm not passing the right value to the function or not comparing it to the right thing in the function (using an IF/Then logic statement of course).

I'm passing "frmLogOn" to the function, like this:

CloseAllObjects ("frmLogOn")

and then comparing it in the function like this:

If aob <> strException Then

(of course inserted into the original code posted, right now between lines 32-33 with and End If statement between lines 33-34, as it is currently a form I'm trying to exempt from closing; but realize I'd have to do the same for each type of object)

Running it this way creates an error:
"Error 438 Object doesn't support this property or method"

Not sure what I'm doing wrong, seems simple...

[SgtTurbo]

I even tried converting the object to a string, like this:

If CStr(aob) <> strException Then

but that didn't work either.

[ADezii]

I have adapted the Code so that you can allow for as many Exceptions as you like using ParamArray(). If you are working with only a single Exception it is overkill, but I will Post it nonetheless. For a single Exception, simply compare the Object Name (aob.Name) against the passed Argument in the Function (strException) for equality and do not Close if they are equal. For the sake of simplicity and brevity I have significantly shortened the Code and eliminated Error Checking, but the Logic can easily be repeated and Error Checking reinserted.

The following Code will Close all Objects except: Form1 (Form), Inventory List (Form), Order Details (Form), rptOrders (Report), and mcrTest (Macro):

1. Function Call:
   

   Call CloseAllObjects("Form1", "Inventory List", "Order List", "rptOrders", "mcrTest")

2. Function Definition:
   

   Public Function CloseAllObjects(ParamArray varExceptions())
   Dim aob As AccessObject
   Dim varX As Variant
   Dim blnObjectFound As Boolean
   
   With CurrentProject
     ' "Forms"
     For Each aob In .AllForms
       If aob.IsLoaded Then
         For Each varX In varExceptions
           If aob.Name = varX Then
             blnObjectFound = True
               Exit For
           End If
         Next
         If Not blnObjectFound Then DoCmd.Close acForm, aob.Name, acSaveYes
       End If
       blnObjectFound = False      'RESET
     Next aob
   End With
   End Function

[SgtTurbo]

Thanks! I currently only have 1 exception, so this worked fine.

If aob.Name <> strException Then

[ADezii]

Glad it worked out for you.