Hiding Dupe Values

Use DCount() to Make Editable Continuous Forms with Hidden Duplicate Values

In this installment, we use the DCount() domain aggregate function to create an editable continuous form with hidden group header values.

Mike Wolfe

Feb 21, 2024 • 5 min read

This article is Part 4 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 this article, we will look at the simplest way to create an editable continuous form that hides duplicate values for "group header" columns.

Basic Query

As in parts 2 and 3, we'll start with a basic query.

We will name it "Part4" which is a terrible name for an Access object. However, for the purposes of this article, I wanted a very short name. And for the purposes of this series, I wanted a name distinct from the other articles. "Part4" fits both criteria.

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;

Unlike parts 2 and 3, this basic query is what will serve as the record source for our form. Because it consists of simple INNER JOINs on foreign key relationships, it is updateable (as evidenced by the new record row shown above).

In parts 2 and 3, we hid the data via the form's record source. In this part, though, we will use expressions in the Control Source of our group header controls to show or hide their values.

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 the DCount() domain aggregate function to check whether there are any other records for a given Invoice that have an invoice item Description that comes before it in the sort order. If there are no such other invoice items, then we know we are in the top row of the invoice and will display the group header values. If not, we will hide them.

Domain Aggregate Functions

One of the simplest ways to turn a non-updateable query into an updateable one is to use domain aggregate functions (like DCount(), DSum(), etc.) to avoid the need for bringing in grouping subqueries.

PRO

Simple to use
Built in to Access

CON

Generally bad performance
Can only be used for relatively simple tasks

In this installment of the series, we'll see both the simplicity and shortcomings of the domain aggregate functions approach.

Renaming Text Boxes

By default, Microsoft Access creates controls with the same names as their control sources. This can cause problems when referencing underlying fields in control source expressions, because the Jet/ACE engine gets confused as to whether you are referring to the form control or the underlying field.

The simplest way to avoid this confusion–and the errors that go along with it–is to rename the controls. Below are the old and new control names on my form:

FirstName => tbFirstName
LastName => tbLastName
InvoiceDate => tbInvoiceDate
DueDate => tbDueDate

Level 1: InvoiceDate and DueDate

The first level of header fields is straightforward, because we are only dealing with two tables: In

Here are the Control Source values for InvoiceDate and DueDate, respectively:

=IIf(DCount("*","Part4","InvoiceID=" & [InvoiceID] & " AND Description<" & Qt([Description]))=0,[InvoiceDate],Null)
=IIf(DCount("*","Part4","InvoiceID=" & [InvoiceID] & " AND Description<" & Qt([Description]))=0,[DueDate],Null)

Level 2: FirstName and LastName

We'll start by applying the same concept as we did for InvoiceDate and DueDate to the FirstName and LastName controls:

=IIf(DCount("*","Part4","InvoiceID=" & [InvoiceID] & " AND Description<" & Qt([Description]))=0,[FirstName],Null)
=IIf(DCount("*","Part4","InvoiceID=" & [InvoiceID] & " AND Description<" & Qt([Description]))=0,[LastName],Null)

This gets us close to what we want, but not quite all the way there. We still need to get rid of that second instance of "Cosmo Kramer", highlighted in the screenshot below:

Unfortunately, there is no straightforward way to eliminate that second instance of "Cosmo Kramer" using the standard domain aggregate functions (DCount, DLookup, etc.).

We'll explore alternatives for addressing this problem in the next installment of this series.

Set AllowAdditions = False

If you notice in the above screenshot, there are "#Error" items in the new record row for the four calculated columns. While the underlying query does allow adding new records (the new record row wouldn't even be visible, otherwise), you would need some way to provide existing CustomerID and InvoiceID values. That will be nearly impossible to do within the confines of the current user interface.

A much better approach is to simply set "Allow Additions" to No for this form:

To add new records, I would recommend using an Unbound Add form:

Change Formatting of Read-Only Columns

While the Description, Quantity, and Unit Price columns can all be edited, the columns based on IIf() expressions cannot.

To improve the user experience, you'll want to visually distinguish these two types of columns. The simplest way to do that is to set the Border Style to "Transparent" for the read-0nly columns.

Here's the final version of our form with the border style changes and Allow Additions set to No: