RunTime Error # 6 OVERFLOW

[Randy]

Acccess 2000. I'm trying to get this module to work. I downloaded from a db off the internet. Its supposed to find gaps in a sequence of numbers. It stops at lastnum = !CertNumber with an error message of "Runtime error # 6 Overflow" any ideas...Thanks...Randy

Function FindGaps()
Dim MyDB As DAO.Database
Dim mytbl As DAO.Recordset
Dim mytbl2 As DAO.Recordset
Dim lastnum As Integer
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

 

[Bas Cost Budde]

Acccess 2000. I'm trying to get this module to work. I downloaded
from a db off the internet. Its supposed to find gaps in a sequence of
numbers. It stops at *lastnum = !CertNumber* with an error message of
"Runtime error # 6 Overflow" any ideas...Thanks...Randy

Function FindGaps()
Dim MyDB As DAO.Database
Dim mytbl As DAO.Recordset
Dim mytbl2 As DAO.Recordset
Dim lastnum As Integer

change Integer to Long in the line above.

 

[Randy]

Thank you very much. Works great...Randy

change Integer to Long in the line above.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea