RunTime Error # 6 OVERFLOW

R

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
 
B

Bas Cost Budde

Randy said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with module 2
Not sorting numbers correctly? 4
VB Critique 2
Permission Error 1
Runtime Error 3021: 'No Current Record' 2
Overflow error 6 3
Handling error 2
VBA works in orignal but fails in copy of database 1

Top