MS Access Query : Check if form is open / Check if

Question

I'm currently trying, in a MS Access Query (using the GUI tool, and not the SQL tool), to check if a specific form is open or not, without using VBA.

Using the following expression :

Expr1 : [Formulaires]![Form1].[Visible]

If Form1 is open, the query works fine, but if Form1 is closed, it asks for input, since the Form1 isn't open, and the variable doesn't exist anymore.

Is there a way to check if a specific form is open, or to check if a certain variable requires input ?

Answer 1

You would need a VBA function to discover this but you could call the function from your query and incorporate the result in your formula.

This function will return true if the form is loaded;

Function IsFormLoaded(strForm As String) As Boolean

Dim frm As Form

For Each frm In Forms
    If frm.Name = strForm Then
        IsFormLoaded = True
        Exit Function
    End If
Next

End Function

You can then use the function in an IIF statement;

IIF(IsFormLoaded('MyForm'), MyForm.Control.Value, '')

Answer 2

Yes, use a function:

Public Function IsFormLoaded(ByVal strForm As String) As Boolean

    Dim frmForm     As Form
    Dim booLoaded   As Boolean

    For Each frmForm In Forms
        If frmForm.Name = strForm Then
            booLoaded = True
            Exit For
        End If
    Next

    IsFormLoaded = booLoaded

    Set frmForm = Nothing

End Function