> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Triggering Cache Invalidation on the Hidden Duplicate Values Form
- URL: https://nolongerset.com/triggering-cache-invalidation/
- Published: 2024-02-27T15:32:38.000Z
- Updated: 2026-05-08T12:38:54.000Z
- Description: In this installment, we explore two different approaches for deciding when to invalidate our cache: user-triggered and automatic.
- Author: Mike Wolfe
- Tags: Hiding Dupe Values, #Import 2026-05-20 02:59

*This article is Part 8 of my ["Hiding Duplicate Values" Mini-Series](https://nolongerset.com/hiding-duplicate-values/)*.

*To follow along with the screenshots and examples, download the sample data from here:* [*Sample Data for the "Hiding Duplicate Values" Series*](https://nolongerset.com/sample-data-dupe-values/).

---

## Background Information

In Part 7, we touched on one of the hardest problems in computer science: knowing *when* to invalidate a cache.

You need some way to verify the contents of the cache periodically. "Periodically" could mean:

- At regular intervals (e.g., every five minutes)
- Whenever a record is updated on the current form
- Whenever *any* change is made to the underlying database (hello, [rowversion](https://nolongerset.com/under-100-rowversion/)!)
- Whenever the form is refreshed
- Via an explicit \[Refresh\] button on the form
- Whenever the form is opened

There is no one correct approach. It all depends on the situation.

## Demonstrating an Invalid Cache

What exactly does it look like when the cache becomes "invalid"?

### GOOD

Using our running example, let's start by showing the form with a "valid" cache, that is, one where the data stored in the cache matches the underlying table data:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2024/02/image-89.png)

I added the three primary key fields at the end of the form for reference purposes.

As you can see, each customer name appears once at the first instance of a related invoice item. Similarly, each invoice and due date appear once for each invoice.

When this form first opens, the top InvItemID gets saved for each customer and invoice in a pair of dictionary objects as described in [Part 6](https://nolongerset.com/caching-hidden-dupes/):

```vba
?this.TopCustomerItems(4)
5008
?this.TopInvoiceItems(1004)
5008
```

The names and dates are visible in the top row because the following function calls return **True**:

- `IsTopCustomerItem(CustomerID:=4, InvItemID:=5008)`
- `IsTopInvoiceItem(InvoiceID:=1004, InvItemID:=5008)`

Similarly, the names and dates are *hidden* in the second row because these two function calls return **False**:

- `IsTopCustomerItem(CustomerID:=4, InvItemID:=5007)`
- `IsTopInvoiceItem(InvoiceID:=1004, InvItemID:=5007)`

### BAD

Now, let's imagine that the proctologist from the [iconic "ASSMAN" episode](https://seinfeldlaw.com/2019/07/04/the-assman/) lived in a state that had chosen to "ban specific and certain words on a vanity license plate that are particularly offensive because the same message could be conveyed through other words."

As an alternative to "ASSMAN," we'll assume our proctologist friend would settle for "BUTTMAN" instead. If we change the description of our first item from "Assman License Plate" to "Buttman License Plate," we've suddenly changed the alphabetical order of the invoice items in our first invoice.

If we click the "Refresh All" records button in the ribbon, here's what we get:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2024/02/image-90.png)

The reason the form looks like this is because the top InvItemID values that were looked up when the form first opened are still stored in our cache of dictionaries:

```vba
?this.TopCustomerItems(4)
5008
?this.TopInvoiceItems(1004)
5008
```

However, since we've changed the Description of InvItemID 5007, that is now the top invoice item detail for both CustomerID 4 and InvoiceID 1004.

Thus, the calls to `IsTopCustomerID()` and `IsTopInvoiceID()` return the same results as before, but those values are now incorrect.

## How to Invalidate the Cache

Now that we've seen the practical effects of an invalid cache, let's briefly review how to "invalidate" the cache. In our case, the way we've chosen to invalidate the cache is by simply clearing it and letting it rebuild itself naturally. 

Here's the code from Part 7:

```vba
'Invalidate the cache
Private Function ClearCache()
    Set this.TopCustomerItems = Nothing
    Set this.TopInvoiceItems = Nothing
End Function
```

## Two Basic Approaches

Now that we know *why* we need to invalidate the cache and *how* to do it, let's tackle the trickiest problem of all: *when* to do it.

In this article, we'll explore two basic approaches to invalidating the cache:

- User-triggered
- Automatic

The **user-triggered** approach has a few advantages:

- Simple to implement
- Less surprising to the user

The **automatic** approach has a few advantages:

- Requires no explicit action from the user
- Does not require invalidating the whole cache

### User-Triggered Approach

For this approach, we'll start by adding a \[Refresh All\] button to the form header:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2024/02/image-91.png)

Then, we'll set the button's OnClick property to \[Event Procedure\] and code it as follows:

```vba
Private Sub btnRefreshAll_Click()
    ClearCache
    Me.Requery
End Sub
```

Super simple.

### Automatic Approach

I mentioned at the top of the article that clicking on the \[Refresh All\] button in the *Access ribbon* (i.e., not the command button we just added) will reload the form and rerun all the `IsTopCustomerItem()` and `IsTopInvoiceItem()` calls. Due to our caching, though, the calls to `IsTopXxxItem()` will return stale information.

In the user-triggered approach, we took the brute-force approach of clearing the entire cache. This has the advantage of being simple, but it also forces us to re-populate all the items in the cache, including those that may not require it.

With the automatic approach, we will add code to the AfterUpdate event of the Description text box that invalidates the cache *for just the current customer and invoice* and not every record in the form.

```vba
'Invalidate the cache for just the current customer and invoice
Private Sub tbDescription_AfterUpdate()
    Dim CustomerID As Long: CustomerID = Me.CustomerID.Value
    With this.TopCustomerItems
        If .Exists(CustomerID) Then .Remove CustomerID
    End With
    
    Dim InvoiceID As Long: InvoiceID = Me.InvoiceID.Value
    With this.TopInvoiceItems
        If .Exists(InvoiceID) Then .Remove InvoiceID
    End With
End Sub
```

Now, when the user clicks the **Refresh All** button in the Access ribbon, the `IsTopXxxItem()` functions will repopulate the dictionary for only those customers and invoices that the current user may have changed.

#### Notes about the automatic approach

- Even though our `ClearCache()` routine above sets the `TopXxxItems` dictionary variables to `Nothing`, we don't have to check for `Nothing` in the `tbDescription_AfterUpdate()` routine because we declared them `As New` in the form's header (see [Part 6](https://nolongerset.com/caching-hidden-dupes/#the-code) for that code).
- Generally speaking, **you have to be careful about putting code in the AfterUpdate event of a *control* on a *bound* form**. That's because the event is called when the control's value is changed, *but before the record is updated*. Thus, if the user cancels editing the record, the control's value could revert to what it was previously. That reversion *does not trigger* the control's AfterUpdate event. In most cases, you would want to monitor the form's OnUndo event to capture the data reversion. For our purposes, though, it's not worth the extra complexity. The worst case scenario is that our code performs two extra unnecessary database calls, a performance hit so minor that users almost certainly won't notice it.
- We only call this code from the *Description* text box because that is the only editable control that affects the sort order of the form. The Quantity and Unit Price controls are editable, but changing them will not affect the outcome of our `IsTopXxxItem()` functions.
- This approach does not account for the actions of other users. If another user changes an invoice item description, it will not be reflected with this approach (unless it happened to overlap with a change to the same invoice by the current user). How to handle those changes is an entirely different (and deeper) topic. Remember, cache invalidation is hard!