Triggering Cache Invalidation on the Hidden Duplicate Values Form

Mike Wolfe -

This article is Part 8 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.

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!)
  • 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:

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:

?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 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:

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:

?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:

'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:

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

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.

'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 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!