School database: How to use controls and a macro to simplify student exam registrations. (1 Viewer)

[SurreyNick]

I am developing an Access 2010 database for tracking students’ progress and exam achievements and I am trying to figure out a way to quickly and easily register students on exams.

The table I am trying to populate is the Tests table in the attached diagram. This is a junction table with ExamPapers on one side and Students on the other. The relationship diagram also shows two other tables; Classes and Classes_Students, the latter being another junction table.

Each year around 280 students across 9 class cohorts take 6 exams each. Students generally sit exams in class groups and what I want to do is design a form for managing the registrations in batches.

The idea I have is a tutor selects an exam paper from a control linked to the ExamPapers table and then selects a class group from another control linked to the Classes_Students table. These two selections returns a list of the Active students in that class who have NOT taken the chosen exam. The tutor then selects individual students or all students (from this list) who will be taking the exam, chooses a date for the exam and then clicks a control button which runs a macro and populates the three fields in the Tests table.

There might be a better solution than the one I propose, but is something like this possible and if it is, can someone help by walking me through how I go about doing it?

Thank you.

 

[theDBguy]

Hi. Sounds like you could use a multi-select listbox to select individual or all students who haven't taken the exam yet. So, in your code, you will have to construct a Row Source using the information about the exam and the student group selected by the user/proctor. The Row Source also needs to filter out those students who already took the exam by checking the Tests table.

 

[plog]

I know you posted about forms, but I'm not convinced your table structure is correct. In your relationships, Tests and ExamPapers are not related to Classes, is that correct? Shouldn't Tests and ExamPapers be related to a Student and a Class?

If so, then Tests should be related to Classes_Students not Students. To do that StudentID comes out of Tests and is replaced by Classes_StudentsID.

 

[SurreyNick]

I know you posted about forms, but I'm not convinced your table structure is correct. In your relationships, Tests and ExamPapers are not related to Classes, is that correct? Shouldn't Tests and ExamPapers be related to a Student and a Class?........

Hi plog
Thank you for showing concern about the table structure. I also don't want to stray from the topic of this thread but I might submit another thread in the appropriate section asking for advice on my db structure
N.

 

[SurreyNick]

....So, in your code, you will have to construct a Row Source using the information about the exam and the student group selected by the user/proctor. The Row Source also needs to filter out those students who already took the exam by checking the Tests table.

Hi DBguy
Thank you for the advice. I have gone away and now know how to do the multi-select listbox to select individual or all students who haven't taken the exam yet. That was straight forward. But this next bit has me stumped. I have never tackled writing an SQL statement in a Row Source. Could you or someone else please elaborate a bit more, by perhaps giving me a sample of code that accomplishes the sort task you describe, or point me in the direction of a tutorial I can follow?
Many thanks
N.

 

[bastanu]

You should build a form to help you with the various "class management tasks". Here is one from a demo db I used to work for:

