I'm trying to create a table automatically (like when a button is clicked on the form), but am stuck. I'm not 100% sure if I need to use SQL or not to accomplish this. Really new to this stuff so please help me.
The follow is the code I have right now. When I tried to run this I get an error saying that the field INDIVIDUAL APR can't be found...
INDIVIDUAL APR is a form that the button is located. There are bunch data displayed on this particular form.
-----------------------------
'Session Average
Private Sub Session_Average()
On Error GoTo ErrorHandler
Dim tb20 As DAO.TableDef, table As DAO.Recordset, ENTRY_YEAR As String, YEAR As String
Dim session_year As String, yr As Integer, ssAvg As String, CURRENT_YEAR As Integer
tableName = Forms![INDIVIDUAL APR]!Box2
Set db = CurrentDb()
'Checking if table exists, if yes delete table
If ObjectExists(acTable, "SESSION AVERAGE") Then DoCmd.DeleteObject acTable, "SESSION AVERAGE"
'Create new table
Set tb20 = db.CreateTableDef("SESSION AVERAGE")
With tb20
.Fields.Append .CreateField("SESSION", dbText)
.Fields.Append .CreateField("SESSION AVERAGE", dbText)
End With
'Begin algorithm for calculating session average
ENTRY_YEAR = Form![INDIVIDUAL APR]!Box27
YEAR = Right$(ENTRY_YEAR, 2)
If YEAR = "00" Then
yr = 99
Else
yr = CInt(YEAR) - 1
End If
Do Until yr > yr + 5
'Algorithm for winter sessions
ssAvg = ""
session_year = ""
session_year = CStr(YEAR) + "W"
ssAvg = Round(DSum(" [GRADES] * [ACT CREDITS] ", tableName, "[SESSION] = '" & session_year & "' AND ([GRADES] LIKE '[0-9]*' OR [GRADES] = NULL )") / DSum("[ACT CREDITS]", tableName, "[SESSION] = '" & session_year & "' AND ([GRADES] LIKE '[0-9]*' OR [GRADES] = NULL)"), 2)
table.AddNew
table![SESSION] = session_year
table![SESSION AVERAGE] = ssAvg
table.Update
'Algorithm t summer sessions
ssAvg = ""
session_year = ""
session_year = CStr(YEAR) + "S"
ssAvg = Round(DSum(" [GRADES] * [ACT CREDITS] ", tableName, "[SESSION] = '" & session_year & "' AND ([GRADES] LIKE '[0-9]*' OR [GRADES] = NULL )") / DSum("[ACT CREDITS]", tableName, "[SESSION] = '" & session_year & "' AND ([GRADES] LIKE '[0-9]*' OR [GRADES] = NULL)"), 2)
table.AddNew
table![SESSION] = session_year
table![SESSION AVERAGE] = ssAvg
table.Update
yr = yr + 1
Loop
Exit Sub
ErrorHandler:
MsgBox Error(Err)
Exit Sub
End Sub
-----------------------------------------
Here's a similar code that actually works:
Private Sub AU_query()
On Error GoTo ErrorHandler
Dim qdf1 As QueryDef, tbl3 As DAO.TableDef, table As DAO.Recordset
Dim strSQL2 As String, AUREPORT As String
tableName = Forms![INDIVIDUAL APR]!Box2
Set db = CurrentDb()
If ObjectExists(acTable, "AU REPORT") Then DoCmd.DeleteObject acTable, "AU REPORT"
Set tbl = db.OpenRecordset("SELECT * FROM [" & tableName & "];")
'Create new table.
Set tbl3 = db.CreateTableDef("AU REPORT")
With tbl3
.Fields.Append .CreateField("REQ COURSE", dbText)
.Fields.Append .CreateField("ACT COURSE", dbText)
.Fields.Append .CreateField("OPTION", dbText)
.Fields.Append .CreateField("CREDITS", dbText)
.Fields.Append .CreateField("SESSION", dbText)
.Fields.Append .CreateField("YEAR", dbText)
.Fields.Append .CreateField("MATH AU", dbText)
.Fields.Append .CreateField("BAS SCI AU", dbText)
.Fields.Append .CreateField("COMP STUD AU", dbText)
.Fields.Append .CreateField("ENG DES AU", dbText)
.Fields.Append .CreateField("ENG SCI AU", dbText)
End With
db.TableDefs.Append tbl3
Set table = db.OpenRecordset("AU REPORT", dbOpenTable)
If (tbl.RecordCount = 0) Then
Exit Sub
Else
tbl.MoveFirst
Do Until tbl.EOF
table.AddNew
table![REQ COURSE] = tbl![REQ COURSE]
table![ACT COURSE] = tbl![ACT COURSE]
table![OPTION] = tbl![OPTION]
table![CREDITS] = tbl![ACT CREDITS]
table![SESSION] = tbl![SESSION]
table![YEAR] = tbl![YEAR]
calYear = session_edit(tbl![SESSION])
If (Not (calYear = "20" Or calYear = "19")) Then
Set Autable = db.OpenRecordset("SELECT * FROM [AU " & calYear & "];")
checkAU = "[COURSE] = '" & tbl![ACT COURSE] & "'"
Autable.FindFirst checkAU
If (Not (Autable.NoMatch)) Then
table![MATH AU] = Autable![MATH AU]
table![BAS SCI AU] = Autable![BAS SCI AU]
table![COMP STUD AU] = Autable![COMP STUD AU]
table![ENG DES AU] = Autable![ENG DES AU]
table![ENG SCI AU] = Autable![ENG SCI AU]
End If
End If
table.Update
tbl.MoveNext
Loop
End If
Exit Sub
ErrorHandler:
MsgBox Error(Err)
If ObjectExists(acTable, "AU REPORT") Then DoCmd.DeleteObject acTable, "AU REPORT"
Exit Sub
End Sub
---------------------
Not sure why there are bunch variables declared in the 2nd one that aren't even used in the sub.
Thanx everyone!
The follow is the code I have right now. When I tried to run this I get an error saying that the field INDIVIDUAL APR can't be found...

