jfgambit said:
Can you send a copy of the database to me. I'll see what I can do for you.
If the database is to big to be sent to a hotmail account send me a message and I'll give you a different account.
It's 44MB big.
Though not the same this is kinda what I'm talking about:
'VIEW AU REPORT button.
'This function calls the function AU_query.
Private Sub Label124_Click()
On Error GoTo ErrorHandler
If (IsNull(Me!Box2)) Then
MsgBox ("Please first select a student by clicking on GET STUDENT or browsing through the STUDENT LIST.")
Exit Sub
End If
AU_query
DoCmd.OpenReport "AU REPORT", acViewPreview
Exit Sub
ErrorHandler:
MsgBox Error(Err)
Exit Sub
End Sub
'This will make a table to get the AU's from the appropriate AU table.
'This table is then used to generate a student's AU report.
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
As you can see, it creates a new table when View AU Report button is created. Just wondering if you can do the same thing for quiery.