You would have a subform listing your classes (by AcademicYear), once you select one class the subform (or multi select listbox) listing the (active) students would refresh. Now you would select your students (if using a subform you could have a IsSelected yes/no field to select the student(s) the you would select your exam and date and click a button that would run an update (or append) query. Many ways to do this, just give it a try and come back if you get stuck.

Cheers,
Vlad

 

[SurreyNick]

I’m happy to go away and learn how to do things but I just can’t get my head around how to proceed with the task at hand. I could be way off track here but the way I see it is as follows:

1. I need a method to populate a subform with a list of active students who are in a (user selected) class who have NOT taken a (user selected) exam.
2. I then need a method for the user to select some or all of these students in the populated subform.
3. Next I need a method for the user to choose a date for the exam to be taken by the selected students.
4. And finally I need a method which takes the selected students and appends them to my Tests table along with the selected exam and the chosen date. [StudentID], [ExamPaperID], and [TestDate] respectively.

As far as I can work out I cannot use a standard query to achieve task 1. To achieve this task I need two unbound controls (a list box and a combo box) plus a control button with an SQL statement. The list box is for the user to select one or more classes [ClassID]. The combo box is for the user to choose the exam [ExamPaperID]. The On Click EventProcedure of the control button runs an SQL statement which selects from my Students table all StudentID fields which are Active (True) where that StudentID and the selected ExamPaperID are not in the Tests table and then returns all students which meet this condition to the subform. I have been looking for answers on the internet but I don’t have any experience with VBA and I’m struggling to write this bit of code. I’m hoping someone on this forum can give me some help and I have also bought myself a book on Access 2010 VBA Programming because I think I’m going to need it.

For task 2. I like that idea of an IsSelected yes/no field on the subform for the user to select the students. Does this need to be a field in one of my tables or is it just on the form or in an underlying query?

For task 3. Is there is some sort of unbound control I can use for choosing a date where the value is remembered for use in task 4. (Quick update. I worked out how to put an unbound date picker control on the form - simply a text box with its Format property set to Date and the Show Date Picker property set to "For dates").

Finally, for task 4. I am assuming a need another control button with an SQL statement which takes the IsSelected students [StudentID] from the subform, and appends each one to the Tests table along with the ExamPaperID from the combo box and the date from the other unbound control. An additional bit of code which then clears the unbound controls and de-populates the subform would be nice and neat. Once again, I don’t know what this bit of code needs to be.

Anyway, that’s what I think the solution is. Am I on the right track?

Nick.

 

[bastanu]

Nick,

I think you are on the right track. Here are some suggestions on how to do some of the steps:

On the main form have a textbox for the date picker, a listbox or combo-box for the exam and a control to select the class. For this you could use a listbox or combobox, my preference is a subform as it is easy to customize (column order, widths, etc.), shows all the records and it is easily filtered (by academic year, teacher, etc.).

In the OnCurrent event of the class subform (or the AfterUpdate event of the combo or listbox if you go with those) you would do a Me.sfrmStudentClassList.Requery where sfrmStudentClassList is a second subform whose recordsource is a temporary table populated by an append query that limits the students to the active ones in the class with ClassID equal to the first class subform (or list or combo box). In the append query (qryAPPEND_SelectedClassListActive) you include your student id, name, gender, Active (=True)from Students, classid from Classes_Students, maybe classref from Classes. The temporary table has all these fields plus an extra Yes/No IsSelected field. The temporary table gets emptied (CurrendDb.Execute "DELETE * FROM tmpStudentCleass;",dbFailOnError) and populated (CurrendDb.Execute "qryAPPEND_SelectedClassListActive",dbFailOnError) every time you change your selection of the class.

Now once you have students selected with the IsSelected check box and the exam date populated in your textbox you just need to run a second append query (after you validate the selections) that takes the IsSelected=True StudentIds from tmpStudentCleass, exam date and class from the main form and appends them to Tests. And finally run another refresh of the student list subform.

Cheers,
Vlad

 

[MajP]

So in this example I go the simple approach and put the boolean field in the table.
Here is the code in the main form on current event

Private Sub Form_Current()
LoadChecks
End Sub
Public Sub LoadChecks()
Dim strSql As String
Dim rs As DAO.Recordset
'First uncheck everything in tblSelections
strSql = "Update tblSelections Set Selected = FALSE"
CurrentDb.Execute strSql
'Add the users selections
strSql = "Select * from tblUserSelections where UserID_FK = " & Me.UserID
Set rs = CurrentDb.OpenRecordset(strSql)
Do While Not rs.EOF
strSql = "Update tblSelections set Selected = true where SelectionID = " & rs!SelectionID_FK
CurrentDb.Execute strSql
rs.MoveNext
Loop
Me.Refresh
End Sub

1. clear out the previous selections
2. load the selections related to the main form user

Here is the code in the subform

Private Sub Selected_Click()
If Selected Then
AddSelection
Else
RemoveSelection
End If
End Sub
Public Sub AddSelection()
Dim strSql As String
Dim UserID As Long
Dim SelectionID As Long
UserID = Me.Parent.UserID
SelectionID = Me.SelectionID
strSql = "Insert into tblUserSelections (UserID_FK, SelectionID_FK) VALUES (" & UserID & ", " & SelectionID & ")"
CurrentDb.Execute strSql
End Sub
Public Sub RemoveSelection()
Dim strSql As String
Dim UserID As Long
Dim SelectionID As Long
UserID = Me.Parent.UserID
SelectionID = Me.SelectionID
strSql = "Delete * from tblUserSelections WHERE USERID_FK = " & UserID & " AND SelectionID_FK = " & SelectionID
CurrentDb.Execute strSql
End Sub

To me this is the easiest solution to read and persist the selections
This example is in the link I posted, just ignore the unbound form discusstion.

 

[SurreyNick]

Thanks!
I'll get to work.
N.

 

[SurreyNick]

I’d just like to thank everyone who helped with this post. I’m pleased to report I managed to achieve what I wanted. In the end I went with a simpler solution but in spite of this I still couldn’t have done it without the help I was given here, so thank you.

The spec changed because my son (he’s the one I’m creating the DB for) told me when tutors set a test they set it for every student in the class. He just wanted a form where a tutor could select a class, then select an exam and lastly choose a date for the test and having done so click a button and populate the Tests table.

Following the advice I was given in this thread I created a “Choose Class” combo box to hold the ClassID, a “Choose Exam Paper” combo box for the ExamPaperID and a “Set Test Date” text box with a date picker for the TestDate. The form contains two subforms. When the user selects a class one subform displays all the exams already taken by that class and the other returns a list of all the active students in the class. This second subform is for information only, basically a visual check the tutor has selected the right class group because they will recognise the student names. Having made the three selections the tutor then clicks a “Click To Enrol” button.

When the button is clicked the script first checks there are no Null values in the three boxes. If there are then a message box is displayed and the user is forced to provide valid selections. If the validation is passed the user is presented with a YesNo message box prompting them to double check their selections. If they click to continue the script creates a temporary table and appends to it the StudentID for each of the students in the class along with the ExamPaperID and TestDate from the combo box and text box. The contents of the temporary table are then appended to the Tests table and the temporary table deleted. Finally the contents of the combo boxes and text box are cleared and the form re-queried.

It may not be very elegant solution and I expect there is a much better way of writing the procedure but it works. I will have to create another form enabling tutors to edit exam enrollments in case a planned date changes and/or students don’t sit it, but I’d planned on creating this form anyway so it’s no great shakes.

I did think about limiting the list of exams in the Choose Exam Paper combo box to only those that the class hasn’t already taken but couldn’t work out how to do the code. Nor could I work out how to create a bit of code that would check if the class had taken an exam and then pop up a message box asking if the tutor wants the class to take the exam again. Perhaps I’ll revisit this when I’ve learned a lot more

 

[bastanu]

Well done! You can very easily edit the exam list combo to only show the exams that the class didn't take by using a unmatched query (left outer join between ExamId in tblExamList to whatever query you use to populate the subform showing the exams the class did take) - no VBA needed.

Cheers,
Vlad