I created a small routine that might help. The "ID" field that you are using (looking for gaps) can not be autonumber.
Once an autonumber is deleted, I think it is gone.
Anyway I did test this with a couple of tables, and it seems to work. There is a function and a test routine.
Code:
'---------------------------------------------------------------------------------------
' Procedure : MissingRecNos
' Author : mellon
' Date : 21/03/2015
' Purpose : To identify any missing numbers in the "id" field in a table.
'This function finds the highest used number; and the number of records
'in the table. If the numbers are the same, then there are no gaps.
'If the numbers are different, then some gap(s) my exist.
'This routine uses debug.print to write info to the immediate window.
'
' *** This will NOT work if the "ID" field is an autonumber ***
' *** ***
'**************************************************************
'---------------------------------------------------------------------------------------
' Parameters:
' tablename---- is the name of the table you are working with
' recIdField--- is the identifier field in the table
'---------------------------------------------------------------------------------------
Function MissingRecNos(tableName As String, recIdField As String) As String
Dim sql As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim mmax As Long, i As Long
Dim NoRec As Long
10 On Error GoTo MissingRecNos_Error
20 Set db = DBEngine(0)(0)
30 Set rs = db.OpenRecordset(tableName)
'get highest used number
40 rs.MoveLast
50 mmax = rs.Fields(recIdField)
'How many records in the table
60 NoRec = DCount(recIdField, tableName)
70 Debug.Print mmax - NoRec & " unused records exist"
'what are the unused numbers
80 For i = 1 To mmax
' If DCount(recIdField, tableName, recIdField = i) = 0 Then
' Debug.Print i
90 sql = "SELECT Count(" & tableName & "." & recIdField & ") AS CountX FROM " & tableName _
& " WHERE " & recIdField & " = " & i
100 Set rs = db.OpenRecordset(sql)
110 If rs!Countx = 0 Then
120 MissingRecNos = MissingRecNos & recIdField & " " & i & " is available" & vbCrLf
130 End If
140 Next i
150 On Error GoTo 0
160 Exit Function
MissingRecNos_Error:
170 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure MissingRecNos of Module Module4"
End Function
Code:
Sub testMissing()
Dim tbl As String
Dim fld As String
tbl = "employeeTask" ' "authors" '<<<your table name
fld = "MyID" ' "author_id" '<<<your field name
Debug.Print MissingRecNos(tbl, fld)
End Sub
Give it a try. You can move the output to where ever it makes sense to you.
NOTE: For anyone following this thread.
I tried to use a Dcount() but constantly got a type mismatch, after several attempts I swithed to
the sql and open recordset approach.