AfterUpdate code to validate check boxes

[HeislerKurt]

To be eligible for a study, a patient has to meet certain criteria.
There are 7 things that could prevent the patient from being eligible
(e.g., "Patient is ill," "Patient is pregnant," "Patient is on
medication," etc.).

On a form, these 7 criteria are listed as 7 check boxes (Yes/No
fields). The nurse is asked to "check all that apply." If at least one
of the 7 boxes is checked, a text box says, "Patient is NOT eligible."
If none of the 7 boxes is checked, the text box says, "Patient IS
eligible"

One way to code this is to create a command button that, when clicked,
evaluates the 7 check boxes and then returns the appropriate text. But
I'd like to avoid using a command button (it's one more thing the user
has to click, and it could easily be ignored). Instead, I'd like the
text box to get populated on the fly (as items are checked or
unchecked).

I could add an AfterUpdate code for each check box, something like ...

If chkCriteria1.Value = True OR chkCriteria2.Value = True OR ...
chkCriteria7.Value = True Then
Me.txtEligibility.Value = "Patient is NOT eligible."
Else
Me.txtEligibility.Value = "Patient IS eligible."
End If

But I would have to add this to all 7 checkboxes. Is there a more
efficient way to code this?

Thank you!

Kurt

 

[Douglas J. Steele]

Create a function (not a sub) that does the evaluation of the 7 check boxes.
For the sake of argument, call it "EvaluateCheckBoxes".

Function EvaluateCheckBoxes()

If chkCriteria1.Value = True _
OR chkCriteria2.Value = True
OR chkCriteria3.Value = True

OR chkCriteria4.Value = True
OR chkCriteria5.Value = True

OR chkCriteria6.Value = True
OR chkCriteria7.Value = True Then

Me.txtEligibility.Value = "Patient is NOT eligible."

Else

Me.txtEligibility.Value = "Patient IS eligible."

End If

End Function

(Don't worry if the function doesn't return a value. It doesn't have to with
what you're going to be doing)

Select all 7 of the check boxes at once.

Go into the Properties window. For the AfterUpdate event, put the name of
the function: "=EvaluationCheckBoxes()" (without the quotes, but with the
equal sign and parentheses)

 

[Steve]

When a checkbox is checked it has a value of -1 and unchecked 0. The
absolute value of -1 is 1 so if any checkbox is checked the sum of the
checkboxes is greater than 0. You can use this fact to accomplish your goal.
Put the following expression in the control source of the textbox where you
currently display whether the patient is eligible or not:
=IIF(Sum(Abs(ChkBox1) + Abs(ChkBox2) ..... Abs(ChkBox7)) = 0,"Patient Is
Eligible","Patient Is Not Eligible")

The textbox will automatically display "Patient Is Eligible" when no
checkboxes are checked and automatically display "Patient Is Not Eligible"
when any checkbox is ckecked.



PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[HeislerKurt]

=IIF(Sum(Abs(ChkBox1) + Abs(ChkBox2) ..... Abs(ChkBox7)) = 0,"Patient Is

Eligible","Patient Is Not Eligible")

I've tried this and many permutations, but the text box keeps
returning "#Error" I'm definitely referring to correct names for the
check box controls, too. For instance, just to keep it simple, this
abbreviated version returns "#Error."

=IIf(Sum(Abs([ChkBox1])+Abs([ChkBox2]))=0,"Eligible","Not Eligible")

Any ideas?

 

[Marshall Barton]

=IIF(Sum(Abs(ChkBox1) + Abs(ChkBox2) ..... Abs(ChkBox7)) = 0,"Patient Is
Eligible","Patient Is Not Eligible")

I've tried this and many permutations, but the text box keeps
returning "#Error" I'm definitely referring to correct names for the
check box controls, too. For instance, just to keep it simple, this
abbreviated version returns "#Error."

=IIf(Sum(Abs([ChkBox1])+Abs([ChkBox2]))=0,"Eligible","Not Eligible")

Rhe aggregate functions only operate on **fields** (not
controls) in a form/report's record source table/query.

Are you sure you want to count all the field1s that a true?
It seems like you rally want to use:

=IIF(Abs(ChkBox1) + Abs(ChkBox2) +...+ Abs(ChkBox7)) =
0,"Patient Is Eligible","Patient Is Not Eligible")

 

[Steve]

My mistake!

Remove the Sum function and just add the absolute values of the checkboxes
together:
=IIF(Abs(ChkBox1) + Abs(ChkBox2) ..... Abs(ChkBox7) = 0,"Patient Is
Eligible","Patient Is Not Eligible")

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

=IIF(Sum(Abs(ChkBox1) + Abs(ChkBox2) ..... Abs(ChkBox7)) = 0,"Patient Is
Eligible","Patient Is Not Eligible")

I've tried this and many permutations, but the text box keeps
returning "#Error" I'm definitely referring to correct names for the
check box controls, too. For instance, just to keep it simple, this
abbreviated version returns "#Error."

=IIf(Sum(Abs([ChkBox1])+Abs([ChkBox2]))=0,"Eligible","Not Eligible")

Any ideas?

When a checkbox is checked it has a value of -1 and unchecked 0. The
absolute value of -1 is 1 so if any checkbox is checked the sum of the
checkboxes is greater than 0. You can use this fact to accomplish your
goal.
Put the following expression in the control source of the textbox where
you
currently display whether the patient is eligible or not:
=IIF(Sum(Abs(ChkBox1) + Abs(ChkBox2) ..... Abs(ChkBox7)) = 0,"Patient Is
Eligible","Patient Is Not Eligible")

The textbox will automatically display "Patient Is Eligible" when no
checkboxes are checked and automatically display "Patient Is Not
Eligible"
when any checkbox is ckecked.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)













- Show quoted text -