Use Custom Functions to Hide Multi-Level Group Headers on Editable Continuous Forms

Mike Wolfe -

This article is Part 5 of my "Hiding Duplicate Values" Mini-Series.

To follow along with the screenshots and examples, download the sample data from here: Sample Data for the "Hiding Duplicate Values" Series.

Starting Point

Unlike reports, controls on Access forms do not have a "Hide Duplicates" property.

The simplest way to hide duplicate values on a continuous form is by way of the underlying record source.  We demonstrated that approach in Part 2 and Part 3 of this series.  The issue with those two approaches is that they left us with a non-updateable query.  That's fine sometimes, but what if we want to create a continuous form with editable fields?

In Part 4, we made such an editable form using the DCount() domain aggregate function.  This is a simple approach, but it has poor performance and is limited in its flexibility.  For example, there's no easy way to hide header values for multiple group levels.

In this article, we will use custom VBA functions to create an editable continuous form that hides duplicate values for multiple levels of "group header" columns.

Basic Query as Record Source

We will reuse the "Part4" query from our previous article to serve as the form's record source.

This is a simple query that INNER JOINs three tables that form a common grandparent-parent-child relationship:

  • Customer
  • Invoice
  • InvItem
SELECT
    Customer.FirstName,
    Customer.LastName,
    Customer.CustomerID,
    Invoice.InvoiceDate,
    Invoice.DueDate,
    Invoice.InvoiceID,
    InvItem.Description,
    InvItem.Quantity,
    InvItem.UnitPrice,
    InvItem.InvItemID
FROM
    Customer
    INNER JOIN (
        Invoice
        INNER JOIN InvItem ON Invoice.InvoiceID = InvItem.InvoiceID
    ) ON Customer.CustomerID = Invoice.CustomerID
ORDER BY
    Customer.FirstName,
    Customer.LastName,
    Customer.CustomerID,
    Invoice.InvoiceDate,
    Invoice.InvoiceID DESC,
    InvItem.Description,
    InvItem.InvItemID;

Basic Continuous Form

To concentrate on the important bits, we'll just use Access's default continuous form builder to create the continuous form we will use in the rest of this example:

  • Select the "Part4" query in the navigation pane
  • Go to the Create tab in the ribbon
  • Click on More Forms > Multiple Items

I adjusted the sizes of the controls for screenshot purposes, but otherwise I kept everything else stock.

High Level Overview

For each row, we will use custom functions to check whether there are any other records that come before it in the sort order.  

We will create different functions for each group level:

  • IsTopCustomerItem()
  • IsTopInvoiceItem()

For the first and last name fields, if IsTopCustomerItem returns True, we will display the field values.  Otherwise, we will display Null.

For the invoice and due date fields, if IsTopInvoiceItem returns True, we will display the field values.  Otherwise, we will display Null.

Control Source Expressions

Here are the four custom ControlSource expressions:

  • tbFirstName.ControlSource: =IIf(IsTopCustomerItem([CustomerID],[InvItemID]), [FirstName], Null)
  • tbLastName.ControlSource: =IIf(IsTopCustomerItem([CustomerID],[InvItemID]), [LastName], Null)
  • tbInvoiceDate.ControlSource: =IIf(IsTopInvoiceItem([InvoiceID],[InvItemID]), [InvoiceDate], Null)
  • tbDueDate.ControlSource: =IIf(IsTopInvoiceItem([InvoiceID],[InvItemID]), [DueDate], Null)

Building the Custom Functions: A TDD Approach

Below are the two custom functions referenced above.

In building these functions, we'll use a concept similar to test driven development (TDD).  

In TDD, you start by creating a failing test, then the simplest version of a passing test, then properly working general purpose code.  The reason you do this with testing is to make sure that there's not a problem with the test itself.  A good test must fail with bad code and pass with good code.

The reason we are doing it here is to isolate potential problems.

If we don't test the function until it's completely implemented and there is a problem, then we don't know whether there's a problem with:

  • How the function is called
  • Where the function is defined
  • The function's signature (i.e., its parameters and return type)
  • How the function is implemented

By taking a sysematic approach, we can isolate each of these potential problems.

Where to Define the Functions

When calling a custom function from a control source expression, the function must be within scope of the calling control.  In practical terms, the function is within scope if it is a:

  • Public Function in a Standard Module
  • Private or Public Function in the form's code-behind module

Since I think of these functions as part of the user interface, it makes sense to define them inside the form's code-behind module.  

How to Define the Functions

