Overflow Error When Doing Calculation

[Comtech]

Hi,

I'm new to using this forum, and I hope I'm doing this right. I have an issue with a database I'm designing for a cadet corps. I'm trying to set it up so that it automatically calculates attendance on each parade night. First of all, here is the code.

Private Sub Form_Open(Cancel As Integer)


On Error GoTo Err_Form_Open

Dim nbrPresent, CountCadets, CountList, CadetsatPresent As Integer
Dim AttAverage As Double
Dim dbTraining As Object
Dim rstPresent As Object
Dim strAverage As String
Set dbTraining = CurrentDb
Set rstPresent = dbTraining.OpenRecordset("tblListofActivities")
CountList = DCount("[ActivityID]", "tblListofActivities")
CountCadets = DCount("[RecordID]", "tblCadetMain")


Select Case CountCadets
Case Is > 0
[Forms]![frmAttendance]![sfrmAttendanceCadets].SetFocus
DoCmd.GoToRecord , , acFirst
For I = 1 To CountList
CadetsatPresent = DCount("[RecordID]", "tblActivitiesCadets", "[ActivityID] =" & [Forms]![frmAttendance]![sfrmAttendanceCadets].[Form].ActivityID)
nbrPresent = DCount("[RecordID]", "tblActivitiesCadets", "[ActivityID] =" & [Forms]![frmAttendance]![sfrmAttendanceCadets].[Form].ActivityID & "AND [Present]=True")
rstPresent.Edit:
rstPresent("CadetsPresent").Value = nbrPresent:
rstPresent("CadetsCounted").Value = CadetsatPresent:
AttAverage = Round((nbrPresent / CadetsatPresent), 1) * 100:
strAverage = AttAverage & "%":
rstPresent("CadetAverage").Value = strAverage:
rstPresent.Update:


If I <> CountList Then
rstPresent.MoveNext
DoCmd.GoToRecord , , acNext
End If
Next I
DoCmd.GoToRecord , , acFirst
End Select


Exit_Form_Open:
Exit Sub


Err_Form_Open:
MsgBox Err.Description
Resume Exit_Form_Open

End Sub

Whenever I try to run this code, I get the following error:

Run Time Error '6'
Overflow

and the debugger points to the line highlighted in red. I even tried adding a button, and putting the code in the OnClick property, but it does the same thing. Does anybody know what this means and what I can do to solve it? Any help would be greatly appreciated.

Thank you.
Comtech

[davegri]

Try it this way. Long integers can hold a much larger value than an Integer.
Also, when you define all on one line, only the last is an Integer, the first three are Variants.
You understand that integers and long integers can contain only whole numbers, no decimal places.

Dim nbrPresent as Long
Dim CountCadets as Long
Dim CountList as Long
Dim CadetsatPresent As Long

[ssanfu]

Also, it is NOT necessary to put a colon at the end of lines

rstPresent.Edit:
rstPresent("CadetsPresent").Value = nbrPresent:
rstPresent("CadetsCounted").Value = CadetsatPresent:

And welcome to the forum........

[Comtech]

ssanfu

Thank you for your quick reply. I did everything you said, and it still gives me the same error. Could there be a problem with the way one of the tables is set up. I was playing around with it last night, and discovered that it works when I move one of the lines (highlighted in red below) outside of the loop. The problem is, I need it inside the loop to because this value may be different each time.

Private Sub Form_Open(Cancel As Integer)


On Error GoTo Err_Form_Open


Dim nbrPresent As Long
Dim CountCadets As Long
Dim CountList As Long
Dim CadetsatPresent As Long
Dim AttAverage As Double
Dim dbTraining As Object
Dim rstPresent As Object
Dim strAverage As String


Set dbTraining = CurrentDb
Set rstPresent = dbTraining.OpenRecordset("tblListofActivities")

CountList = DCount("[ActivityID]", "tblListofActivities")
CountCadets = DCount("[RecordID]", "tblCadetMain")

'This line was inside the loop
CadetsatPresent = DCount("[RecordID]", "tblActivitiesCadets", "[ActivityID] =" & [Forms]![frmAttendance]![sfrmAttendanceCadets].[Form].ActivityID)


Select Case CountCadets


Case Is > 0


[Forms]![frmAttendance]![sfrmAttendanceCadets].SetFocus
DoCmd.GoToRecord , , acFirst


For I = 1 To CountList


nbrPresent = DCount("[RecordID]", "tblActivitiesCadets", "[ActivityID] =" & [Forms]![frmAttendance]![sfrmAttendanceCadets].[Form].ActivityID & "AND [Present]=True")
rstPresent.Edit
rstPresent("CadetsPresent").Value = nbrPresent
rstPresent("CadetsCounted").Value = CadetsatPresent
AttAverage = Round((nbrPresent / CadetsatPresent), 1) * 100
strAverage = AttAverage & "%"
rstPresent("CadetAverage").Value = strAverage
rstPresent.Update
If I <> CountList Then
rstPresent.MoveNext
DoCmd.GoToRecord , , acNext
End If
Next I


DoCmd.GoToRecord , , acFirst


End Select


Exit_Form_Open:
Exit Sub


Err_Form_Open:
MsgBox Err.Description
Resume Exit_Form_Open

End Sub

[Comtech]

OK I got it to work. I used an Update query in conjunction with the code. Thanks for your reply.

[ssanfu]

It is very confusing because it looks like you are setting the focus to a sub form and trying to do some calculations using a recordset, trying to keep the sub form and the recordset in sync. I think it would have been easier just using code.....


We're both happy you got it working.