Newbie needs help

[jrlkan]

I am very new with using Access. I need to be able to query a field for nonsequential data. Example:
Under the field for Transaction Number, I have 1-70 but 62 is not listed. How could I make a query to find that 62 has been skipped????
Thanks for your help and patience.

[schlauberger]

Wouldn't you be better off with a sub procedure or a function? The following is a sub procedure that could easily be converted into a function:

Public Sub MissingTransNumber()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim intCount As Integer, x As Integer, y As Integer
Dim intMissing(99) As Integer
Dim strMissing As String, strMessage As String

intCount = 1

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Open "SELECT intTransNum FROM tblTransAct ORDER BY intTransNum", cnn, adOpenDynamic, adLockOptimistic

With rst
If .EOF = False And .BOF = False Then
Do Until .EOF = True
While !intTransNum <> intCount
x = x + 1
intMissing(x) = intCount
intCount = intCount + 1
Wend
intCount = intCount + 1
.MoveNext
Loop
End If
End With
rst.Close
cnn.Close

Set rst = Nothing
Set cnn = Nothing

For y = 1 To x
strMissing = strMissing & CStr(intMissing(y)) & ", "
Next

strMessage = "The following sequential values are missing: " & strMissing

MsgBox strMessage, vbInformation + vbOKOnly, "Missing Values"

End Sub