A couple of years ago I was working on a project that required me to generate dozens of simple input forms.  Rather than create each form by hand, I wanted to be able to generate them in code.

This approach has several advantages:

  • Design consistency
  • Minimal time needed to design each form
  • Ability to tweak the global design and regenerate all forms quickly
  • Version-control friendly

There are also many disadvantages:

  • Setting every control property in code gets verbose quickly
  • The form generation code is quite complex
  • Building (and rebuilding) forms this way often causes front-end corruption
  • The form must be built exclusively in code or we lose most of the advantages

I don't actually want to write about the form generation code, though.  Rather, I want to talk about a technique I used to make the code much more readable.

Declarative programming

Most VBA developers are very familiar with procedural programming.  In procedural code, you are telling the computer what to do, step by step.  The computer carries out whatever instructions you give it.

Declarative programming differs in that your code tells the computer what outcome you want, and the language implementation figures out how to provide you with that outcome by coming up with its own internal plan.  Access developers should be intimately familiar with declarative programming, too, as Structured Query Language (SQL) is a good example of a declarative language.

We don't really care whether the database engine uses a bubble sort or a quick sort or some other kind of sort to return a set of records in order, so long as those records are, in fact, returned in the requested order.  


One of the major advantages of declarative languages is that they tend to be very expressive.  Because you are not telling the computer every single step to get the outcome you desire, your code tends to have a very high signal to noise ratio.  

Consider a simple SELECT statement:

SELECT AcctID, AcctNumber, OpenDate
FROM BankAcct
WHERE CloseDate Is Null
ORDER BY AcctNumber

There is a tremendous amount of information packed into the 13 words above.  This simple SELECT statement conveys all of this info:

  • We are returning data from the BankAcct table
  • We only want records that are active (i.e., have no CloseDate)
  • We want those records returned in AcctNumber order
  • We only need the data from the AcctID, AcctNumber, and OpenDate fields

Every word expresses meaning.  Every word conveys intent.  That's what I mean by expressive.  

Dealing with verbosity

I wanted the code I wrote to generate my forms to be as expressive as possible.  I also wanted the code to be as compact as possible.  Ideally, I wanted all the code necessary to generate a form to fit on-screen in my code editor without having to scroll.

I had written form generation code in the past, but it took up a lot of vertical real estate within my code editor.  For example, here's some code I wrote to add and format a "Preview Report" button:

With CreateControl(CtlName, acCommandButton)
    .Left = 0.125 * TwipsPerInch
    .Top = 2 * TwipsPerInch
    .Width = 1.0625 * TwipsPerInch
    .Height = 0.5625 * TwipsPerInch
    .Caption = "&Preview Report"
    .OnClick = "[Event Procedure]"
    .Name = "PreviewRptBtn"
End With

If every control I added to the form required 9 lines of code, I would never be able to get everything to fit on-screen without scrolling.  I needed a solution.

Fluent interfaces

A "fluent interface" is one which uses method-chaining to call multiple object methods on a single line.  This can be used to create a very compact syntax.

For a class module that supports a fluent interface, most methods are functions whose return value is the current object instance itself.  This sounds more confusing than it is.  Let's explore the concept with an example.

Say we want to add a label to a form.  We want this label to be two inches wide, bold, italicized, with a 14-point Segoe UI font.  Here's how we would do this using the above technique:

With CreateControl(CtlName, acLabel)
    .Caption = "My Label"
    .Width = 2 * TwipsPerInch
    .TextAlign = 2 'Center
    .FontSize = 14
    .FontName = "Segoe UI"
    .FontBold = True
    .FontItalic = True
End With

Here's how we might do the same thing using a class that implements a fluent interface:

fb.AddLabel("My Label").Width(2).Center.Size(14).Font("Segoe UI").Bold.Italic

For those scoring at home, that is 9 lines of code versus 1.  Same signal, a lot less noise.

Much like the SQL example above, our single line of code is simply declaring all the properties we want for the Label we just added.  The class module--much like the database engine earlier--takes care of all the implementation.


We need to use class modules to implement a fluent interface.  Constructing a fluent method within a class module is quite simple.  Use this template as a guide:

Public Function {MethodName}() As {ClassName}
    '... code to implement the method's functionality ...
    Set {MethodName} = Me
End Function

Notice the three requirements of a fluent method:

  1. It must be a Public Function
  2. The return type is the class itself
  3. The value returned is the current instance of the class (Me)

Let's see what this looks like in practice.  I'll implement a few of the functions from above.  Note that these must all be implemented within the same class module, oFormBuilderCtl:

Public Function Bold() As oFormBuilderCtl
    this.Control.FontBold = True
    Set Bold = Me
End Function

Public Function Italic() As oFormBuilderCtl
    this.Control.FontItalic = True
    Set Italic = Me
End Function

Public Function Size(FontSizeValue As Integer) As oFormBuilderCtl
    this.Control.FontSize = FontSizeValue
    Set Size = Me
End Function

Public Function Center() As oFormBuilderCtl
    this.Control.TextAlign = ta_Center
    Set Center = Me
End Function

Public Function Font(FontName As String) As oFormBuilderCtl
    this.Control.FontName = FontName
    Set Font = Me
End Function

Practical example

So, just how powerful can this technique be when fully implemented?  As it turns out, it is quite powerful indeed.

Shown below are two screenshots.  The first is a form that I created for the project I referenced earlier.  As I said, I created dozens of forms just like the one below.  The second screenshot is the code I used to build the form.  There are obviously pieces of code not shown, but most of the code used to build the form is shown on the second screenshot.

The purple, green, orange, and red rectangles were added to the screenshots to make it easier for you, dear reader, to match up the blocks of code with their impact on the form.  Would you believe this form was created in fewer than 50 lines of code?

Final thoughts

There's no doubt that fluent interfaces are one of the shiniest of hammers to keep in your toolbox.  But they are not for every situation.  In fact, they are ill-suited for most situations.  However, in the right situation, they can be pretty badass.

Image by Arek Socha from Pixabay