Evolution of a Function: ShowForm() Part 1

This is Part 1 of a series on the evolution of my ShowForm() function.

• Series Introduction

One of the principles of building generic solutions in code is Just-In-Time development.

In other words, build features only as you need them.  Step 1, then, is to start with what's known as an MVP: a minimum viable product.  The MVP solves our immediate problem, but–and this is critical–it does not attempt to predict what other problems we may need to solve in the future.

We will cross those bridges as we get to them.

DoCmd.OpenForm Wrapper

The MVP for the ShowForm() function is little more than a convenience function that wraps the DoCmd.OpenForm method.

There were a few things about the built-in OpenForm method that annoyed me:

• Unused arguments
• "Missing operator" errors
• Filtering not applied
• No error handling

My first iteration of this function addressed each of these concerns.

Unused Arguments

The DoCmd.OpenForm method has the following six optional parameters, but I only use the two in bold on a regular basis:

• View
• FilterName
• WhereCondition
• DataMode
• WindowMode
• OpenArgs

By far, the argument I use the most is the WhereCondition.  Before I wrote my ShowForm() function, my code was littered with calls to OpenForm that included commas with no values to act as placeholders for the missing optional values:

DoCmd.OpenForm "MyForm", , , "MyTableID = " & Me.tbMyTableID, , , "Key=Value"

Using my ShowForm() MVP, the above code would be rewritten as:

ShowForm "MyForm", "MyTableID = " & Me.tbMyTableID, "Key=Value"

This is ugly to read and it's asking for trouble.  It's too easy to leave out a comma or insert an extra one.  Because there are so many optional parameters, adding or removing a comma is unlikely to generate a compile error, but it will likely lead to runtime errors.

That's a bad thing because runtime errors are worse than compile errors.

Missing Operator Errors

Imagine we are calling the above code on the double-click event of the detail section of a continuous lookup form.

The user double-clicks a record and the record's MyTableID value is used to filter the form.  Let's say the value is 42.  The WhereCondition will evaluate to: "MyTableID = 42".  The form MyForm will open and show only the record with a MyTableID value of 42.

Now imagine that the form allows additions, and the user double-clicks on the new record at the bottom of the form.  Since it's a new record, the Me.tbMyTableID field will return Null as its value.  Thus, the WhereCondition will evaluate to: "MyTableID = ".  The form MyForm will fail to open and the following error will display:

Syntax error (missing operator) in query expression 'MyTableID = '.

The ShowForm() function checks to see if the right-most character of the WhereCondition is an equal sign.  If it is, then the form does not open so as to avoid triggering this error.

Filtering Not Applied

Let me start by saying that I can no longer reproduce this issue in Access 365.

However, when I first wrote this version of the function, our primary target Access version was Access 2000.  As I recall, the form did not update if you called it with a different WhereCondition.

For example, consider executing the following code in the immediate window:

DoCmd.OpenForm "MyForm", , , "MyTableID = 42"
DoCmd.OpenForm "MyForm", , , "MyTableID = 69"

The old Access behavior was to make no changes to the data of an already-opened form.  

After executing the above two lines of code, the user would see the MyForm form and it would be filtered to show only those records where MyTableID = 42.

All that said, the new Access behavior seems to have addressed this situation.  If you run the above two lines of code in Access 365, you will see the MyForm form filtered to show only those records where MyTableID = 69.

No Error Handling

Built-in functions will raise errors that you can catch, but you can't add error handling code to the built-in functions themselves.

By the time I wrote my ShowForm() MVP, I had created a global LogError function that I would call from the error-handling section of every procedure I wrote (I eventually evolved beyond adding error-handling boilerplate to my routines).  

Thus, one of the nice features of the first version of the ShowForm() function was that it had a built-in error handler.

ShowForm() Part 1: Actual Code

Here's the code for the function along with its lone dependency, the FormIsOpen function:

'https://nolongerset.com/showform-part-1/
Function ShowForm(FormName As String, _
                  Optional Where As String = "", _
                  Optional OpenArgs As Variant)
    On Error GoTo Err_ShowForm

    If Right(Where, 1) <> "=" Then
        If FormIsOpen(FormName) Then DoCmd.Close acForm, FormName
        DoCmd.OpenForm FormName, acNormal, , Where, , , OpenArgs
    End If

Exit_ShowForm:
    Exit Function
Err_ShowForm:
    MsgBox Err.Description, , "Error " & Err.Number
    'LogError Err.Number, Err.Description, "ShowForm", "FormFunctions Module"
    Resume Exit_ShowForm
End Function

'https://nolongerset.com/why-so-lazy-access/
Function FormIsOpen(FormName As String) As Boolean
    FormIsOpen = (SysCmd(acSysCmdGetObjectState, acForm, FormName) <> 0)
End Function

Cover image created with Microsoft Designer