G
Guest
I have an access 2003 application that contains a student timeclock. The
student types in their student id number in a field, presses enter and the
code behind the form clocks the student in or out. In the past, student ID
numbers could be no greater than 9999. Now they can be up to 999999999 (9
digits instead of 4). I modified the field in the tables so that the field
type for the student id number is now a double number but when they type in a
9 digit number into the time clock I get the following error: Runtime Error
6 - Overflow. I have debugged the code until I am blue in the face and don't
know where the problem lies. Here's the line of code the error occurs on.
It never gets to the ScanStudentID sub routine but I included it anyway. Any
ideas where this error is coming from?
ScanStudentID CInt(Me!tbxID.Text) ' clock in/out
Here's the ScanStudentID sub:
Private Sub ScanStudentID(iStudentID As Integer)
Dim oRSStudentClock As Recordset
Dim oRSSumHrs As Recordset
Dim dblSumHrs As Double, dblHrsToDate As Double
Dim dblHrsNew As Double, dblHrsRemain As Double
Dim datTimeIn As Date, datTimeOut As Date
Dim strCriteria As String, sTemp As String, vTemp As Variant
Dim sSQL As String, sStatus As String, sMsg As String, booOverMax As Boolean
Dim sunhrs As Double
Dim monhrs As Double
Dim tuehrs As Double
Dim wedhrs As Double
Dim thuhrs As Double
Dim frihrs As Double
Dim sathrs As Double
Dim dayofweek As Integer
On Error GoTo Err_ScanStudentID
'modifications to allow maximum hours by day of week GG 11/25/05
' get max hours/day student can work
'GG strCriteria = "EmployeeID=" & CStr(iStudentID) ' EmployeeID is
autonumber
'GG moRSStudents.FindFirst strCriteria
sunhrs = 0
monhrs = 0
tuehrs = 0
wedhrs = 0
thuhrs = 0
frihrs = 0
sathrs = 0
GetMaxHrs CStr(iStudentID), sunhrs, monhrs, tuehrs, wedhrs, thuhrs,
frihrs, sathrs
dayofweek = Weekday(Now) 'Sunday = 1
Select Case dayofweek
Case 1 'Sunday
mdblMaxHrs = sunhrs
Case 2 'Monday
mdblMaxHrs = monhrs
Case 3 'Tuesday
mdblMaxHrs = tuehrs
Case 4 'Wednesday
mdblMaxHrs = wedhrs
Case 5 'Thursday
mdblMaxHrs = thuhrs
Case 6 'Friday
mdblMaxHrs = frihrs
Case 7 'Saturday
mdblMaxHrs = sathrs
End Select
'commenting this code GG 11/25/05
'mdblMaxHrs = 0 ' init to testable value
'If Not moRSStudents.NoMatch Then ' found student
' vTemp = moRSStudents.Fields("default_hours").Value
' mdblMaxHrs = Nz(vTemp, 0) ' use max hours from
student record (default_hours is number)
'End If
'if no value was found in the tblstudent_max_hrs table or if the value was
zero then
'use the default_hrs from the salon setup table. GG 11/25/05
If mdblMaxHrs = 0 Then ' did not find max hours
in student record
vTemp = DLookup("[reg#]", "Salon Setup") ' reg# is text
If IsNumeric(vTemp) Then
mdblMaxHrs = CDbl(vTemp) ' use max hours from Salon
Setup
Else
mdblMaxHrs = 0
End If
End If
If mdblMaxHrs = 0 Then ' still did not find max
hours
mdblMaxHrs = 24# ' max hours unlimited
End If
' get sum of hours student has worked today
sSQL = "Select sum(dailyhours) as SumHrs " & _
"From Attendance_Clock_In " & _
"Where StudentID = " & iStudentID & " " & _
"And Date = #" & Date & "# " & _
"Group By StudentID, Date;"
Set oRSSumHrs = db.OpenRecordset(sSQL, dbOpenDynaset)
If Not oRSSumHrs.EOF Then
dblSumHrs = Nz(oRSSumHrs.Fields("SumHrs").Value, 0)
Else
dblSumHrs = 0
End If
oRSSumHrs.Close
Set oRSSumHrs = Nothing
' get student timeclock record that is clocked in but not clocked out
Set oRSStudentClock = db.OpenRecordset("Attendance_Clock_In", dbOpenDynaset)
strCriteria = "StudentID=" & CStr(iStudentID) & " "
strCriteria = strCriteria & "AND Date=#" & Date & "# "
strCriteria = strCriteria & "AND Not (timeinday Is Null) "
strCriteria = strCriteria & "AND timoutday Is Null"
oRSStudentClock.FindFirst strCriteria
If Not oRSStudentClock.NoMatch Then ' student has open
timeclock record
datTimeIn = oRSStudentClock!timeinday ' get clock in time
datTimeOut = TIME() ' get clock out time
' elasped time for this
interval, rounded to 1/4
dblHrsNew = RoundHours(ElapsedTime(datTimeIn, datTimeOut))
If dblSumHrs = mdblMaxHrs Then ' student already
meet/exceed max allowable
dblHrsNew = 0 ' do not record any of
this interval
booOverMax = True ' flag indicates student
meet/exceed max allowable
Else
If dblSumHrs + dblHrsNew >= mdblMaxHrs Then ' this interval causes max
allowable to be met/exceeded
dblHrsNew = mdblMaxHrs - dblSumHrs ' record part of this
interval up to max allowable
dblHrsNew = IIf(dblHrsNew < 0, 0, dblHrsNew)
booOverMax = True ' flag indicates student
meet/exceed max allowable
Else ' this interval does not
cause max allowable to be met/exceeded
booOverMax = False ' flag indicates student
meet/exceed max allowable
End If
End If
dblSumHrs = dblSumHrs + dblHrsNew
' update the record
oRSStudentClock.Edit
oRSStudentClock!timoutday = datTimeOut
oRSStudentClock!dailyhours = RoundHours(dblHrsNew)
oRSStudentClock!MaxHrsEx = booOverMax
oRSStudentClock.Update
sMsg = "Clocked Out"
Else ' no open timeclock record
' start new timeclock record
oRSStudentClock.AddNew
oRSStudentClock!studentid = iStudentID
oRSStudentClock!Date = Date
oRSStudentClock!timeinday = TIME() ' clock in time
oRSStudentClock!timoutday = Null
oRSStudentClock.Update
sMsg = "Clocked In"
End If
oRSStudentClock.Close
Set oRSStudentClock = Nothing
' setup Status display
dblHrsRemain = mdblMaxHrs - dblSumHrs
dblHrsRemain = IIf(dblHrsRemain < 0, 0, dblHrsRemain)
sStatus = "Status:" & vbCrLf
tbxName.Enabled = True
tbxName.SetFocus
sStatus = sStatus & tbxName.Text & " - " & sMsg & vbCrLf
tbxID.SetFocus
tbxName.Enabled = True
sStatus = sStatus & "Daily Hours Allowed: " & IIf(mdblMaxHrs = 24,
"Unlimited", Format(mdblMaxHrs, "#0.00")) & vbCrLf
sStatus = sStatus & "Hours Today: " & Format(dblSumHrs, "#0.00") & vbCrLf
sStatus = sStatus & "Hours Remaining: " & IIf(mdblMaxHrs = 24,
"Unlimited", Format(dblHrsRemain, "#0.00"))
txtstatus.Caption = sStatus
dblHrsToDate = Format(Nz(DSum("dailyhours", "Attendance_Clock_In",
"StudentID=" & iStudentID), 0), "#####0.00")
Me!txtHrsToDate = dblHrsToDate
vTemp = PlaySound("chimes.wav")
Exit_ScanStudentID:
On Error GoTo 0 ' reset error handler
Exit Sub
Err_ScanStudentID:
MsgBox err.Description
Resume Exit_ScanStudentID
End Sub
student types in their student id number in a field, presses enter and the
code behind the form clocks the student in or out. In the past, student ID
numbers could be no greater than 9999. Now they can be up to 999999999 (9
digits instead of 4). I modified the field in the tables so that the field
type for the student id number is now a double number but when they type in a
9 digit number into the time clock I get the following error: Runtime Error
6 - Overflow. I have debugged the code until I am blue in the face and don't
know where the problem lies. Here's the line of code the error occurs on.
It never gets to the ScanStudentID sub routine but I included it anyway. Any
ideas where this error is coming from?
ScanStudentID CInt(Me!tbxID.Text) ' clock in/out
Here's the ScanStudentID sub:
Private Sub ScanStudentID(iStudentID As Integer)
Dim oRSStudentClock As Recordset
Dim oRSSumHrs As Recordset
Dim dblSumHrs As Double, dblHrsToDate As Double
Dim dblHrsNew As Double, dblHrsRemain As Double
Dim datTimeIn As Date, datTimeOut As Date
Dim strCriteria As String, sTemp As String, vTemp As Variant
Dim sSQL As String, sStatus As String, sMsg As String, booOverMax As Boolean
Dim sunhrs As Double
Dim monhrs As Double
Dim tuehrs As Double
Dim wedhrs As Double
Dim thuhrs As Double
Dim frihrs As Double
Dim sathrs As Double
Dim dayofweek As Integer
On Error GoTo Err_ScanStudentID
'modifications to allow maximum hours by day of week GG 11/25/05
' get max hours/day student can work
'GG strCriteria = "EmployeeID=" & CStr(iStudentID) ' EmployeeID is
autonumber
'GG moRSStudents.FindFirst strCriteria
sunhrs = 0
monhrs = 0
tuehrs = 0
wedhrs = 0
thuhrs = 0
frihrs = 0
sathrs = 0
GetMaxHrs CStr(iStudentID), sunhrs, monhrs, tuehrs, wedhrs, thuhrs,
frihrs, sathrs
dayofweek = Weekday(Now) 'Sunday = 1
Select Case dayofweek
Case 1 'Sunday
mdblMaxHrs = sunhrs
Case 2 'Monday
mdblMaxHrs = monhrs
Case 3 'Tuesday
mdblMaxHrs = tuehrs
Case 4 'Wednesday
mdblMaxHrs = wedhrs
Case 5 'Thursday
mdblMaxHrs = thuhrs
Case 6 'Friday
mdblMaxHrs = frihrs
Case 7 'Saturday
mdblMaxHrs = sathrs
End Select
'commenting this code GG 11/25/05
'mdblMaxHrs = 0 ' init to testable value
'If Not moRSStudents.NoMatch Then ' found student
' vTemp = moRSStudents.Fields("default_hours").Value
' mdblMaxHrs = Nz(vTemp, 0) ' use max hours from
student record (default_hours is number)
'End If
'if no value was found in the tblstudent_max_hrs table or if the value was
zero then
'use the default_hrs from the salon setup table. GG 11/25/05
If mdblMaxHrs = 0 Then ' did not find max hours
in student record
vTemp = DLookup("[reg#]", "Salon Setup") ' reg# is text
If IsNumeric(vTemp) Then
mdblMaxHrs = CDbl(vTemp) ' use max hours from Salon
Setup
Else
mdblMaxHrs = 0
End If
End If
If mdblMaxHrs = 0 Then ' still did not find max
hours
mdblMaxHrs = 24# ' max hours unlimited
End If
' get sum of hours student has worked today
sSQL = "Select sum(dailyhours) as SumHrs " & _
"From Attendance_Clock_In " & _
"Where StudentID = " & iStudentID & " " & _
"And Date = #" & Date & "# " & _
"Group By StudentID, Date;"
Set oRSSumHrs = db.OpenRecordset(sSQL, dbOpenDynaset)
If Not oRSSumHrs.EOF Then
dblSumHrs = Nz(oRSSumHrs.Fields("SumHrs").Value, 0)
Else
dblSumHrs = 0
End If
oRSSumHrs.Close
Set oRSSumHrs = Nothing
' get student timeclock record that is clocked in but not clocked out
Set oRSStudentClock = db.OpenRecordset("Attendance_Clock_In", dbOpenDynaset)
strCriteria = "StudentID=" & CStr(iStudentID) & " "
strCriteria = strCriteria & "AND Date=#" & Date & "# "
strCriteria = strCriteria & "AND Not (timeinday Is Null) "
strCriteria = strCriteria & "AND timoutday Is Null"
oRSStudentClock.FindFirst strCriteria
If Not oRSStudentClock.NoMatch Then ' student has open
timeclock record
datTimeIn = oRSStudentClock!timeinday ' get clock in time
datTimeOut = TIME() ' get clock out time
' elasped time for this
interval, rounded to 1/4
dblHrsNew = RoundHours(ElapsedTime(datTimeIn, datTimeOut))
If dblSumHrs = mdblMaxHrs Then ' student already
meet/exceed max allowable
dblHrsNew = 0 ' do not record any of
this interval
booOverMax = True ' flag indicates student
meet/exceed max allowable
Else
If dblSumHrs + dblHrsNew >= mdblMaxHrs Then ' this interval causes max
allowable to be met/exceeded
dblHrsNew = mdblMaxHrs - dblSumHrs ' record part of this
interval up to max allowable
dblHrsNew = IIf(dblHrsNew < 0, 0, dblHrsNew)
booOverMax = True ' flag indicates student
meet/exceed max allowable
Else ' this interval does not
cause max allowable to be met/exceeded
booOverMax = False ' flag indicates student
meet/exceed max allowable
End If
End If
dblSumHrs = dblSumHrs + dblHrsNew
' update the record
oRSStudentClock.Edit
oRSStudentClock!timoutday = datTimeOut
oRSStudentClock!dailyhours = RoundHours(dblHrsNew)
oRSStudentClock!MaxHrsEx = booOverMax
oRSStudentClock.Update
sMsg = "Clocked Out"
Else ' no open timeclock record
' start new timeclock record
oRSStudentClock.AddNew
oRSStudentClock!studentid = iStudentID
oRSStudentClock!Date = Date
oRSStudentClock!timeinday = TIME() ' clock in time
oRSStudentClock!timoutday = Null
oRSStudentClock.Update
sMsg = "Clocked In"
End If
oRSStudentClock.Close
Set oRSStudentClock = Nothing
' setup Status display
dblHrsRemain = mdblMaxHrs - dblSumHrs
dblHrsRemain = IIf(dblHrsRemain < 0, 0, dblHrsRemain)
sStatus = "Status:" & vbCrLf
tbxName.Enabled = True
tbxName.SetFocus
sStatus = sStatus & tbxName.Text & " - " & sMsg & vbCrLf
tbxID.SetFocus
tbxName.Enabled = True
sStatus = sStatus & "Daily Hours Allowed: " & IIf(mdblMaxHrs = 24,
"Unlimited", Format(mdblMaxHrs, "#0.00")) & vbCrLf
sStatus = sStatus & "Hours Today: " & Format(dblSumHrs, "#0.00") & vbCrLf
sStatus = sStatus & "Hours Remaining: " & IIf(mdblMaxHrs = 24,
"Unlimited", Format(dblHrsRemain, "#0.00"))
txtstatus.Caption = sStatus
dblHrsToDate = Format(Nz(DSum("dailyhours", "Attendance_Clock_In",
"StudentID=" & iStudentID), 0), "#####0.00")
Me!txtHrsToDate = dblHrsToDate
vTemp = PlaySound("chimes.wav")
Exit_ScanStudentID:
On Error GoTo 0 ' reset error handler
Exit Sub
Err_ScanStudentID:
MsgBox err.Description
Resume Exit_ScanStudentID
End Sub