The Global Form

In most of our applications, we use a hidden form named "Global" to store certain global variables.  Our newer applications have far fewer values on this form than our older applications because overuse of global state is the source of many hard-to-fix bugs.  However, there are some legitimate reasons to use global variables.  And when you're using global variables, there are a few advantages to storing them on a hidden global form.

Advantages of a Global Form

1. Values survive program state resets
2. Values can be referenced from queries
3. Form can be bound to a GlobalSettings table
4. Easy to view and edit variable values while debugging
5. Can enable closing forms in LIFO order
6. Provides an application level "OnExit" event

Disadvantages of a Global Form

1. No compile-time variable name checking
2. No compile-time type checking

The basic concept

Create a form named "Global."  The actual name doesn't matter, but it should be consistent across all your applications so that you can add functions to your code library that reference this form by its name.  

Next, create a macro named "AutoExec".  In this case, the name does matter.  AutoExec and AutoKeys are reserved macro names that get auto-magically tied to the application startup event and global key press events, respectively.  Add the following action:

AutoExec macro

• OpenForm
• Form Name: Global
• View: Form
• Window Mode: Hidden

With the AutoExec macro in place, the Global form will open as a hidden form as soon as your application opens.

Advantages

Let's discuss the advantages I mentioned earlier in a bit more detail.

Values survive program state resets

When a VBA program "resets," all of the declared variables are re-initialized (i.e., they lose any values they have been assigned to that point).  This is one of the reasons that unhandled errors cause Access to quit without warning in runtime mode; the program would be left in an unknown state otherwise.

Your program's "state" can reset one of three ways:

1. Using the VBIDE menu: Run -> Reset
2. Clicking the blue square Reset button on the VBIDE Debug or Standard toolbars
3. Clicking the [End] button on the standard error dialog box:
   

When running on your users' machines, it's better for your application to quit when the program state resets.  The alternative is that the program will behave unpredictably, which often leads to invalid data.  However, during development, it can be annoying to have your global variables reset constantly as you are troubleshooting bugs.

I should note here that the TempVars object is an alternative to managing global variables that survive program state resets.  The TempVars object suffers from the same disadvantages as the hidden form approach and lacks many of the other benefits I describe herein.

Values can be referenced from queries

It's not possible to directly reference global variables in VBA from queries in Access.  You can access them indirectly by calling a public function that returns the value of the global variable, but that requires at least four lines of code for each variable you access in that way.

On the other hand, form fields can be accessed directly in queries using the syntax Forms!Global!MyField.  

One big caveat, though, is that if you try to execute queries that reference form fields via DAO methods in code (such as CurrentDB.Execute), you will need to provide the values of these fields via the .Parameters property of the DAO object.  For this reason, I have a public function named simply G() that returns the value of the passed global form field.

'(See more robust version of this function at the end of the article)
Public Function G(CtlName As String) As Variant
    G = Forms!Global(CtlName)
End Function

Form can be bound to a GlobalSettings table

In many of our applications, we store settings that apply to all users in a table that is linked to our back-end database.  This is especially useful in applications that we have sold to multiple clients.  For example, our Tax Claim software is used in several Pennsylvania counties.  Each county location has a table with global settings that includes the name of the county, the county courthouse mailing address, etc.

In these applications, we set the Global form's recordsource to the name of this settings table.  This loads the values from the settings table into our "global variables" automatically at startup when the Global form itself is opened.  Since the Global form remains open for as long as the application is open, it serves the dual purpose of maintaining an active database connection which can improve performance by reducing the need to constantly close and open new connections.

Easy to view and edit variable values while debugging

Simply unhide the form and all your values are there to see.  If you want to change one of the values, it's as simple as overwriting the data in the text box.  

One word of warning: a control's .Value property is not updated until after the focus leaves the control.  If you do change one of your Global form values this way, make sure you tab out of the control or the application may not reference the new value.

