MS Access – VBA – Close All Open Forms

In some cases it may be useful to close all the forms within a database.

Why you might ask, one example would be to ensure you close all connection to the back-end prior to working with it.

'---------------------------------------------------------------------------------------
' Procedure : CloseAllOpenForms
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Close all the currently open Forms 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:
' ~~~~~~
' ? CloseAllOpenForms
'   Returns -> True     => Closed all Forms 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 frm in Forms skips
'                                       objects
'---------------------------------------------------------------------------------------
Function CloseAllOpenForms() As Boolean
    On Error GoTo Error_Handler
    Dim i As Long

    For i = Application.Forms.Count - 1 To 0 Step -1
        DoCmd.Close acForm, Forms(i).Name, acSaveNo
    Next i
    
    CloseAllOpenForms = True

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: CloseAllOpenForms" & 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

Then you can simply call it by doing

Call CloseAllOpenForms

OR

If CloseAllOpenForms Then
    'All Forms were successfully closed
    'Your code goes here
End if

4 responses on “MS Access – VBA – Close All Open Forms”

  1. Dmitrii Sonnykh

    Tried this subroutine. Opened 5 forms in a row. Started the function. The first, third and fifth forms were closed. Two remained unopened. Launched a second time. One form closed. The first of the remaining (the second of the original). Those. Forms are closed through one. We need to fix the function.

    It turns out that? A list of open forms is taken and the enumeration begins. The first form is removed. Request for the name of the second form. But the second form jumps to the place of the first in the collection and the name of the third form is displayed. She is removed. The name of the following form is requested. But the fourth form jumps to the place of the second form in the collection and the name of the fifth one is displayed. Well, and so on.

    1. Daniel Pineault Post author

      Not sure how I never caught that, but you are entirely right. I’ve updated the function and it should work properly now. I have to verify my other functions to make sure the close queries, reports, … all work properly. Thank you for letting me know.

  2. Dmitrii Sonnykh

    ‘—————————————————————————————
    ‘ Procedure : CloseAllOpenForms
    ‘ Author : Daniel Pineault, CARDA Consultants Inc.
    ‘ Website : http://www.cardaconsultants.com
    ‘ Purpose : Close all the currently open Forms in the database
    ‘ Any form that is actively loaded, this includes those in design view.
    ‘ 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:
    ‘ ~~~~~~
    ‘ Call CloseAllOpenForms
    ‘
    ‘ 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-02-13 Update
    ‘—————————————————————————————
    Function CloseAllOpenForms() As Boolean
    On Error GoTo Error_Handler
    Dim oFrms As Object
    Dim oFrm As Access.Form
    Dim strFormName() As String
    Dim intFormCount As Integer
    Dim i As Integer

    Set oFrms = Application.Forms ‘Collection of all the open forms
    intFormCount = oFrms.Count
    ReDim strFormName(intFormCount)

    For Each oFrm In oFrms ‘Loop all the forms
    i = i + 1
    strFormName(i) = oFrm.Name
    Next oFrm

    For i = 1 To intFormCount
    DoCmd.Close acForm, strFormName(i), acSaveNo
    Next

    CloseAllOpenForms = True

    Error_Handler_Exit:
    On Error Resume Next
    Set oFrm = Nothing
    Set oFrms = Nothing
    Exit Function

    Error_Handler:
    MsgBox “The following error has occurred” & vbCrLf & vbCrLf & _
    “Error Source: CloseAllOpenForms” & 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