Similar to my previous couple of posts:
Access - VBA - Close All Open Tables
Have you ever needed to close any and all open tables? Ensure that no tables are still open? It is remarkably easy to do!
Access – VBA – Close All Open Queries
Continuing down the same path as my previous post: I thought I’d share a simple function to close any and all open queries. The concept is the same in that we loop through all the queries (CurrentData.AllQueries), check to see if each one is loaded (IsLoaded), and if so, close them (DoCmd.Close).
I thought I’d present a function to close all the open reports within a database.
The code is slightly simpler that the 2 previous posts as we can use the Reports property which returns a list of only the open reports. Thus we don’t need to validate whether the report is loaded or not.
The Code
'---------------------------------------------------------------------------------------
' Procedure : CloseAllOpenReports
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Close all the currently open Reports in the database
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Usage:
' ~~~~~~
' ? CloseAllOpenReports
' Returns -> True => Closed all Reports successfully
' False => A problem occurred
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2015-02-17 Initial Release
' 2 2023-02-09 Update variable naming, Error handler, copyright
' 3 2023-03-13 Changed approach as For each rpt in Reports skips
' objects
'---------------------------------------------------------------------------------------
Function CloseAllOpenReports() As Boolean
On Error GoTo Error_Handler
Dim i As Long
For i = Application.Reports.Count - 1 To 0 Step -1
DoCmd.Close acReport, Reports(i).Name, acSaveNo
Next i
CloseAllOpenReports = True
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Source: CloseAllOpenReports" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
Usage Example(s)
Then to use the function we simply do:
Call CloseAllOpenReports
Or
If CloseAllOpenReports = True Then
'...
End If
