Hi there!
I've written a function (my first!) that uses a switch() against a query to set the text in two fields of a table and I'd like to be sure that the row it's getting the data from and the row it's setting the fields in match. Specifically (and someone reading this probably already knows this!
) I'm working on a fairly complicated database that will ultimately award students a certain degree class based on the number of exam marks they have that meet the classing criteria.
The classing function seems works fine when I've tested it against a temporary table that holds all of the data it uses, but now I'd like to run it against the query that I used to create the table and update the main table holding the students' exam data. (This seemed more sensible, since the tmp table would need to be recreated every time and the query is dynamic and always has the right data.)
Ideally, I would like to explicitly state in the function somewhere that when it goes to the next record in the first recordset (rs) I want it to go to the record with the same student_id in the second recordset (rs2). I think things are only working ok right now because I've got the query and the table sorted by student_id and every student_id appears in both the query and the table. I can't guarantee that my users won't open the table and sort it according to another field and then save it.
Also, because some students will be on the border between two classes, some of their classes will be set manually, and I don't want to update those using this function - only the ones that it determines are clear cut. So eventually, the query will have fewer student_ids than the table.
This is probably quite easy, but I think I must be Googling the wrong phrases. *grin*
I would be very grateful for any help any of you might be able to offer.
-Jen
Below is my function (which I wouldn't have been able to pull together without the search function on this forum! Thanks!):
I've written a function (my first!) that uses a switch() against a query to set the text in two fields of a table and I'd like to be sure that the row it's getting the data from and the row it's setting the fields in match. Specifically (and someone reading this probably already knows this!
) I'm working on a fairly complicated database that will ultimately award students a certain degree class based on the number of exam marks they have that meet the classing criteria. The classing function seems works fine when I've tested it against a temporary table that holds all of the data it uses, but now I'd like to run it against the query that I used to create the table and update the main table holding the students' exam data. (This seemed more sensible, since the tmp table would need to be recreated every time and the query is dynamic and always has the right data.)
Ideally, I would like to explicitly state in the function somewhere that when it goes to the next record in the first recordset (rs) I want it to go to the record with the same student_id in the second recordset (rs2). I think things are only working ok right now because I've got the query and the table sorted by student_id and every student_id appears in both the query and the table. I can't guarantee that my users won't open the table and sort it according to another field and then save it.
Also, because some students will be on the border between two classes, some of their classes will be set manually, and I don't want to update those using this function - only the ones that it determines are clear cut. So eventually, the query will have fewer student_ids than the table.This is probably quite easy, but I think I must be Googling the wrong phrases. *grin*
I would be very grateful for any help any of you might be able to offer.
-Jen
Below is my function (which I wouldn't have been able to pull together without the search function on this forum! Thanks!):
Code:
Public Function classing() As String
On Error GoTo Err_Handler
'
Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset
'
Set db = CurrentDb
Set rs = db.OpenRecordset("qry_crosstab_profiles_and_aggregate")
Set rs2 = db.OpenRecordset("tbl_prt1_markbook")
'
Do While Not rs.EOF
classing = Switch( _
rs![T] >= 474 And rs![AStar] >= 2 And rs![A1] = 7, "1*", _
rs![T] >= 474 And rs![A1] >= 3 Or rs![I1] >= 6, "1", _
rs![T] >= 474 And rs![IH1] >= 1 And rs![I1] >= 5, "1", _
rs![T] >= 474 And rs![A1] < 3 And rs![I1] < 6, "2.1/I B", _
rs![T] >= 460 And rs![T] < 474 And rs![A1] >= 3 Or rs![I1] >= 6, "2.1/I C", _
rs![T] >= 460 And rs![T] < 474 And rs![IH1] >= 1 And rs![I1] >= 5, "2.1/I C", _
rs![T] >= 420 And rs![T] < 474 And rs![A21] >= 4 And rs![A1] < 3, "2.1", _
rs![T] >= 420 And rs![T] < 474 And rs![I21] >= 7, "2.1", _
rs![T] >= 420 And rs![T] < 474 And rs![A21] < 4 And rs![I21] < 7, "2.1/2.2 E", _
rs![T] >= 413 And rs![T] < 420 And rs![A21] >= 4 Or rs![I21] >= 7, "2.1/2.2 F", _
rs![T] >= 371 And rs![T] < 420 And rs![A22] >= 6 And rs![A21] < 4, "2.2", _
rs![T] >= 371 And rs![T] < 420 And rs![I22] >= 11, "2.2", _
rs![T] >= 371 And rs![T] < 420 And rs![A22] < 6 And rs![I22] < 11, "2.2/III H", _
rs![T] >= 350 And rs![T] < 371 And rs![A22] >= 3 And rs![A21] >= 2, "2.2/III I1", _
rs![T] >= 350 And rs![T] < 371 And rs![I22] >= 7 And rs![I21] >= 3, "2.2/III I2", _
rs![T] >= 320 And rs![T] < 371 And rs![A3] >= 6 And rs![A22] < 3 And rs![A21] < 2, "III", _
rs![T] >= 320 And rs![T] < 371 And rs![I3] >= 11, "III", _
rs![T] >= 280 And rs![T] < 371 And rs![A3] < 6 And rs![I3] < 11, "III/Ord/NC K", _
rs![T] >= 245 And rs![T] < 280, "Ord", _
rs![T] < 245, "Fail", _
True, "Error!" _
)
'
rs2.Edit
rs2![Overall_Class] = classing
rs2![aggregate] = rs![T]
rs2.Update
'
rs.MoveNext
rs2.MoveNext
Loop
'
rs.Close
rs2.Close
db.Close
Set db = Nothing
'
Exit Function
Err_Handler:
MsgBox Err.Description
End Function
