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
Carl, you have been a blessing more than once ‘in fact I believe it’s been somewhere in double digit years’ since I first needed assistance and I must say you have been my right arm since the beginning. I also agree with Jim Lipko (Truly an Extra Mile). Thanks for all the support given over the years..
Don, it’s been my pleasure.