Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / June 2007

Tip: Looking for answers? Try searching our database.

AfterUpdate code to validate check boxes

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
HeislerKurt@gmail.com - 10 Jun 2007 20:06 GMT
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 - 10 Jun 2007 20:33 GMT
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)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> 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
[quoted text clipped - 29 lines]
>
> Kurt
Steve - 10 Jun 2007 20:51 GMT
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
resource@pcdatasheet.com

> 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
[quoted text clipped - 29 lines]
>
> Kurt
HeislerKurt@gmail.com - 10 Jun 2007 21:32 GMT
> =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
[quoted text clipped - 52 lines]
>
> - Show quoted text -
Marshall Barton - 10 Jun 2007 22:03 GMT
>> =IIF(Sum(Abs(ChkBox1) + Abs(ChkBox2) ..... Abs(ChkBox7)) = 0,"Patient Is
>> Eligible","Patient Is Not Eligible")
[quoted text clipped - 5 lines]
>
>=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")

Signature

Marsh
MVP [MS Access]

Steve - 10 Jun 2007 22:14 GMT
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
resource@pcdatasheet.com

>> =IIF(Sum(Abs(ChkBox1) + Abs(ChkBox2) ..... Abs(ChkBox7)) = 0,"Patient Is
>> Eligible","Patient Is Not Eligible")
[quoted text clipped - 67 lines]
>>
>> - Show quoted text -
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.