run time error 6 overflow

G

Guest

I keep getting the subject error message whenever I run this code, which
compiles oK.

Sub EctoCount()
Dim db As Database
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim sSQL As String
Dim CountNo As Integer
Dim CountYes As Integer
Dim CurrentID As String
Set db = CurrentDb()
Set rst1 = db.OpenRecordset("tblEctos2005", dbOpenDynaset)
Set rst2 = db.OpenRecordset("qyrHak2005Ecto", dbOpenDynaset)
rst1.Sort = "[Subsite],[Species],[Bandnum]"
Set rst1 = rst1.OpenRecordset
rst2.Sort = "[Bandnum]"
Set rst2 = rst2.OpenRecordset
CountNo = 0
CountYes = 0
Do Until rst1.EOF
CurrentID = rst1!Bandnum
sSQL = "[Bandnum]= ' " & CurrentID & " ' "
rst2.FindFirst sSQL
Do While rst2!Bandnum = CurrentID
If rst2!Ectopara = "N" Then
CountNo = CountNo + 1 ** This is where the error occurs **
Else
CountYes = CountYes + 1
End If
Loop
rst1.Edit
rst1.EctoNo = CountNo
rst1.EctoYes = CountYes
rst1.Update
CountNo = 0
CountYes = 0
rst1.MoveNext
Loop

What can one do to correct this error?
 
D

Dave Patrick

Try;
Dim CountNo As Long
Dim CountYes As Long

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
|I keep getting the subject error message whenever I run this code, which
| compiles oK.
|
| Sub EctoCount()
| Dim db As Database
| Dim rst1 As Recordset
| Dim rst2 As Recordset
| Dim sSQL As String
| Dim CountNo As Integer
| Dim CountYes As Integer
| Dim CurrentID As String
| Set db = CurrentDb()
| Set rst1 = db.OpenRecordset("tblEctos2005", dbOpenDynaset)
| Set rst2 = db.OpenRecordset("qyrHak2005Ecto", dbOpenDynaset)
| rst1.Sort = "[Subsite],[Species],[Bandnum]"
| Set rst1 = rst1.OpenRecordset
| rst2.Sort = "[Bandnum]"
| Set rst2 = rst2.OpenRecordset
| CountNo = 0
| CountYes = 0
| Do Until rst1.EOF
| CurrentID = rst1!Bandnum
| sSQL = "[Bandnum]= ' " & CurrentID & " ' "
| rst2.FindFirst sSQL
| Do While rst2!Bandnum = CurrentID
| If rst2!Ectopara = "N" Then
| CountNo = CountNo + 1 ** This is where the error occurs **
| Else
| CountYes = CountYes + 1
| End If
| Loop
| rst1.Edit
| rst1.EctoNo = CountNo
| rst1.EctoYes = CountYes
| rst1.Update
| CountNo = 0
| CountYes = 0
| rst1.MoveNext
| Loop
|
| What can one do to correct this error?
 
M

Marshall Barton

LAF said:
I keep getting the subject error message whenever I run this code, which
compiles oK.

Sub EctoCount()
Dim db As Database
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim sSQL As String
Dim CountNo As Integer
Dim CountYes As Integer
Dim CurrentID As String
Set db = CurrentDb()
Set rst1 = db.OpenRecordset("tblEctos2005", dbOpenDynaset)
Set rst2 = db.OpenRecordset("qyrHak2005Ecto", dbOpenDynaset)
rst1.Sort = "[Subsite],[Species],[Bandnum]"
Set rst1 = rst1.OpenRecordset
rst2.Sort = "[Bandnum]"
Set rst2 = rst2.OpenRecordset
CountNo = 0
CountYes = 0
Do Until rst1.EOF
CurrentID = rst1!Bandnum
sSQL = "[Bandnum]= ' " & CurrentID & " ' "
rst2.FindFirst sSQL
Do While rst2!Bandnum = CurrentID
If rst2!Ectopara = "N" Then
CountNo = CountNo + 1 ** This is where the error occurs **
Else
CountYes = CountYes + 1
End If
Loop
rst1.Edit
rst1.EctoNo = CountNo
rst1.EctoYes = CountYes
rst1.Update
CountNo = 0
CountYes = 0
rst1.MoveNext
Loop
Click to expand...


You need a rst2.MoveNext inside the inner loop.

You will also need to check for rst2.EOF in case CurrentID
is the last one in the recordset:

Do While rst2!Bandnum = CurrentID And Not rst2.EOF
 
G

Guest

Thanks Dave and Marshall. I used Marshall's recommendations and it worked
fine. It is people like you that make learning access development a more
pleasureable experience than banging one's head against a wall.

