TryGetForm: Set a Strongly-Typed Form Object in Access VBA
The TryGetForm function helps you convert "stringly-typed" code into "strongly-typed" code when working with Access forms in VBA.
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.
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.
' ---------------------------------------------------------------- ' 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
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.