TryGetForm: Set a Strongly-Typed Form Object in Access VBA

Using Forms!MyForm!MyControl is an insidious form of "stringly-typed" programming.

The VBA Bang (!) Operator: Hidden Stringiness

Ben Clothier explained the situation in a comment in my article about turning runtime errors into compile errors:

I would argue that the average VBA code is far more stringly than might be apparent.

People will probably look at expression like:

Forms!FormA!txtThing = Forms!FormB!txtOtherThing

and think it's perfectly fine. But that is a complete lie! There is a great deal of information out there that's simply wrong about the bang operator. A common description that's wrong is to claim that it's for indexing into a collection. Nope.

All the bang operator does is translate the code into:

Forms.Item("FormA").Controls("txtThing").Value = Forms.Item("FormB").Controls("txtOtherThing").Value

Thus, holy strings, batman!

Ben's Recommended Workaround

Ben went on to suggest a way to improve the situation: use the form's class module to convert the stringly-typed code to strongly-typed code.

Ben continues:

If we wanted to convert those into a compile-time error whenever the forms or its controls are deleted or renamed or changed in other ways, then we need to write code like the following:

Dim frmA As Form_FormA
Dim frmB As Form_FormB

If TryGetForm("frmA", frmA) Then
  If TryGetForm("frmB", frmB) Then
    frmA.txtThing.Value = frmB.txtOtherThing.Value
  Else
    Debug.Print "frmB isn't loaded"
  End If
Else
  Debug.Print "frmA isn't loaded"
End If

My Implementation of Ben's TryGetForm() Function

Ben didn't include an implementation of the TryGetForm function, but it is easy enough to infer based on the context.  In fact, I've written in the past about TryXxx functions:

.VB.Net has several standard library methods named TryXxx.  These functions try to perform some operation, then return a boolean to indicate whether the operation succeeded or failed.
...
I'm going to start adopting TryXxx as my own personal naming convention for functions and methods that share this purpose.

In other words, we want a function that will:

• return a Boolean to indicate success or failure (i.e., the function itself should never fail)
• set the form object to the open form (i.e., the form argument is treated as an "Out" parameter)

Additionally, I wanted to provide some feedback to the developer to help differentiate between typos (where the form name is misspelled) and situations where the form name is spelled correctly, but it simply is not open.

The Code

' ----------------------------------------------------------------
' Purpose   : Attempts to set a form object variable to an open form.
'               Returns True if successful, False otherwise.
' Author    : Mike Wolfe (inspired by Ben Clothier)
' Date      : 4/20/2023
' Source    : https://nolongerset.com/trygetform/
' ----------------------------------------------------------------
Public Function TryGetForm(FormName As String, _
                           Out_FormObject As Access.Form) As Boolean
    On Error Resume Next
    Set Out_FormObject = Forms(FormName)

    Select Case Err.Number
    Case 0  'Success
        Const DesignView As Integer = 0
        If Out_FormObject.CurrentView = DesignView Then
            'This won't be an issue for end users as they SHOULD NOT have
            '   the ability to edit forms, but checking for
            '   design view will save us as developers from annoying
            '   "Type Mismatch" errors in our calling code
            TryGetForm = False
            Debug.Print "TryGetForm = False: " & FormName & " open in Design View"
        Else
            TryGetForm = True
        End If
    Case 2450
        'Microsoft Access cannot find the referenced form '|'.
        'The form you referenced may be closed or may not exist in this database.
        
        'Let's determine whether it's closed OR doesn't exist
        Err.Clear
        Dim Dummy As String
        Dummy = CurrentProject.AllForms(FormName).Name
        If Err.Number = 0 Then
            Debug.Print "TryGetForm = False: " & FormName & " is not open"
        Else
            Debug.Print "TryGetForm = False: " & FormName & _
                        " does not appear to exist; check spelling for typos"
        End If
        
    Case Else
        Debug.Print "TryGetForm = False: [" & Err.Number & "] " & Err.Description
    End Select
    
    Err.Clear
    
End Function

Testing and Usage

To test the function, let's create three forms in an empty database (names are in bold):

• Form1_Open: a form with Has Module = Yes that will be open during testing
• Form2_Closed: a form with Has Module = Yes that will be closed during testing
• Form3_OpenNoModule: a form with Has Module = No that will be open during testing
• Form4_OpenDesignView: a form with Has Module = Yes that will be open in Design View during testing

Additionally, our test routine will attempt to open a form named "Form5_DoesNotExist" that does not exist so that you can see how that situation is handled.

Public Sub TestTryGetForm()
    On Error Resume Next

    DoCmd.OpenForm "Form1_Open"
    DoCmd.Close acForm, "Form2_Closed"
    DoCmd.OpenForm "Form3_OpenNoModule"
    DoCmd.OpenForm "Form4_OpenDesignView", acDesign

    'This form has a code module and is open
    Dim Form1 As Form_Form1_Open
    If TryGetForm("Form1_Open", Form1) Then
        Debug.Print "Success: "; Form1.Name
    End If

    'This form has a code module but is closed
    Dim Form2 As Form_Form2_Closed
    If TryGetForm("Form2_Closed", Form2) Then
        Debug.Print "Success: "; Form2.Name
    End If

    'There is no code module for this form
    Dim Form3 As Access.Form
    If TryGetForm("Form3_OpenNoModule", Form3) Then
        Debug.Print "Success: "; Form3.Name
    End If

    'This form is open, but it is in Design View
    Dim Form4 As Form_Form4_OpenDesignView
    If TryGetForm("Form4_OpenDesignView", Form4) Then
        Debug.Print "Success: "; Form4.Name
    End If
    
    'This form does not exist
    Dim Form5 As Access.Form
    If TryGetForm("Form5_DoesNotExist", Form5) Then
        Debug.Print "Success: "; Form5.Name
    End If

End Sub

Epilogue

I'll admit, the TryGetForm() function ended up a lot longer than I originally anticipated it would be.

As I wrote the TestTryGetForm() function, I kept running into situations that I wanted to handle better, such as:

• detecting whether a form was open but in design view
• determining whether AccessError 2450 was due to the form not being open or actually missing from the database

This goes to show that there is a lot of value in writing stand-alone single-purpose routines as that approach allows you to handle edge cases and corner cases in one place within your code base as they pop up.