INDIVIDUAL APR is a form that the button is located. There are bunch data displayed on this particular form.
-----------------------------
'Session Average
Private Sub Session_Average()
On Error GoTo ErrorHandler
Dim tb20 As DAO.TableDef, table As DAO.Recordset, ENTRY_YEAR As String, YEAR As String
Dim session_year As String, yr As Integer, ssAvg As String, CURRENT_YEAR As Integer
tableName = Forms![INDIVIDUAL APR]!Box2
Set db = CurrentDb()
'Checking if table exists, if yes delete table
If ObjectExists(acTable, "SESSION AVERAGE") Then DoCmd.DeleteObject acTable, "SESSION AVERAGE"
'Create new table
Set tb20 = db.CreateTableDef("SESSION AVERAGE")
With tb20
.Fields.Append .CreateField("SESSION", dbText)
.Fields.Append .CreateField("SESSION AVERAGE", dbText)
End With
'Begin algorithm for calculating session average
ENTRY_YEAR = Form![INDIVIDUAL APR]!Box27
YEAR = Right$(ENTRY_YEAR, 2)
If YEAR = "00" Then
yr = 99
Else
yr = CInt(YEAR) - 1
End If
Do Until yr > yr + 5
'Algorithm for winter sessions
ssAvg = ""
session_year = ""
session_year = CStr(YEAR) + "W"
ssAvg = Round(DSum(" [GRADES] * [ACT CREDITS] ", tableName, "[SESSION] = '" & session_year & "' AND ([GRADES] LIKE '[0-9]*' OR [GRADES] = NULL )") / DSum("[ACT CREDITS]", tableName, "[SESSION] = '" & session_year & "' AND ([GRADES] LIKE '[0-9]*' OR [GRADES] = NULL)"), 2)
table.AddNew
table![SESSION] = session_year
table![SESSION AVERAGE] = ssAvg
table.Update
'Algorithm t summer sessions
ssAvg = ""
session_year = ""
session_year = CStr(YEAR) + "S"
ssAvg = Round(DSum(" [GRADES] * [ACT CREDITS] ", tableName, "[SESSION] = '" & session_year & "' AND ([GRADES] LIKE '[0-9]*' OR [GRADES] = NULL )") / DSum("[ACT CREDITS]", tableName, "[SESSION] = '" & session_year & "' AND ([GRADES] LIKE '[0-9]*' OR [GRADES] = NULL)"), 2)
table.AddNew
table![SESSION] = session_year
table![SESSION AVERAGE] = ssAvg
table.Update
yr = yr + 1
Loop
Exit Sub
ErrorHandler:
MsgBox Error(Err)
Exit Sub
End Sub
-----------------------------------------
Here's a similar code that actually works:
Private Sub AU_query()
On Error GoTo ErrorHandler
Dim qdf1 As QueryDef, tbl3 As DAO.TableDef, table As DAO.Recordset
Dim strSQL2 As String, AUREPORT As String
tableName = Forms![INDIVIDUAL APR]!Box2
Set db = CurrentDb()
If ObjectExists(acTable, "AU REPORT") Then DoCmd.DeleteObject acTable, "AU REPORT"
Set tbl = db.OpenRecordset("SELECT * FROM [" & tableName & "];")
'Create new table.
Set tbl3 = db.CreateTableDef("AU REPORT")
With tbl3
.Fields.Append .CreateField("REQ COURSE", dbText)
.Fields.Append .CreateField("ACT COURSE", dbText)
.Fields.Append .CreateField("OPTION", dbText)
.Fields.Append .CreateField("CREDITS", dbText)
.Fields.Append .CreateField("SESSION", dbText)
.Fields.Append .CreateField("YEAR", dbText)
.Fields.Append .CreateField("MATH AU", dbText)
.Fields.Append .CreateField("BAS SCI AU", dbText)
.Fields.Append .CreateField("COMP STUD AU", dbText)
.Fields.Append .CreateField("ENG DES AU", dbText)
.Fields.Append .CreateField("ENG SCI AU", dbText)
End With
db.TableDefs.Append tbl3
Set table = db.OpenRecordset("AU REPORT", dbOpenTable)
If (tbl.RecordCount = 0) Then
Exit Sub
Else
tbl.MoveFirst
Do Until tbl.EOF
table.AddNew
table![REQ COURSE] = tbl![REQ COURSE]
table![ACT COURSE] = tbl![ACT COURSE]
table![OPTION] = tbl![OPTION]
table![CREDITS] = tbl![ACT CREDITS]
table![SESSION] = tbl![SESSION]
table![YEAR] = tbl![YEAR]
calYear = session_edit(tbl![SESSION])
If (Not (calYear = "20" Or calYear = "19")) Then
Set Autable = db.OpenRecordset("SELECT * FROM [AU " & calYear & "];")
checkAU = "[COURSE] = '" & tbl![ACT COURSE] & "'"
Autable.FindFirst checkAU
If (Not (Autable.NoMatch)) Then
table![MATH AU] = Autable![MATH AU]
table![BAS SCI AU] = Autable![BAS SCI AU]
table![COMP STUD AU] = Autable![COMP STUD AU]
table![ENG DES AU] = Autable![ENG DES AU]
table![ENG SCI AU] = Autable![ENG SCI AU]
End If
End If
table.Update
tbl.MoveNext
Loop
End If
Exit Sub
ErrorHandler:
MsgBox Error(Err)
If ObjectExists(acTable, "AU REPORT") Then DoCmd.DeleteObject acTable, "AU REPORT"
Exit Sub
End Sub
---------------------
Not sure why there are bunch variables declared in the 2nd one that aren't even used in the sub.
Thanx everyone!