Lightweight Forms? Just Don't

From the excellent FMS website:

Use Lightweight Forms

Consider replacing Visual Basic code in a form's module with calls to standard modules, or with hyperlink objects. Then set the form's HasModule property to False. This turns the form into a Lightweight form, making it load faster. Search Access online help for "Lightweight Forms" for more information. In Access 2007, you can use embedded macros for simple operations.

From the official Microsoft documentation:

Forms or reports that have the HasModule property set to No are considered lightweight objects. Lightweight objects are smaller and typically load and display faster than objects with associated class modules. In many cases, a form or report doesn't need to use event procedures and doesn't require a class module.

These are two of the most reputable sources for Microsoft Access advice.  And they're both wrong.  

OK, wrong might be too strong a term.  Technically speaking, their statements are factually true.  Given two otherwise identical forms, the one with its HasModule property set to False (i.e., the "lightweight" form) will load faster than the other.  But we're talking about milliseconds here.

Maybe this was a bigger deal in Access 97.  In thirteen+ years of writing Access applications in Access 2000 and later, I cannot think of a single time where my solution to a performance problem was to convert an Access form with code-behind into a lightweight form.

I can, however, think of some of the extraordinary lengths I went to as a young developer trying to follow this misguided advice.  My early programs are full of Event properties with direct function calls:

What's wrong with this approach?  Um, a lot.

No compile time checking

The functions in the screenshot above do not receive compile time checking. Let's say I add a second required parameter to my SelectDate() function.  Access won't complain.  I would not know there was a problem until a user bumped into it at runtime.  

If instead that function call was in the code-behind module, I would know there was an issue as soon as I tried to compile my code.  Access would notify me of the problem and take me right to each incorrect function call, so it would be easy to fix.

Find and replace

Good luck.  Oh, it can be done.  Philipp Stiefel's  Find and Replace add-in is one option.  You can also hand-write code to loop through all your form, report, and control properties, checking their contents and then setting new values as needed.  It's risky because there's no easy way to undo it.  It's also tedious and annoying, even if you automate the process through code.  I've been there many times.

Tougher to debug

There's no way to set a breakpoint inside of an event property on a form or report. Sure, if you're calling a custom function you can set the breakpoint on the function itself.  Of course, then the code will break any time that function is called, not just when it's called from the event you are troubleshooting.  And if you are calling the function from multiple event properties, it can be difficult to tell which is the calling event.

In a moment of frustration, I worked around this limitation by creating a dummy function for the sole purpose of providing me with that information.  That I had to do this at all should have been my first clue that I was working with a heinous antipattern.

Function Watch(Val, Optional CalledFrom As String = "")
    Debug.Print Val, CalledFrom
    Watch = Val
End Function 

Problems with version control

I use DoCmd.SaveAsText to export forms and reports for version control.  This approach has the advantage that it does capture changes made to event properties.  However, those changes appear alongside less consequential changes to the user interface.

The export format also adds backslashes to escape double quotes and inserts hard-line breaks, often in the middle of words.  These formatting issues make it harder to read event properties in exported forms and reports.

Changes to program logic are the most critical items to track in version control.  When your logic is mixed in with control positioning properties, it's easy to overlook it in all that noise.  Here's a good example of these issues from a TortoiseHg diff screen:

Tracing dependencies

The MZ-Tools add-in has this awesome feature called "Method Callers."  You put your cursor inside a procedure, click the "Method Callers" button, and you get a dialog box with every place in your program where that procedure is called.  It's fantastic.  Unless you've been calling that function from an event property.  Then you get bupkis.  :-(

No access to event arguments

While you can call any custom function from an event property using the =MyFunction() format, there is one key limitation.  You do not have access to the event arguments.  This means, for example, that you can't set Cancel = True in a form's BeforeUpdate property.  You could jump through all kinds of hoops to avoid creating a code module for your form only for Access to force your hand when you need to use an event argument.

Uses expression evaluation

Did you know that when you put code in your event properties that VBA is not executing it?  Seriously.  Technically, that stuff your putting in there is not code; it's an expression.  Expression evaluation is what happens in queries and form/report/control properties.  Code execution is what happens in the VBA editor. Usually this is just a distinction without a difference, but there can be some real differences.  I've never found a good online reference that addresses this fact, but it's easy to demonstrate with a simple example.

There are two forms of IIf() we can use in Access.  The IIf() statement is what is used in queries.  The IIf() function is used in VBA.  The function will always evaluate both the True and False parts, but the statement will only evaluate the appropriate part.  So, which one will a control event property use?  Let's find out.

Using our Watch() function from above, let's test three different environments.  First, create and run a query with the following SQL:

SELECT  IIf(True, Watch("True", "SQL"), Watch("False", "SQL"))

Next, create a blank form, add a button with the following OnClick property, show the form, and click the button:

=IIf(True,Watch("True","Form"),Watch("False","Form"))

Finally, go into the immediate window and run the function after the results from above, like so:

True          SQL
True          Form

IIf True, Watch("True", "VBA"), Watch("False", "VBA")
True          VBA
False         VBA

This has implications beyond the scope of this article.  The key takeaway for now is to understand that expression evaluation and code execution are similar, but not identical.  Treating them as identical can lead to subtle, hard to find bugs.

Sandbox security issues

When Access 2007 came along, it introduced Sandbox mode.  This was a security feature to prevent the evaluation of unsafe expressions.  Some of these "unsafe expressions" were Kill and Shell.  That makes sense.  But another "unsafe expression" is DoCmd.  

Guess who had two thumbs and a bunch of DoCmd.OpenForm() calls in the OnClick property of his command buttons?  This guy {cheesily points to self with both thumbs}. (Also, despite my use of the past tense, I still have both my thumbs.  Thanks for your concern.)

I spent more time than I wish to admit replacing all these DoCmd calls with custom function calls or [Event Procedure].  I had a handy little regular expression I used with Notepad++'s Find in Files feature to go through every .form and .report text file in my project repositories looking for offending expressions.  It was far better than combing through all my forms manually, but still not fun.

Bottom Line

Converting a form or report with a code-behind module to a lightweight form is the worst premature optimization in the Access world.  It makes your code less readable, harder to debug, and hides business logic completely out of sight.  On the bright side, though, you may shave a few milliseconds off of your form's load time.

Call me crazy, but I'd rather spend my time increasing my programs performance with techniques that actually make a difference, like a normalized database schema, appropriate table indexes, lazy loading of subforms on tabbed interfaces, cascading combo boxes, auto-filtering combo boxes, and so much more.  But I am done following the misguided advice to use lightweight forms.

In conclusion, lightweight forms make your code harder to read and debug, but if your users have monitors with high enough refresh rates, they'll appreciate the effort.

Disclaimer

If your form or report's code module looks like this...

Option Explicit
Option Compare Database

...then set that object's HasModule property to False.  There's no downside in this case.  But please, don't go to *any* effort to get to this point.

Image by Hans Braxmeier from Pixabay