I've been working on this particular sub for a while now. Basically the sub reads a table, pulls data out of this table, does calculation, then inserts results into a new table. The table that gets read has bunch fields, but I'm interested in the GRADES and SESSIONS. Due to some administration issues, sometimes cells in SESSION could be NULL, and sometimes GRADES could be NULL. Want to avoid these particular rows when doing calculation. Also only want to calculate grades that are numeric, so no P or F stuff. This code is a part of another function that basically says..
tblSESSIONAVERAGE
DoCmd.OpenReport "MAIN REPORT" acViewPreview
Here's my code:
------------------------------------
'Creating tblSESSIONAVERAGE for later use
Private Sub session_average()
On Error GoTo ErrorHandler
Dim dbs As Database
Dim tbs As TableDef
Dim fld1 As Field
Dim fld2 As Field
Dim entry_year As String, YEAR As String
Dim sessYr As String, num As Integer, den As Integer, Avg As Double, sessAvg As String
Dim stu_num As String
'Checking if table exists, if yes delete table
If ObjectExists(acTable, "tblSESSIONAVERAGE") Then DoCmd.DeleteObject acTable, "tblSESSIONAVERAGE"
'Creating table
Set dbs = CurrentDb
Set tbs = CurrentDb.CreateTableDef("tblSESSIONAVERAGE")
Set fld1 = tbs.CreateField("SESSION", dbText)
Set fld2 = tbs.CreateField("SESSION AVERAGE", dbText)
tbs.Fields.Append fld1
tbs.Fields.Append fld2
tbs.Fields.Refresh
dbs.TableDefs.Append tbs
dbs.TableDefs.Refresh
stu_num = Forms![INDIVIDUAL APR]!Box2
Dim res As ADODB.Recordset
Set res = New ADODB.Recordset
Set db = CurrentDb
res.Open "SELECT * From [" & stu_num & "] Order By [SESSION]", CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "tblSESSIONAVERAGE", CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdTable
sessYr = ""
sessAvg = ""
Avg = 0
num = 0
den = 0
res.MoveFirst
sessYr = res("SESSION")
Do Until res.EOF
Do While res("SESSION") = sessYr
If IsNull(res("SESSION")) Or IsNull(res("GRADES")) Then
MsgBox ("NULL")
res.MoveNext
End If
If IsNumeric(res("GRADES")) And res("GRADES") <> "P" And res("GRADES") <> "F" Then
sessYr = res("SESSION")
num = CInt(res("GRADES")) * CInt(res("ACT CREDITS")) + num
den = CInt(res("ACT CREDITS")) + den
End If
res.MoveNext
Loop
Avg = num / den
sessAvg = Round(Avg, 2)
With rst
.AddNew
.Fields("SESSION") = sessYr
.Fields("SESSION AVERAGE") = sessAvg
.Update
End With
Avg = 0
num = 0
den = 0
sessAvg = ""
sessYr = res("SESSION")
Loop
MsgBox ("Done!")
res.Close
ErrorHandler:
MsgBox Error(Err)
Exit Sub
End Sub
------------------
If I use the exact code right now i run into 2 problems...depending on the record table I use.
1.) If the record table has GRADES that are NULL or SESSION that are NULL, I get a error window that says "Overflow." I also get Overflow if the SESSION has only 1 record and the GRADES in this particular record is "P" or "F."
The report doesn't get opened, but the table gets created partially (no results where the SESSION is NULL or GRADE is NULL).
I tried to put MsgBox in the If statement testing for NULL, I get the message boxes alright, but still get overflow after.
2.) If I go into the record table and eliminate the above situation, I get this message:
"Either BOF or EOF is True, or the current record has been deleted. Request operation requres a current record."
Report doesn't open either. I tried to put a MsgBox outside of the 2nd loop but it doesn't show up. I believe for some reason the loop isn't exiting.
Any idea why this might be happening? Plaese help! THANX!
tblSESSIONAVERAGE
DoCmd.OpenReport "MAIN REPORT" acViewPreview
Here's my code:
------------------------------------
'Creating tblSESSIONAVERAGE for later use
Private Sub session_average()
On Error GoTo ErrorHandler
Dim dbs As Database
Dim tbs As TableDef
Dim fld1 As Field
Dim fld2 As Field
Dim entry_year As String, YEAR As String
Dim sessYr As String, num As Integer, den As Integer, Avg As Double, sessAvg As String
Dim stu_num As String
'Checking if table exists, if yes delete table
If ObjectExists(acTable, "tblSESSIONAVERAGE") Then DoCmd.DeleteObject acTable, "tblSESSIONAVERAGE"
'Creating table
Set dbs = CurrentDb
Set tbs = CurrentDb.CreateTableDef("tblSESSIONAVERAGE")
Set fld1 = tbs.CreateField("SESSION", dbText)
Set fld2 = tbs.CreateField("SESSION AVERAGE", dbText)
tbs.Fields.Append fld1
tbs.Fields.Append fld2
tbs.Fields.Refresh
dbs.TableDefs.Append tbs
dbs.TableDefs.Refresh
stu_num = Forms![INDIVIDUAL APR]!Box2
Dim res As ADODB.Recordset
Set res = New ADODB.Recordset
Set db = CurrentDb
res.Open "SELECT * From [" & stu_num & "] Order By [SESSION]", CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "tblSESSIONAVERAGE", CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdTable
sessYr = ""
sessAvg = ""
Avg = 0
num = 0
den = 0
res.MoveFirst
sessYr = res("SESSION")
Do Until res.EOF
Do While res("SESSION") = sessYr
If IsNull(res("SESSION")) Or IsNull(res("GRADES")) Then
MsgBox ("NULL")
res.MoveNext
End If
If IsNumeric(res("GRADES")) And res("GRADES") <> "P" And res("GRADES") <> "F" Then
sessYr = res("SESSION")
num = CInt(res("GRADES")) * CInt(res("ACT CREDITS")) + num
den = CInt(res("ACT CREDITS")) + den
End If
res.MoveNext
Loop
Avg = num / den
sessAvg = Round(Avg, 2)
With rst
.AddNew
.Fields("SESSION") = sessYr
.Fields("SESSION AVERAGE") = sessAvg
.Update
End With
Avg = 0
num = 0
den = 0
sessAvg = ""
sessYr = res("SESSION")
Loop
MsgBox ("Done!")
res.Close
ErrorHandler:
MsgBox Error(Err)
Exit Sub
End Sub
------------------
If I use the exact code right now i run into 2 problems...depending on the record table I use.
1.) If the record table has GRADES that are NULL or SESSION that are NULL, I get a error window that says "Overflow." I also get Overflow if the SESSION has only 1 record and the GRADES in this particular record is "P" or "F."
The report doesn't get opened, but the table gets created partially (no results where the SESSION is NULL or GRADE is NULL).
I tried to put MsgBox in the If statement testing for NULL, I get the message boxes alright, but still get overflow after.
2.) If I go into the record table and eliminate the above situation, I get this message:
"Either BOF or EOF is True, or the current record has been deleted. Request operation requres a current record."
Report doesn't open either. I tried to put a MsgBox outside of the 2nd loop but it doesn't show up. I believe for some reason the loop isn't exiting.
Any idea why this might be happening? Plaese help! THANX!
