Report on Missing Records in a Sequence

[A7thKevin]

Our database tracks batches which have a Batch Number (ID). I'd like to know if there is a way to report on or return ID(s) that are missing from the sequence. Sometimes a user forgets to enter a batch and it would be nice to see which number is missing. I realize we can't report on a record that isn't there. But I was wondering if there is code that will allow us to see a break in the sequence of batch numbers?
Thank you.

[Seth Schrock]

Depending on how many records you are returning, this could get expensive in terms of computer resources and take awhile to complete since you would have to loop through each record.

Because of the time it could take, I think that it would be best to first test if a number is missing and then if there is, check which one.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strRst As String
Dim c As Integer 'If number of records is over 32,767, then us Long
Dim s As Long
Dim e As Long
 
 
strRst = "SELECT ... ORDER BY ID"
Set db = CurrentDb
Set rst = db.OpenRecordset(strRst, dbOpenDynaset)
 
With rst
    .MoveLast
    e = !ID
    .MoveFirst
    s = !ID
 
    If e <> (s + .RecordCount - 1) Then
        For c = s to e
            If c <> !ID Then Debug.Print c
        Next c
    End If
 
    .Close
End With
 
Set db = Nothing
Set rst = Nothing