What's wrong with this?

[tawcan1]

I'm working on an existing database and trying to add some new stuff... This is done with MS Access using VB coding.

What I am trying to do now is to go through a particular table in the database, arrange the data I want in order, do calculation, then insert the calculation results into a new table. I seem to be having some problems with the "OpenRecordset" function. 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 db As Database
Dim fld1 As Field
Dim fld2 As Field
Dim entry_year As String, YEAR As String
Dim tb1 As DAO.Recordset, sessYr As String, num As Integer, den As Integer, sessAvg As Integer


'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

tableName = Forms![INDIVIDUAL APR]!Box2

Set db = CurrentDb()
Set tb1 = db.OpenRecordset("SELECT [GRADES], [SESSION], [ACT CREDITS] FROM [" & tableName & "] ORDER BY [SESSION];" )

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "tblSESSIONAVERAGE", CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdTable

sessYr = ""
tb1.MoveFirst
sessYr = tb1![SESSION]

Do loop
Do Until tb1.EOF
MsgBox "In here"
Do While tb1![SESSION] = sessYr
If tb1![GRADES] <> &quot;P&quot; Then
num = (tb1![GRADES] * tb1![ACT CREDITS]) + num
den = (tb1![ACT CREDTIS]) + den

End If
tb1.MoveNext
Loop

sessAvg = num / den

'Inserting sessAvg to the table
With rst
.AddNew
.Fields(&quot;SESSION&quot; ) = sessYr
.Fields(&quot;SESSION AVERAGE&quot; ) = sessAvg
.Update
End With

sessAvg = num = den = 0
sessYr = tb1![SESSION]
Loop

tb1.Close


ErrorHandler:
MsgBox Error(Err)
Exit Sub

End Sub

==============
Tablename is already declared in the modules as a public variable.

I think using both ADO and DAO might be causing a problem. I'm trying to use only ADO but I'm not 100% sure how to switch the codes. I first used ADO to create the tblSESSIONAVERAGE table. That worked perfectly. I then added the DAO code afterwards.

Furthermore, I'm not 100% sure my if statement is ok. I'm trying to get GRADES only if the GRADES are in percentages.... can I do

If tb1![GRADES] LIKE '[0-9]*' OR IsNull([GRADES] Then
Algorithm...

???

THANX!

 

[hymn]

The old was written with DAO the new with ADO I don't think they mix and match too well

Hope this helps
Hymn