The code uses the Data Access Objects library, so you need to reference that library. Choose References from the Tools menu (still in the VBE), check the Microsoft DAO 3.x Object Library (as shown in Figure 1
), and click OK.
To execute the code, return to Access and click the Search button in the example form. Figure 2 shows the populated list box, which contains an ordered list of missing values from the OrderID field in the Orders table.
|Figure 2. Set a Reference to DAO: This list contains all the missing values from the OrderID field in the Orders table.|
Using the table and field constants, the code populates a recordset employing an ordered set of values from the field you want to search. A For...Next statement cycles through the records, comparing the values of the last record and the current record in an interesting way. As long as the For statement's expressions evaluate to different values, the For loops add the current For value to a collection. For instance, if the first two values in the recordset are 1 and 4, the For loop evaluates as follows:
For lngMiss = lngLast + 1 To lngNext - 1
For lngMiss = 1 + 1 To 4 - 1
For lngMiss = 2 To 3
The For statement's expressions evaluate to values that represent the first and last missing values between the first two existing values. Consequently, the For...Next loop inserts the values 2 and 3 into a custom collection. The code selects the next record and repeats this process until the code reaches the last record in the recordset.
Before closing the recordset, the code adds one final value to the collection: the next value in the sequence. If you want a list strictly of missing values, comment out this line. Otherwise, it's an easy way to grab the next value in the sequence if you're generating values as you go. (The example evaluates an AutoNumber field for the sake of convenience only. In a working situation, you wouldn't need to generate the next AutoNumber value.)
A second For...Next statement populates the list box control by retrieving values from the collection. Just remember that the list box portion of the code simplifies the example; it isn't required to actually find missing values. Adapt the code to handle the missing values. You could generate a report or even reuse the values.
Accommodate Missing Values
Finding missing date values requires a few changes in the code, but basically, the process is the same. The variables compensate for the Date/Time data type and the comparison statements use date functions. Remember, this procedure will evaluate dates with time values, but it won't consider the time values in the comparison. The downloadable demo contains both procedures: the one in Listing 1 and another for finding missing dates.
Finding missing values in a field that contains sequential values isn't difficult. With a small bit of code, you can generate a list of missing values to reuse, audit, or share with others in some meaningful way.