Frictionless Validity Checks

Let's apply the concept of frictionless development to the defensive programming technique of data validity checks.

Mike Wolfe

Oct 30, 2021 • 4 min read

Every Access application should be split between a front-end user interface and back-end data storage. That said, it makes sense to keep certain tables in the front-end file.

A table of validity checks is one such example.

Validity Checks

Data validity checks are queries that identify invalid data that users need to correct. They do NOT replace basic referential integrity. Rather, they augment it, filling in the gaps where referential integrity is either inappropriate or insufficient.

Adding validity checks to your application is a great way to empower your users to keep their data in good shape.

To maximize the value from this technique, you need to minimize the work required to add each additional check. You need to make the process frictionless.

The key to becoming a better developer is to make it so The Right Way is also The Easy Way.

Minimizing Development Effort

You may have noticed that I was careful to say that you need to minimize work for "each additional check." That's very different from saying that you need to minimize work overall.

Very often in software development, there is clear tradeoff between short-term and long-term efficiency. If you constantly pursue the most efficient short-term approach, you will soon end up with an unmanageable mess of an application.

By investing time upfront, you can save time over the long run.

Upfront Process

There are three steps to setting up a frictionless validity check workflow:

Create a local table to store your data validity checks
Add a combo box tied to the local table
Add a button to open the selected validity check

Local table

Add a local table named ValidityCheck. At minimum, the table should have the following fields:

QuerySuffix
QueryDescription
IsActive

The actual queries will all use the prefix "Validity" so that they are easy to organize. What we store in the table, then, is just the suffix portion. For example, a query named ValidityLeases would get stored in the QuerySuffix field as "Leases".

Combo box

Add a combo box named cbValidityCheck with RowSource as follows:

SELECT QuerySuffix, QueryDescription
FROM ValidityCheck
WHERE IsActive
ORDER BY QueryName

By filtering the combo box to only show active validity checks, you can easily toggle certain validity checks off temporarily for debugging or other purposes.

Open the query

Add code similar to the following to the button's OnClick event:

DoCmd.OpenQuery Me.cbValidityCheck.Value, , acReadOnly
AutoFitDatasheet    'see https://nolongerset.com/autofitdatasheet/

This code will display the query results in an auto-sized datasheet view. We set it to read-only since we don't want the user to accidentally make data changes in a query view that is likely missing important context.

Incremental Process

With the upfront work out of the way, adding a new validity check is an easy two-step process:

Create a query named "Validity{QuerySuffix}"
Add a row to the ValidityCheck table

And that's it!

Further Development

For extra credit, you can add a "[RUN ALL]" option to the combo box. If the user selects that option and clicks the button, the code would get a row count for each active query in the ValidityCheck table. If the row count is greater than zero, display the query; otherwise, skip it.