Not sorting numbers correctly?

[Randy]

Access 2000...I copied an example db off the internet that will find missing
numbers (Gaps) in a table. If I enter into my form: 1, 2, 3,5,6,4 with
the 4 being entered last, I get 4 showing up as a missing number, even
though it isn't. I have tried sorting the number field on my form and
table. Looking at the table and form shows the numbers as sorted properly
1,2,3,4,5,6 etc. But I keep getting any number entered out of order on my
form as a missing number...Here's a copy of the module. What gives...thanks
for any help..Randy

Function FindGaps()
Dim MyDB As DAO.Database
Dim mytbl As DAO.Recordset
Dim mytbl2 As DAO.Recordset
Dim lastnum As Long
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE GAPS.* FROM GAPS;"
DoCmd.SetWarnings True
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set mytbl = MyDB.OpenRecordset("CompletedCertificates")
Set mytbl2 = MyDB.OpenRecordset("GAPS")
With mytbl
.MoveFirst
lastnum = !CertNumber
Do Until .EOF
If !CertNumber - lastnum > 1 Then
lastnum = lastnum + 1
Do
With mytbl2
.AddNew
!missing = lastnum
.Update
lastnum = lastnum + 1
If mytbl!CertNumber - lastnum = 0 Then
Exit Do
End If
End With
Loop
.MoveNext
Else
lastnum = !CertNumber
.MoveNext
End If
Loop
End With
mytbl.Close
mytbl2.Close
Set mytbl = Nothing
Set mytbl2 = Nothing
Set MyDB = Nothing
End Function

 

[Ken Snell [MVP]]

What is CompletedCertificates? Assuming that it is a table, you cannot
expect any specific order of the records from a table. You must use a query
to get order.

Try changing this
Set mytbl = MyDB.OpenRecordset("CompletedCertificates")

to this
Set mytbl = MyDB.OpenRecordset("SELECT * FROM CompletedCertificates
ORDER BY CertNumber")

 

[Randy]

Thanks Ken, I tried your suggerestion but got a "Complile Error Syntax
Error" any other ideas?

 

[Ken Snell [MVP]]

I'm going to guess here.... when you pasted my suggested code step in your
code, is it on one line or two? It should be all one line.

 

[Randy]

Thant was it! Thanks again...Randy

I'm going to guess here.... when you pasted my suggested code step in your
code, is it on one line or two? It should be all one line.