Once again, in trying to help someone in an Access forum, I came up with the following procedure(s) to be able to identify currently open, but hidden forms.

Option 1 – Print the results to the immediate window

'---------------------------------------------------------------------------------------
' Procedure : ListHiddenOpenFrms
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Generate a list of current open, but hidden forms and print their names to
'             the immediate window.
' 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 (links
'             back to this site are allowed).
'
' Usage:
' ~~~~~~
' Call ListHiddenOpenFrms
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2014-Nov-19                 Initial Release
'---------------------------------------------------------------------------------------
Function ListHiddenOpenFrms()
On Error GoTo Error_Handler
    Dim DbF     As Form
    Dim DbO     As Object
    
    Set DbO = Application.Forms 'Collection of all the open forms
    
    For Each DbF In DbO    'Loop all the forms
        If DbF.Visible = False Then
            Debug.Print DbF.Name
        End If
    Next DbF

Error_Handler_Exit:
    On Error Resume Next
    Set DbF = Nothing
    Set DbO = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: ListHiddenOpenFrms" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Option 2 – Return a delimited listing which can be used elsewhere

'---------------------------------------------------------------------------------------
' Procedure : ListHiddenOpenFrms
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Generate a list of current open, but hidden forms and return a delimited listing which 
'             can be used elsewhere.
' 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 (links
'             back to this site are allowed).
'
' Usage:
' ~~~~~~
' Call ListHiddenOpenFrms
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2014-Nov-19                 Initial Release
'---------------------------------------------------------------------------------------
Function ListHiddenOpenFrms()
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
        If DbF.Visible = False Then
            Frms = Frms & ";" & DbF.Name
        End If
    Next DbF
    
    If Len(Frms) > 0 Then
        Frms = Right(Frms, Len(Frms) - 1)   'Truncate initial ;
    End If
 
    ListHiddenOpenFrms = Frms

Error_Handler_Exit:
    On Error Resume Next
    Set DbF = Nothing
    Set DbO = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: ListHiddenOpenFrms" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Hopefully this can help someone else out.

I was recently needing to breakdown a very complex form and amongst other things, I needed to extract a list of the subforms contained within it for further analysis.  Below is a simple function I wrote which will return to the immediate pane a listing of the subform names.

Function ListSubFrms(sFrm As String)
On Error GoTo Error_Handler
    Dim ctl             As Access.Control
    Dim frm             As Access.Form

    DoCmd.OpenForm sFrm, acDesign
    Set frm = Forms(sFrm).Form
    For Each ctl In frm.Controls
        Select Case ctl.Properties("ControlType")
            Case acSubform    ', acListBox
                ' ctl.Name 'Will return the given name of the control, not necessarily the actual object name
                ' ctl.Properties("SourceObject") 'Will return the object name
                If ctl.Properties("SourceObject") = "" Then
                    Debug.Print ctl.name
                Else
                    Debug.Print ctl.Properties("SourceObject")
                End If
        End Select
    Next ctl

Error_Handler_Exit:
    On Error Resume Next
    DoCmd.Close acForm, sFrm, acSaveNo
    Set frm = Nothing
    Set ctl = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: ListSubFrms" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

I typically do not add form captions, mainly because I will include a label in the form header (and I find it redundant to repeat it in the form caption). That said, Access in its’ infinite (should I say finite) wisdom figures that since you did not specify a caption (left it blank) then it will help us out by simply displaying the form’s name.

Now, in many instances this is not a big deal, and it can actually be helpful. However, when deploying a database to an end-user, such information can help malicious users to hack your application since they formally know the names of your database objects! As such, I wanted a simple and fast way to solve this issue. The solution, enter a ‘space’ in each form’s caption, thus displaying nothing at all (just what I wanted originally). So I wrote a simple function which quickly loops through all the forms within a database and enters a ‘space’ as the caption for any forms that do not have a caption specified (I wanted to leave any caption I had specifically entered intact!).

