MS Access VBA – List Currently Open Forms

You can use the following function to retrieve a listing of all the currently open forms.

'---------------------------------------------------------------------------------------
' Procedure : ListOpenFrms
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Returns a list of all the loaded forms (preview or design)
'             separated by ; (ie: Form1;Form2;Form3)
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites.
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2009-Oct-31                 Initial Release
'---------------------------------------------------------------------------------------
Function ListOpenFrms()
On Error GoTo Error_Handler

    Dim DbF     As Form
    Dim DbO     As Object
    Dim Frms   As Variant
   
    Set DbO = Application.Forms 'Collection of all the open forms
    
    For Each DbF In DbO    'Loop all the forms
            Frms = Frms & ";" & DbF.Name
    Next DbF

    If Len(Frms) > 0 Then
        Frms = Right(Frms, Len(Frms) - 1)   'Truncate initial ;
    End If
   
    ListOpenFrms = Frms

Exit Function

Error_Handler:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: ListOpenFrms" & vbCrLf & "Error Description: " & _
    Err.Description, vbCritical, "An Error has Occurred!"
    Exit Function
End Function

2 responses on “MS Access VBA – List Currently Open Forms”

  1. Alex Mielus

    Here is an improved version, which will also count the open forms:

    Function ListOpenForms(CountForms As Boolean)
On Error GoTo Error_Handler
 
Dim DbF As Form
Dim DbO As Object
Dim Frms As Variant
Dim FormsCount As Long
    
    FormsCount = 0
    Set DbO = Application.Forms 'Collection of all the open forms
 
    For Each DbF In DbO    'Loop all the forms
            Frms = Frms & ";" & DbF.Name
            FormsCount = FormsCount + 1
    Next DbF
 
    If Len(Frms) > 0 Then
        Frms = Right(Frms, Len(Frms) - 1)   'Truncate initial ;
    End If
    
    If CountForms = True Then
        ListOpenForms = FormsCount
    Else
        ListOpenForms = Frms
    End If
 
Exit Function

Error_Handler:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: ListOpenFrms" & vbCrLf & "Error Description: " & _
    Err.Description, vbCritical, "An Error has Occured!"
    Exit Function
End Function
    1. Daniel Pineault Post author

      No need to iterate through the collection if all you are after is the count, simply use

      DbO.Count
      Function ListOpenForms(CountForms As Boolean)
    On Error GoTo Error_Handler
    Dim DbF                   As Form
    Dim DbO                   As Object
    Dim Frms                  As Variant

    Set DbO = Application.Forms    'Collection of all the open forms
    
    If CountForms = True Then
        ListOpenForms = DbO.Count
    Else
        For Each DbF In DbO    'Loop all the forms
            Frms = Frms & ";" & DbF.Name
        Next DbF
        If Len(Frms) > 0 Then
            Frms = Right(Frms, Len(Frms) - 1)    'Truncate initial ;
        End If
        ListOpenForms = Frms
    End If

Error_Handler_Exit:
    On Error Resume Next
    If Not DbO Is Nothing Then Set DbO = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: ListOpenForms" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

      The onlky thing being I don’t particularily like making function that do 2 different things normally and not define the returned value type like this. I’d much prefer 2 separate functions.