record

[edp1959]

I have a code that looks for missing numbers and puts them in a table via a make table query. I have thousands of records and the missing numbers records can get very large. How can I modify this code to stop once it finds the first missing number and make the table?

Dim dbs As Database
Dim rsTrans As DAO.Recordset
Dim rsMiss As DAO.Recordset
Dim SQL As String
Dim Num As Long
Dim iCount As Long

Set dbs = CurrentDb

SQL = "Select [Part No] as Num " & _
" from tblCSSearch " & _
" order by [Part No]"

Set rsTrans = dbs.OpenRecordset(SQL)

' delete table tblMissingNumbers, if already exists.
On Error Resume Next
DoCmd.DeleteObject acTable, "tblMissingNumbers"
On Error GoTo 0

' create new table tblMissingNumbers.
SQL = "Create Table tblMissingNumbers(MissingNum Long)"
DoCmd.RunSQL SQL

Set rsMiss = dbs.OpenRecordset("tblMissingNumbers")
Num = rsTrans!Num

Do While Not rsTrans.EOF
rsTrans.MoveNext
If rsTrans.EOF Then Exit Do
Num = Num + 1

Do While rsTrans!Num > Num
rsMiss.AddNew
rsMiss!MissingNum = Num
rsMiss.Update
iCount = iCount + 1
Num = Num + 1

Loop
Loop

Set rsTrans = Nothing
Set rsMiss = Nothing
Set dbs = Nothing

MsgBox "Created table tblMissingNumbers for " & iCount & " missing numbers"

 

[namliam]

Change
Do While Not rsTrans.EOF
rsTrans.MoveNext
If rsTrans.EOF Then Exit Do
Num = Num + 1

Do While rsTrans!Num > Num
rsMiss.AddNew
rsMiss!MissingNum = Num
rsMiss.Update
iCount = iCount + 1
Num = Num + 1

Loop
Loop

To:

Do While Not rsTrans.EOF and icount=0
rsTrans.MoveNext
If rsTrans.EOF Then Exit Do
Num = Num + 1

Do While rsTrans!Num > Num and icount=0
rsMiss.AddNew
rsMiss!MissingNum = Num
rsMiss.Update
iCount = iCount + 1
Num = Num + 1

Loop
Loop
It should now exit your search loop after one found missing number...
Regards

The Mailman

 

[edp1959]

Thanks a million!!! This worked great.