505.369.1650 [email protected]

Pass an object type to this VBA function, and it will automatically close all of the open objects of that type.  It loops through an object collection, like AllForms, and examines the IsLoaded property of each AccessObject in the collection.

For example, if you are closing a report criteria form, and you want to make sure that all of the open reports have been closed as well, you would call the function like this:

CloseOpenObjects acReport
Public Function CloseOpenObjects(ObjectType As AcObjectType) As Boolean
' This function closes any loaded objects of the selected ObjectType.
' AcObjectType (partial list)
' acForm
' acMacro
' acQuery
' acReport
' acTable
'
' CloseOpenObjects() Version 1.0.0
' Copyright © 2023 Extra Mile Data, www.extramiledata.com.
' For questions or issues, please contact [email protected].
' Use (at your own risk) and modify freely as long as proper credit is given.
'
On Error GoTo Err_Handler
    
    Dim aob As AccessObject
    
    Select Case ObjectType
    Case acForm
        For Each aob In CurrentProject.AllForms
            If aob.IsLoaded Then
                DoCmd.Close ObjectType, aob.Name, acSaveNo
            End If
        Next aob
    Case acMacro
        For Each aob In CurrentProject.AllMacros
            If aob.IsLoaded Then
                DoCmd.Close ObjectType, aob.Name, acSaveNo
            End If
        Next aob
    Case acQuery
        For Each aob In CurrentData.AllQueries
            If aob.IsLoaded Then
                DoCmd.Close ObjectType, aob.Name, acSaveNo
            End If
        Next aob
    Case acReport
        For Each aob In CurrentProject.AllReports
            If aob.IsLoaded Then
                DoCmd.Close ObjectType, aob.Name, acSaveNo
            End If
        Next aob
    Case acTable
        For Each aob In CurrentData.AllTables
            If aob.IsLoaded Then
                DoCmd.Close ObjectType, aob.Name, acSaveNo
            End If
        Next aob
    End Select

    CloseOpenObjects = True
    
Exit_Proc:
    On Error Resume Next
    Exit Function

Err_Handler:
    MsgBox Err.Number & " " & Err.Description, vbCritical, "CloseOpenObjects()"
    CloseOpenObjects = False
    Resume Exit_Proc
End Function