LAF
 
G

Guest

Hi Marshall,

I spoke a little too soon. One of the records in rst1 was matched by the
last record in rst2, so the movenext statement put rst2 at the EOF. I got
the whole program to work by putting an If Then statement before the
movenext. If rst.EOF then GoTo, which then exited the loop and updated the
record in rst1.

Question: Is there a way of doing this without If-then go to? Your
recommendation for And Not rst2.EOF was not sufficient.

All the best,

Lenny

Marshall Barton said:
LAF said:
I keep getting the subject error message whenever I run this code, which
compiles oK.

Sub EctoCount()
Dim db As Database
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim sSQL As String
Dim CountNo As Integer
Dim CountYes As Integer
Dim CurrentID As String
Set db = CurrentDb()
Set rst1 = db.OpenRecordset("tblEctos2005", dbOpenDynaset)
Set rst2 = db.OpenRecordset("qyrHak2005Ecto", dbOpenDynaset)
rst1.Sort = "[Subsite],[Species],[Bandnum]"
Set rst1 = rst1.OpenRecordset
rst2.Sort = "[Bandnum]"
Set rst2 = rst2.OpenRecordset
CountNo = 0
CountYes = 0
Do Until rst1.EOF
CurrentID = rst1!Bandnum
sSQL = "[Bandnum]= ' " & CurrentID & " ' "
rst2.FindFirst sSQL
Do While rst2!Bandnum = CurrentID
If rst2!Ectopara = "N" Then
CountNo = CountNo + 1 ** This is where the error occurs **
Else
CountYes = CountYes + 1
End If
Loop
rst1.Edit
rst1.EctoNo = CountNo
rst1.EctoYes = CountYes
rst1.Update
CountNo = 0
CountYes = 0
rst1.MoveNext
Loop
Click to expand...


You need a rst2.MoveNext inside the inner loop.

You will also need to check for rst2.EOF in case CurrentID
is the last one in the recordset:

Do While rst2!Bandnum = CurrentID And Not rst2.EOF
Click to expand...
 
D

Douglas J. Steele

Due to how VBA evaluates boolean conditions, I don't think you can avoid the
If-Then-Else construct. However, there's no need to use a GOTO: you could
simply use Exit While.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


LAF said:
Hi Marshall,

I spoke a little too soon. One of the records in rst1 was matched by the
last record in rst2, so the movenext statement put rst2 at the EOF. I got
the whole program to work by putting an If Then statement before the
movenext. If rst.EOF then GoTo, which then exited the loop and updated
the
record in rst1.

Question: Is there a way of doing this without If-then go to? Your
recommendation for And Not rst2.EOF was not sufficient.

All the best,

Lenny

Marshall Barton said:
LAF said:
I keep getting the subject error message whenever I run this code, which
compiles oK.

Sub EctoCount()
Dim db As Database
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim sSQL As String
Dim CountNo As Integer
Dim CountYes As Integer
Dim CurrentID As String
Set db = CurrentDb()
Set rst1 = db.OpenRecordset("tblEctos2005", dbOpenDynaset)
Set rst2 = db.OpenRecordset("qyrHak2005Ecto", dbOpenDynaset)
rst1.Sort = "[Subsite],[Species],[Bandnum]"
Set rst1 = rst1.OpenRecordset
rst2.Sort = "[Bandnum]"
Set rst2 = rst2.OpenRecordset
CountNo = 0
CountYes = 0
Do Until rst1.EOF
CurrentID = rst1!Bandnum
sSQL = "[Bandnum]= ' " & CurrentID & " ' "
rst2.FindFirst sSQL
Do While rst2!Bandnum = CurrentID
If rst2!Ectopara = "N" Then
CountNo = CountNo + 1 ** This is where the error occurs **
Else
CountYes = CountYes + 1
End If
Loop
rst1.Edit
rst1.EctoNo = CountNo
rst1.EctoYes = CountYes
rst1.Update
CountNo = 0
CountYes = 0
rst1.MoveNext
Loop
Click to expand...


You need a rst2.MoveNext inside the inner loop.

You will also need to check for rst2.EOF in case CurrentID
is the last one in the recordset:

Do While rst2!Bandnum = CurrentID And Not rst2.EOF
Click to expand...
Click to expand...
 

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

overflow error 6 7
No current record error 3021 1
FindFirst and FindLast 3
consistent error on first record 11
Sort Question 15
Need Coding Advice 3
Importing a multi-sheet Excel spreadsheet into Access 9
3034 You tried to commit or rollback a transaction 1

Top