Change Checkbox based on criteria entered in other fields

[ItsBarbara]

I have a form for tracking potential failures. The teachers must enter the
numeric grade at 30 weeks, 33 weeks, and 36 weeks (Each week is its own field)
If the most recent grade is below a 70 then I need a check mark in the
Failure check box.

I am trying to create an automated solution so that the Curriculum AP can
just open a form and the checkmarks are there for the students currently in
danger of failing.

For example, if a student had a grade of 60 at 30 weeks, there would be a
check in the failures box. If he then achieved a 72 at 33 weeks, the
checkmark would be removed. If, however, he dipped below 70 at 36 weeks, the
checkmark would automatically appear again.

 

[Klatuu]

In the Form's Current Event:

Dim intLoop as Integer

For intLoop = 36 To 30 Step -3
If Not IsNull(Me.Controls("Week" & Cstr(intLoop))) Then
If Me.Controls("Week" & Cstr(intLoop)) < 70 Then
Me.chkFailDanger = True
End If
Exit For
End If
Next intLoop

 

[Mr B]

ou can use the OnCurrent event of your form to check the value of each field
in progression.

For example to check the 33 Week field and make it checked if the grade is
below 70 or unchecked if the grade is 70 or greater, you can use a statement
like:

If Me.NameOf33WkControl < 70 then
Me.NameOfCheckbox = -1
else
Me.NameOfCheckbox = 0
End If

You would then just repeat the If statement for each control you want to
check.

 

[ItsBarbara]

I tried this, it works, but not until I revisit the field where I have
entered the grade that is below a 70
For example

For student A I entered a score of 35 in Week30
For student B I entered a score of 55 in Week30
For student c I entered a score of 60 in Week30

Failure didn't indicate a check until I went back to click into the field
Week30 for each student.

I closed and came back in, and it still did not populate until I clicked in
the Week30 field for each student.

Any ideas as to what this Newbie is doing wrong??

 

[ItsBarbara]

Klatuu
You have been so helpful in the past.

I tried this (and the one posted by Mr B) and I am not having any luck.
It will populate the check box, but only if I revisit the field for each
record.

Also, I can't get it to take a check off if subsequent weeks attain a
passing grade (>69)

Would it be better to run an update query after I lock the fields (for
example, after Week30 grades are entered, they cannot be changed after the
cutoff date)??

 

[Mr B]

My post did not include all I intended to post.

Place the following code in the After Update event of the 33Wk control:

If Me.NameOf33WkControl < 70 then
Me.NameOfCheckbox = -1
Else
Me.NameOfCheckbox = 0
End If

Also place simular code for each of the other controls, just changing the
name of the controls.

 

[ItsBarbara]

Mr B - You are the Bomb!!

This is working great!!!

The After Update is exactly what was needed.