Form Design

5 Ways to Build Consistent Form User Interfaces in Microsoft Access

Make your Access application feel more professional with a consistent user interface design. Form templates make it easy.

Mike Wolfe

Feb 20, 2023 • 6 min read

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

The above form template is a form named "ConsolasTextBoxes", which presumably is a form where the default font for text boxes is the monospace font, Consolas. [Monospace fonts are ideal for user input](https://nolongerset.com/choosing-the-right-font/#monospace).

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.

Create one or more forms to use as starting points for other forms
To create a new form, select an existing form, [Ctrl] + [C] to copy, [Ctrl] + [V] to paste
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.

Create a function to generate a form from scratch
Set common settings at the top of the function (font names, font sizes, etc.)
Customize other settings based on the type of form (Single, Continuous, Unbound, etc.)
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: