The following simple little procedure can be used to check if a given form is already open.
'---------------------------------------------------------------------------------------
' Procedure : IsFrmOpen
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Determine whether a form is open or not
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFrmName : Name of the form to check if it is open or not
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' IsFrmOpen("Form1")
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2010-05-26 Initial Release
' 2 2018-02-10 Minor code simplification (per Søren M. Petersen)
' Updated Error Handling
' Updated Copyright
'---------------------------------------------------------------------------------------
Function IsFrmOpen(sFrmName As String) As Boolean
On Error GoTo Error_Handler
IsFrmOpen =Application.CurrentProject.AllForms(sFrmName).IsLoaded
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: IsFrmOpen" & 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
How To Use It
Below is a very simple routine which checks to see if form ‘frm_Contacts’ is currently open, and if it is, then we requery it. This type of code would be used if you use a pop-up form to add new records, edit data to ensure that another form reflects those changes.
If IsFrmOpen("frm_Contacts") = True Then
Forms(frm_Contacts).Form.Requery
End If
The Problem and The Solution
The issue with the above is that the .IsLoaded property cannot distinguish between a form being opened in design mode vs. normal visualization modes (Form View, Datasheet View, Layout View, …) so it can be inexact depending on the required usage. All the .IsLoaded property checks is whether the form is Loaded, not in which view mode it is running. As such, I prefer to use a function such as the one below that only return True when the object is open to visualization, not design. Furthermore, the following is more versatile as it can handle both Forms, Queries, Reports or Tables.
'---------------------------------------------------------------------------------------
' Procedure : IsObjectOpen
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Check if an object (Form or Report) is open or not
' .IsLoaded is not reliable since it can't distinguish 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
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sObjectName : Name of the Object (Form, Query, Report or Table) to check and see
' if it is open
' lObjectType : Type of Object: acForm -> Forms
' acQuery -> Queries
' acReport -> Reports
' acTable -> Tables
'
' Usage:
' ~~~~~~
' ?IsObjectOpen("Form1", acForm)
' ?IsObjectOpen("Query1", acQuery)
' ?IsObjectOpen("Report1", acReport)
' ?IsObjectOpen("Table1", acTable)
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2010-05-26 Initial Release
' 2 2018-10-06 Updated Copyright and Error Handling
'---------------------------------------------------------------------------------------
Public Function IsObjectOpen(ByVal sObjectName As String, _
lObjectType As acObjectType) As Boolean
On Error GoTo Error_Handler
'0=Closed, 1=Open, 2=Open and Dirty, 3=Open on a New Record
If SysCmd(acSysCmdGetObjectState, lObjectType, sObjectName) <> 0 Then
Select Case lObjectType
Case acForm
'0=Design View, 1=Form View, 2= Datasheet View, 7=Layout View
If Forms(sObjectName).CurrentView <> 0 Then
IsObjectOpen = True
End If
Case acQuery
If CurrentData.AllQueries(sObjectName).CurrentView <> 0 Then
IsObjectOpen = True
End If
Case acReport
'0=Design View, 5=Print Preview, 6=Report View, 7=Layout View
If Reports(sObjectName).CurrentView <> 0 Then
IsObjectOpen = True
End If
Case acTable
If CurrentData.AllTables(sObjectName).CurrentView <> 0 Then
IsObjectOpen = True
End If
End Select
End If
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: IsObjectOpen" & 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