'---------------------------------------------------------------------------------------
' Procedure : BlankFrmCaptions
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Loop through all the forms within a database and enters a 'space' as the
'             caption for any forms that do not have a caption specified but leave intact
'             any captions I had specifically entered to avoid displaying the form's
'             name by default.
' 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 (links
'             back to this site are allowed).
'
' Usage:
' ~~~~~~
' Call BlankFrmCaptions
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2014-Jan-23                 Initial Release
'---------------------------------------------------------------------------------------
Function BlankFrmCaptions()
On Error GoTo Error_Handler
    Dim obj             As AccessObject
    Dim frm             As Form
    Dim sFrm            As String

    For Each obj In CurrentProject.AllForms    'Loop through all the database forms
        sFrm = obj.Name
        DoCmd.OpenForm sFrm, acDesign   'Open the current form in design mode
        Set frm = Forms(sFrm).Form
        If Len(Trim(frm.Caption)) = 0 Then frm.Caption = " "    'Create a blank caption
        DoCmd.Close acForm, sFrm, acSaveYes    'close and save the change
    Next obj    'repeat with the next form until we have processed all of them

Error_Handler_Exit:
    On Error Resume Next
    'Cleanup our variable (better safe than sorry)
    Set frm = Nothing
    Set obj = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: BlankFrmCaptions" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

When I develop, I always, initially, build my SQL statements directly within my form’s Record Source. Now, it is a best practice to always create an independent query and base form’s off of the query rather than use an embedded SQL Statement. This is all fine a dandy when your db has 5-10 forms, but when you are working on databases with 10s or 100s of forms, this can become quite a tedious task to convert all of the SQL statements into queries and then reassign the newly created queries to their respective forms. So, I thought about it for a couple minutes and quickly realized that this could actually all be automated quite easily. As such, I developed the following procedure.

'---------------------------------------------------------------------------------------
' Procedure : ConvertSQL2QRY
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Save recordsource to queries and update recordsource to use the query
' 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 (links
'             back to this site are allowed).
' Dependencies: CreateQry()
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2011-04-30              Initial Release
' 2         2022-11-18              Fixed Error Handler Issue
'---------------------------------------------------------------------------------------
Sub ConvertSQL2QRY()
On Error GoTo Error_Handler
    Dim db               As DAO.Database
    Dim qdf              As DAO.QueryDef
'    Dim sSQL             As String
    Dim ctl              As Control
    Dim frm              As AccessObject
    Dim dbo              As AccessObject
    Dim dbp              As Object
    Dim sFrmName         As String
    Dim sFrmRS           As String
    Dim sQryRS As String

    Set db = CurrentDb
    Debug.Print "ConvertSQL2QRY Begin"
    Debug.Print "================================================================================"

    'Check Forms
    For Each frm In CurrentProject.AllForms
        sFrmName = frm.Name
        DoCmd.OpenForm sFrmName, acDesign
        sFrmRS = Forms(sFrmName).RecordSource
        Debug.Print "Processing Form: " & sFrmName
        If Len(sFrmRS) > 0 And Left(sFrmRS, 4) <> "qry_" Then
            Debug.Print "   Converting Form: " & sFrmName
            If Left(sFrmRS, 7) = "SELECT " Then
                sQryRS = sFrmRS
            Else
                sQryRS = "SELECT [" & sFrmRS & "].* FROM [" & sFrmRS & "];"
            End If
            'Create a query based on the active RS and name it based on the form name for
            '   traceability
            CreateQry "qry_" & sFrmName, sQryRS
            'Change the form RS to use the newly created query
            Forms(sFrmName).RecordSource = "qry_" & sFrmName
        End If
        DoCmd.Close acForm, frm.Name, acSaveYes
    Next frm

    Debug.Print "================================================================================"
    Debug.Print "ConvertSQL2QRY End"

Error_Handler_Exit:
    On Error Resume Next
    Set qdf = Nothing
    Set db = Nothing
    Set ctl = Nothing
    Set frm = Nothing
    Set dbp = Nothing
    Set dbo = Nothing
    Exit Sub

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

What does this procedure do? Well, it will go through all the forms in your database and create a query using the current record source and then reassign the newly created query as the form’s record source. I run this query just before deploying a database. It is also intelligent enough to only process those forms that don’t already have a query created, so it can be rerun whenever needed should I add new forms, etc…

Please note this procedure is dependent on my CreateQry procedure, so be sure to get it as well!