Can enable closing forms in LIFO order

LIFO stands for Last In, First Out.  When an Access application shuts down, the forms and reports are closed in FIFO order (First In, First Out).  If your child forms assume that their parent forms are available at all times, this can lead to errors.  You can override the default behavior--potentially saving yourself some grief along the way--if you catch it before it gets started.

Since the Global form is the first form opened, it will be the first one Access tries to close.  This means you can handle its Unload event to loop through the Forms collection and close the open forms in reverse order (LIFO).  If you are unable to close a form, perhaps due to a data validation error, you can cancel the Global form's Unload event and force the user to deal with the issue.

Provides an application level "OnExit" event

In addition to micromanaging the order that forms and reports are closed, you can use the Global form's Form_Unload(Cancel As Integer) event as a generic application exit event.

Be careful, though!  As I mentioned in the previous section, there may be many other open forms and reports in the application when the global form's Unload event is first triggered.  If your application "OnExit" event code operates under the assumption that all the other forms are already closed, you will have to explicitly close them before running your event code.

Disadvantages

It's not all sunshines and rainbows.  In addition to the fact that you should generally avoid global variables, there are a few disadvantages to this approach versus the more traditional public-module-level-variables-stored-in-VBA alternative.

No compile-time variable name checking

Every module in VBA should begin with the Option Explicit directive.  This requires you to declare every variable before you use it in code.  This is a good example of defensive programming in that it helps you catch mistakes, such as typos, early in the development process.  It's always better to catch coding errors at compile time than at runtime, and Option Explicit helps with that.

Unfortunately, you lose that advantage with the Global form approach.  It is possible to gain some of that advantage back by taking advantage of the fact that each form control is available as a property of the form object itself.  First, we need to make sure that the form's HasModule property is set to Yes.

Then, instead of writing CountyID = Forms!Global!CountyID, we can write CountyID = Form_Global.CountyID.  To make this more convenient, I added a G method to my standard singleton class, clsApp.  This way I can call the CountyID field like this: CountyID = App.G.CountyID.

'--== clsApp class module (excerpt) ==--
Public Property Get G() As Form_Global
    Set G = Form_Global
End Property

No compile-time type checking

The other thing we lose is compile-time type checking.  This is what prevents us from assigning a string to a number.  For example, the following code will NOT compile:

Dim CountyID As Long
CountyID = "Wayne"

However, if CountyID is a textbox on my Global form, the following will compile without a problem:

Forms!Global!CountyID = "Wayne"

I likely won't find out there's an issue until the first time I try to filter a report by the text "Wayne" instead of the number 64.

Expanded G() function

As I promised above, here is the more robust version of my G() function:

'---------------------------------------------------------------------------------------
' Procedure : G
' Source    : https://nolongerset.com/the-global-form/
' Author    : Mike Wolfe
' Purpose   : Returns the value of a control on the Global form.  Useful for queries
'             to eliminate the need for assigning values to parameters when used in code.
' Notes     - Attempts to open Form if function fails.
'           - Requires a form named Global with HasModule set to Yes.
'---------------------------------------------------------------------------------------
'
Function G(CtlName As String) As Variant
    On Error GoTo Err_G
    G = Forms!Global(CtlName)
Exit_G:
    Exit Function
Err_G:
    Select Case Err.Number
    Case 2450 'Can't find the form 'Global'
        Dim TriedAlready As Boolean
        If TriedAlready Then
            'Replace with your own error logger:
            LogErr Err, Errors, "Module1", "G", , CtlName & ": Force Global Form open failed"
        Else
            DoCmd.OpenForm "Global", acNormal, , , , acHidden
            TriedAlready = True
            Resume
        End If
    Case Else
        'Replace with your own error logger:
        LogErr Err, Errors, "Module1", "G", , CtlName
    End Select
    Resume Exit_G
End Function

Image by FelixMittermeier from Pixabay