You might want to take it a little further than that and set it up so that
all the other check boxes are unticked rather than just the one below. This
would handle other possible situations, such as where a higher level might
have been ticked accidentally and you then tick a lower one, or where for
some reason a level might not have been ticked when the student reached that
level, so you have to skip over one and tick the one two levels up. To cover
these possibilities as well as the normal one do the following:
1. Identify each check box representing a level by setting its Tag property
to something like:
UpdateMe
2. Add the following function to the form's module (you can just copy it
from here and paste it into the module )
Private Function UpdateTicks()
Dim ctrl As Control
For Each ctrl In Me.Controls
If Me.ActiveControl = True Then
If ctrl.Tag = "UpdateMe" And _
ctrl.Name <> Me.ActiveControl.Name Then
ctrl = False
End If
End If
Next ctrl
End Function
3. As the AfterUpdate event property of each of the check boxes put the
following in each one's properties sheet:
=UpdateTicks()
When any of the check boxes is ticked any others which are ticked, whether
above or below the one ticked, will be unticked.
However, this might not be the best approach. I assume that you are using
separate Boolean (Yes/No) fields for each level. This is not a very good
design in principle; its what's known as 'encoding data as column headings'.
As you are only recording the level the student has currently reached you
could use a single field with the level currently reached. For this you'd
create a table, QualificationLevels say, with fields QualificationLevel
(Text) and LevelNumber (number) for instance, the former for the name of the
qualification level, the latter for its position in the sequence of levels
(the lowest would be 1, the next 2 and so on). In the students table you'd
then have a QualificationLevel field (test again) and on the form you'd have
a combo box bound to this with, as its RowSource property:
SELECT QualificationLevel
FROM QualificationLevels
ORDER BY LevelNumber;
Having a single field like this in the students table would make querying
much easier, e.g. you could count how many students have reached each level
with a query such as:
SELECT LevelNumber, Students.QualificationLevel,
COUNT(*) As NumberOfStudents
FROM Students INNER JOIN QualificationLevels
ON Students.QualificationLevel = QualificationLevels.QualificationLevel
GROUP BY LevelNumber, Students.QualificationLevel;
You could go even further than this and record all the levels each student
has reached, with other information such as the date they reached the level.
This involves a many-to-many relationship between Students and
QualificationLevels, so instead of having a QualificationLevel field in the
Students table you'd create another table to model the relationship. This
table, StudentQualifications say, would have fields such as StudentID (a
foreign key referencing the primary key of the Students table),
QualificationLevel (a foreign key referencing the primary key of the
QualificationLevels table, a DateQualified field and any other fields you
might wish to include, e.g. a grade. For data entry in this case you'd use a
Students form in single form view with a subform (in continuous form or
datasheet view) based on a query on the StudentQualifications table, e.g. to
show the qualification levels reached in date order:
SELECT * FROM StudentQualifications
ORDER BY DateQualified;
The form and subform would be linked on the StudentID fields. The
QualificationLevel field in the subform would be the ControlSource of a combo
box as described above.
Ken Sheridan
Stafford, England
