5 Ways to Build Consistent Form User Interfaces in Microsoft Access

One of the best ways to make your Access application feel more professional is with a consistent user interface design.

And one of the easiest ways to get a consistent UI design is with form templates.

Here are four approaches for using form templates (I know the title says five; read to the end of the article):

• The built-in "Form template" setting in Microsoft Access
• Making copies of existing forms
• Creating form "skeletons" via VBA
• Generating entire forms via VBA

Let's explore the pros and cons of each approach.

The Built-in Form Template Setting

You can tell Access which form you would like it to use anytime you create a new form.

By default, it will look for a form named "Normal" and use that as the basis for any new form that you create.  However, you can customize the form template to something else:

• File > Options > Object Designers > Form template

While this approach is very simple, it has some drawbacks:

• You can only have a single form template
• It's a machine-wide setting, so you can't customize it by application
• It is (or at least was at one time) buggy

This was the first approach I used when I first started writing Access applications over 15 years ago.  At the time, my main development environment was Access 2000.  There was some sort of bug where forms that I created from a custom template seemed to remain linked to each other or the template form.  Honestly, I've forgotten the specifics.  Looking back, it was a blessing in disguise.  The bugginess forced me to explore other options, and I found each of them to be far superior than the built-in template support.

VERDICT: 1 out of 5 stars; would not recommend

Making Copies of Existing Forms

This is very similar to the previous approach.

1. Create one or more forms to use as starting points for other forms
2. To create a new form, select an existing form, [Ctrl] + [C] to copy, [Ctrl] + [V] to paste
3. Give the new form its own name

This approach is every bit as simple as the previous one, but it addresses all three drawbacks.  With this approach:

• You can have multiple form templates
• You can have custom templates that differ by application
• I've never run into bugs with this approach

There are still some drawbacks:

• It's not obvious what settings differ from their defaults (e.g., how do you know which settings you intentionally set versus those you hadn't given any thought to?)
• It's difficult to maintain UI consistency within an application (e.g., if you want to change the default font for all your text boxes, you need to update your Single Form template, your Continuous Form template, etc.)
• It's difficult to maintain UI consistency across applications (e.g., making a change to the form template in one app requires you to copy the updated form to every other app; updating multiple form templates increases that work)

VERDICT: 4 out of 5; strongly recommended

Creating Form Skeletons with VBA

After using the second approach for awhile, I wanted something to address its drawbacks.

1. Create a function to generate a form from scratch
2. Set common settings at the top of the function (font names, font sizes, etc.)
3. Customize other settings based on the type of form (Single, Continuous, Unbound, etc.)
4. Call the function passing arguments based on the type of form you need

Your function will be different than mine.  To give you a better idea of what I'm talking about, here's what my routine signature looks like (see here for the full function):

Sub DesignNewForm( _
    Optional ByVal IsContinuous As Boolean = False, _
    Optional ByVal IsUnbound As Boolean = False, _
    Optional ByVal IsPreview As Boolean = False, _
    Optional ByVal IsGetForm As Boolean = False, _
    Optional ByVal IsLookup As Boolean = False, _
    Optional ByVal IsUnboundAddForm As Boolean = False, _
    Optional ByVal InsertCode As Boolean = True)

This approach is less intuitive to set up than the previous approaches.  It requires you to use VBA including parts of the Access Application model you may not have had reason to use before, such as:

• CreateForm, CreateControl
• Form.DefaultControl(acCommandButton | acLabel | acTextBox)
• Form.Section(acDetail | acHeader | acFooter)
• Form.Module.InsertLines, Form.Module.CountOfLines

With this approach:

• You can have multiple form templates
• You can have easily-maintained consistency across templates and applications
• You can support many subtly different templates without ending up in maintenance hell
• You can easily see which properties you cared enough to explicitly set
• It's version control friendly 😁

There are some drawbacks, but the benefits far outweigh these costs:

• Bigger upfront investment of time to set up the function (though it's easy to start small and build from there)
• It's not always obvious which VBA property or method you need for adjustments that can easily be done in the form designer

VERDICT: 5 out of 5; Editor's Choice award

Generating Entire Forms via VBA

A few years ago I had a project that was very heavy on data entry forms.

I could have created a half-dozen or so generic data entry forms.  I could have set each form's RecordSource on the Form_Open event.  With this approach, a single form could have supported data entry for many tables.  

But I wanted something with a more finished look.  I wanted each text box, combo box, and list box to be optimally sized.  I wanted date fields to be treated differently than other text boxes.

I needed a way to generate lots of simple forms that was quick, consistent, and visually appealing.

To do this, I built a form generation class (actually multiple classes) that used a fluent interface to generate entire forms–including code-behind–from scratch at design time.  No form design view.  Just VBA.

Here's a look at a sample form and the VBA that built it, from my article on Fluent Interfaces:

This approach has some MAJOR drawbacks:

• Building the class module was a monumental undertaking
• Even after years of tweaking and debugging, there's still a persistent bug that will sometimes corrupt the entire front-end file if I try to generate the same form without performing a compact-and-repair in between
• The true benefit comes from being able to build an entire form in code; this is where the 80/20 rule bites us: building 80% of the form is pretty easy, but the last 20% can take forever
• You need a very specific kind of project to make the upfront development time worth it (it was for my project, but just barely)

The approach does have some MAJOR advantages, though, too:

• You get ultimate consistency
• A simple form can be created in about ten lines of code
• Adding or removing a control in the middle of a form does not require adjusting all the other controls in the form (just add or delete a line of code and regenerate a new form)
• The complexity is contained in the class modules; the calling code is very clean (if done right)
• Very complex functionality can be built into field-specific controls (e.g., .AddYesNoRadio, .AddDate, .AddTabWithSubform, etc.)
• Once built, the classes can be used across multiple applications

VERDICT: 3 out of 5 stars; a great project for masochists

Microsoft Access Themes

^{This one's for you, Peter Cole.}

I can't talk about building a consistent Microsoft Access user interface without mentioning Access Themes.

Themes provide a way for you to apply consistent fonts and color schemes across an entire application.  Peter Cole is the head Access Theme evangelist.  Check out his site thememydatabase.co.uk and his presentation on Access Themes to the Access Lunchtime user group:

Referenced articles

Cover image created with Microsoft Designer

UPDATE [2023-02-23]: Added link to DesignNewForm() article, "Creating Form Skeletons with VBA."