MS Access: Check If The Normal Form Or Subform Form Is Loaded

By | April 18, 2008

If you need to check if MS Access form is loaded or not, you can easily check it using the folloing code

If CurrentProject.AllForms("FORMNAME").IsLoaded = True Then
	' DO SOMETHING
End If

This code is good until you are using single form application. This code will fail if the form is used as subform. To search all the forms including subform, I have created a function calling which will search for all the loaded forms and gives the result as boolean value. Here is the example to check if the form is loaded or not

Dim IsLoaded as Boolean
IsLoaded = IsFormLoaded("FORMNAME")

The function IsFormLoaded will search each and every control within each form to find the form FORMNAME. Of course, this is a overhead to the application so to reduce the amount of search, two optional arguments can be provided which can be very helpful in searching subform. If you already know the parent form name of the subform and the controlname which holds subform within parent form, the time to search subform will be reduced. For example

Dim IsLoaded as Boolean
IsLoaded = IsFormLoaded("FORMNAME","PARENTFORMNAME")

will search all the controls withing PARENTFORMNAME form while

Dim IsLoaded as Boolean
IsLoaded = IsFormLoaded("FORMNAME","PARENTFORMNAME","CONTROLNAME")

will search for the CONTROLNAME control within PARENTFORMNAME form to check if the FORMNAME form is loaded.

The above mentioned functions are here.

Public Function IsFormLoaded(strFormName As String,
		Optional LookupFormName As Form,
		Optional LookupControl As Control) As Boolean
    Dim frm As Form
    Dim bFound As Boolean

    If Not (IsMissing(LookupFormName) Or IsNull(LookupFormName)
		Or LookupFormName Is Nothing) Then
        If Not (IsMissing(LookupControl) Or IsNull(LookupControl)
 		Or LookupControl Is Nothing) Then
        On Error GoTo ErrorHandler:
            If LookupControl.ControlType = acSubform Then
                If LookupControl.Form.Name = strFormName Then
                    bFound = True
                End If
            End If
        Else
            Call SearchInForm(LookupFormName, strFormName, bFound)
        End If
    Else
        For Each frm In Forms
            If frm.Name = strFormName Then
                bFound = True
                Exit For
            Else
                Call SearchInForm(frm, strFormName, bFound)
                If bFound Then
                    Exit For
                End If
            End If
        Next
    End If
ErrorHandler:
    IsFormLoaded = bFound
End Function

Public Function SearchInForm(frm As Form, strDoc As String,
 			bFound As Boolean)
On Error GoTo ErrorHandler
    Dim ctl As Control

    For Each ctl In frm.Controls
        If ctl.ControlType = acSubform Then
            If ctl.Form.Name = strDoc Then
            'If ctl.SourceObject = strDoc Then
                bFound = True
            Else
                Call SearchInForm(ctl.Form, strDoc, bFound)
            End If
SkipElement:
            If bFound Then
                Exit For
            End If
        End If
    Next
    Exit FunctionErrorHandler:
            Resume SkipElement
End Function

5 thoughts on “MS Access: Check If The Normal Form Or Subform Form Is Loaded

  1. shrekkk

    Very nice post. It helps me a lot. Sometime forms takes lot of time in loading and not able load. It may cause of database corruption, to get rid from this problem use <a href=”htp://www.repair-access-file.com”> access repair</a> utility.

    Thanks

  2. Dan Joosten

    This is a useful function. However, I don’t understand how to pass in the optional parameters since the LookupFormName and LookupControl are access objects rather than string values. The function fails if you pass in the optional arguments as strings as in your examples.

    How would I use the optional options correctly?

  3. Vishal Post author

    @Dan Joosten
    LookupFormName and LookupControl are access objects. If you have those objects available, you would pass it. Otherwise don’t pass anything and let the function handle it. For example, you want to search for a form called “frmMemberInfoSection1” but as a developer you know that you are always loading any sub form into “frmMemberInfoMain” form so you would pass the object of “frmMemberInfoMain” as a 2nd parameter. If you don’t have that object, don’t pass it. Alternatively you may modify the code to accept string value and make necessary changes.

  4. Ross

    I too was unsure about how to use the LookupFormName parameter. For anybody else who is scratching their heads: if the parent form is called frmMemberInfoMain then the form object is constructed by using Forms!frmMemberInfoMain.

    So, to continue with the illustration from reply #3, the function call would look like
    IsFormLoaded(“frmMemberInfoSection1”, Forms!frmMemberInfoMain)

  5. Nick

    Can something similar be done from within a query? I’ve attempted something where the SQL would be similar to:
    update table set table.field = iif(CurrentProject.AllForms.Formname.IsLoaded, Forms.FormName.Field, 0)
    where something=something.
    The concept being if “Formname” isn’t open and this query is called the value defaults to zero. I know it would be much easier to do in VBA but I’m working with legacy code with many steps written by another developer many years ago and I’m trying to avoid making any more changes than I have to because I’m not sure if it will affect other processes.

Leave a Reply

Your email address will not be published. Required fields are marked *