The next thing we need to do is create the function signatures.

A procedure's signature is simply its declaration line.  It consists of the following parts:

  • Visibility (Private, Public, or Friend)
  • Type of procedure (Sub, Function, or Property)
  • Identifier name (the name of the procedure)
  • Parameter/argument list
  • Return type (for functions and get properties)

Generally speaking, you should minimize the visibility of your procedures as much as possible. In other words, if a sub or function can be Private, you should define it as such.  This helps keep the global namespace clean.  Since a form's code-behind module is a type of class module, whether we declare these functions as public or private won't actually affect the global namespace, so this is not a particularly consequential decision.  That said, we'll declare them as Private if for no other reason than to prove that Private Functions are visible from the control source of control's on the associated object (i.e., the form or report).

We'll start with two very simple definitions.  To make sure the calling code is working, we'll have one function always return True and the other always return False:

Private Function IsTopInvoiceItem(InvoiceID As Long, InvItemID As Long) As Boolean
    IsTopInvoiceItem = False
    Exit Function
End Function

Private Function IsTopCustomerItem(CustomerID As Long, InvItemID As Long) As Boolean
    IsTopCustomerItem = True
    Exit Function
End Function

So far, so good:

Hard Code a "Passing Test"

The next thing we will do is hard code a passing test.  

For our purposes, a passing test is one where the two functions return true for the highlighted records below:

So what does a "hard-coded passing test" look like exactly?  It looks exactly like this:

Private Function IsTopInvoiceItem(InvoiceID As Long, InvItemID As Long) As Boolean
    Select Case InvoiceID
    Case 1001: IsTopInvoiceItem = (InvItemID = 5002)
    Case 1002: IsTopInvoiceItem = (InvItemID = 5003)
    Case 1003: IsTopInvoiceItem = (InvItemID = 5006)
    Case 1004: IsTopInvoiceItem = (InvItemID = 5008)
    Case 1005: IsTopInvoiceItem = (InvItemID = 5011)
    End Select
End Function

Private Function IsTopCustomerItem(CustomerID As Long, InvItemID As Long) As Boolean
    Select Case CustomerID
    Case 1: IsTopCustomerItem = (InvItemID = 5002)
    Case 2: IsTopCustomerItem = (InvItemID = 5003)
    Case 3: IsTopCustomerItem = (InvItemID = 5006)
    Case 4: IsTopCustomerItem = (InvItemID = 5008)
    End Select
End Function

Here's what our form looks like now:

Yay!  We're all done!

As long as the form is only used to view the 13 records of test data we have, the functions work perfectly.  Plus they're blazing fast!  What more could you want?!?!

Oh, real functioning code, you say?  I guess we could do that.

Implementing the Functions

The final step is to write the real code so that our functions will handle all Customer and Invoice data, not just these 13 records.

Here's one such implementation:

Private Function IsTopInvoiceItem(InvoiceID As Long, _
                                  InvItemID As Long) As Boolean
    Dim SQL As String
    SQL = "SELECT InvItemID " & _
          "FROM Part4 " & _
          "WHERE InvoiceID=" & InvoiceID
          
    Dim TopInvItemID As Long
    TopInvItemID = CurrentDb.OpenRecordset(SQL, dbOpenForwardOnly)!InvItemID
    
    IsTopInvoiceItem = (InvItemID = TopInvItemID)
End Function

Private Function IsTopCustomerItem(CustomerID As Long, _
                                   InvItemID As Long) As Boolean
    Dim SQL As String
    SQL = "SELECT InvItemID " & _
          "FROM Part4 " & _
          "WHERE CustomerID=" & CustomerID
          
    Dim TopInvItemID As Long
    TopInvItemID = CurrentDb.OpenRecordset(SQL, dbOpenForwardOnly)!InvItemID
    
    IsTopCustomerItem = (InvItemID = TopInvItemID)
End Function

After confirming everything still looks good when we preview the form, let's contrast it to Part 4's form from yesterday.  As shown below, today's solution gets rid of the duplicate Customer header which would have been difficult to do with domain aggregate functions alone.

Next Steps

Careful readers may have realized that the above two functions will be called a total of 52 times for the 13 records in our sample form.

Our recordset-based functions likely perform better than the earlier DCount() solution, but opening and closing that many recordsets is still wasteful.  Now imagine doing that for a form with hundreds of records.  Connected to an Azure SQL cloud backend.  

In the next article, we'll explore a technique we can use to drastically reduce the performance impact of this approach.  Stay tuned.