Validation of multiple check boxes

D

dsc2bjn

I have a form with 9 check boxes. Each check box represents a data element
within the same table. Only 1 of the check boxes must be checked in order
for the record to be accepted into the data table.

I have written a validation which works for other required values; however,
when the same logic is applied to the check box nothing happens. The message
box does not appear. If I change the "0" value (which should represent an
unchecked box) to 1, the validation works fine (all check boxes are checked).


What is wrong with this:

On Error GoTo Err_Command44_Click
If (Eval("[Forms]![SAR Data Entry]![Confirmation]=0")) And _
(Eval("[Forms]![SAR Data Entry]![DOCUMENT ANALYSIS]=0")) And _
(Eval("[Forms]![SAR Data Entry]![Interview]=0")) And _
(Eval("[Forms]![SAR Data Entry]![Observation]=0")) And _
(Eval("[Forms]![SAR Data Entry]![Physical Examination]=0")) And _
(Eval("[Forms]![SAR Data Entry]![QUESTIONNAIRE]=0")) And _
(Eval("[Forms]![SAR Data Entry]![Sampling]=0")) And _
(Eval("[Forms]![SAR Data Entry]![TRANSACTION TESTING]=0")) And _
(Eval("[Forms]![SAR Data Entry]![TO BE DETERMINED]=0")) Then
MsgBox "A Value for 'Testing Methods' is Required.", vbOKOnly, "Message
title"
End If
If (Eval("[Forms]![SAR Data Entry]![AU] Is Null")) Then
MsgBox "A Value for 'AU' is Required.", vbOKOnly, "Message title"
End If
If (Eval("[Forms]![SAR Data Entry]![SAR Question] Is Null")) Then
MsgBox "A Value for 'SAR Question' is Required.", vbOKOnly, "Message
title"
End If
If (Eval("[Forms]![SAR Data Entry]![Weakness] Is Null")) Then
MsgBox "A Value for 'Weakness' is Required.", vbOKOnly, "Message title"
Else
DoCmd.Close
End If
Exit_Command44_Click:
Exit Sub

Err_Command44_Click:
MsgBox Err.Description
Resume Exit_Command44_Click

End Sub
 
D

Douglas J. Steele

There should be no need to use the Eval function.

To check that only one check box is checked, you can use something like:

If Abs([Forms]![SAR Data Entry]![Confirmation] + _
[Forms]![SAR Data Entry]![DOCUMENT ANALYSIS] + _
[Forms]![SAR Data Entry]![Interview] + _
[Forms]![SAR Data Entry]![Observation] + _
[Forms]![SAR Data Entry]![Physical Examination] + _
[Forms]![SAR Data Entry]![QUESTIONNAIRE] + _
[Forms]![SAR Data Entry]![Sampling]=0 +_
[Forms]![SAR Data Entry]![TRANSACTION TESTING] + _
[Forms]![SAR Data Entry]![TO BE DETERMINED]) <> 1 Then
MsgBox "You haven't checked only 1 checkbox.", vbOKOnly, "Message
title"
End If

This takes advantage of the fact that True is -1 and False is 0 in Access.
If the total was 0, that would mean that no check boxes were checked. If the
total was > 1, that would mean that more than 1 checkbox was checked.
 
D

dsc2bjn

I am looking to ensure AT LEAST one box is checked. The user could check all
9 boxes. That is the reason for evaluating the "group" was to make sure all
the check boxes were not blank.

Douglas J. Steele said:
There should be no need to use the Eval function.

To check that only one check box is checked, you can use something like:

If Abs([Forms]![SAR Data Entry]![Confirmation] + _
[Forms]![SAR Data Entry]![DOCUMENT ANALYSIS] + _
[Forms]![SAR Data Entry]![Interview] + _
[Forms]![SAR Data Entry]![Observation] + _
[Forms]![SAR Data Entry]![Physical Examination] + _
[Forms]![SAR Data Entry]![QUESTIONNAIRE] + _
[Forms]![SAR Data Entry]![Sampling]=0 +_
[Forms]![SAR Data Entry]![TRANSACTION TESTING] + _
[Forms]![SAR Data Entry]![TO BE DETERMINED]) <> 1 Then
MsgBox "You haven't checked only 1 checkbox.", vbOKOnly, "Message
title"
End If

This takes advantage of the fact that True is -1 and False is 0 in Access.
If the total was 0, that would mean that no check boxes were checked. If the
total was > 1, that would mean that more than 1 checkbox was checked.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


dsc2bjn said:
I have a form with 9 check boxes. Each check box represents a data element
within the same table. Only 1 of the check boxes must be checked in order
for the record to be accepted into the data table.

I have written a validation which works for other required values;
however,
when the same logic is applied to the check box nothing happens. The
message
box does not appear. If I change the "0" value (which should represent an
unchecked box) to 1, the validation works fine (all check boxes are
checked).


What is wrong with this:

On Error GoTo Err_Command44_Click
If (Eval("[Forms]![SAR Data Entry]![Confirmation]=0")) And _
(Eval("[Forms]![SAR Data Entry]![DOCUMENT ANALYSIS]=0")) And _
(Eval("[Forms]![SAR Data Entry]![Interview]=0")) And _
(Eval("[Forms]![SAR Data Entry]![Observation]=0")) And _
(Eval("[Forms]![SAR Data Entry]![Physical Examination]=0")) And _
(Eval("[Forms]![SAR Data Entry]![QUESTIONNAIRE]=0")) And _
(Eval("[Forms]![SAR Data Entry]![Sampling]=0")) And _
(Eval("[Forms]![SAR Data Entry]![TRANSACTION TESTING]=0")) And _
(Eval("[Forms]![SAR Data Entry]![TO BE DETERMINED]=0")) Then
MsgBox "A Value for 'Testing Methods' is Required.", vbOKOnly,
"Message
title"
End If
If (Eval("[Forms]![SAR Data Entry]![AU] Is Null")) Then
MsgBox "A Value for 'AU' is Required.", vbOKOnly, "Message title"
End If
If (Eval("[Forms]![SAR Data Entry]![SAR Question] Is Null")) Then
MsgBox "A Value for 'SAR Question' is Required.", vbOKOnly, "Message
title"
End If
If (Eval("[Forms]![SAR Data Entry]![Weakness] Is Null")) Then
MsgBox "A Value for 'Weakness' is Required.", vbOKOnly, "Message
title"
Else
DoCmd.Close
End If
Exit_Command44_Click:
Exit Sub

Err_Command44_Click:
MsgBox Err.Description
Resume Exit_Command44_Click

End Sub
Click to expand...
Click to expand...
 
G

Graham Mandeno

Notice that Doug said:
If the total was 0, that would mean that no check boxes were checked. If
the
total was > 1, that would mean that more than 1 checkbox was checked.
Click to expand...

So, instead of testing for a total that is not exactly 1, you should test
for a total of zero.

Change <> 1 to = 0
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

dsc2bjn said:
I am looking to ensure AT LEAST one box is checked. The user could check
all
9 boxes. That is the reason for evaluating the "group" was to make sure
all
the check boxes were not blank.

Douglas J. Steele said:
There should be no need to use the Eval function.

To check that only one check box is checked, you can use something like:

If Abs([Forms]![SAR Data Entry]![Confirmation] + _
[Forms]![SAR Data Entry]![DOCUMENT ANALYSIS] + _
[Forms]![SAR Data Entry]![Interview] + _
[Forms]![SAR Data Entry]![Observation] + _
[Forms]![SAR Data Entry]![Physical Examination] + _
[Forms]![SAR Data Entry]![QUESTIONNAIRE] + _
[Forms]![SAR Data Entry]![Sampling]=0 +_
[Forms]![SAR Data Entry]![TRANSACTION TESTING] + _
[Forms]![SAR Data Entry]![TO BE DETERMINED]) <> 1 Then
MsgBox "You haven't checked only 1 checkbox.", vbOKOnly, "Message
title"
End If

This takes advantage of the fact that True is -1 and False is 0 in
Access.
If the total was 0, that would mean that no check boxes were checked. If
the
total was > 1, that would mean that more than 1 checkbox was checked.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


dsc2bjn said:
I have a form with 9 check boxes. Each check box represents a data
element
within the same table. Only 1 of the check boxes must be checked in
order
for the record to be accepted into the data table.

I have written a validation which works for other required values;
however,
when the same logic is applied to the check box nothing happens. The
message
box does not appear. If I change the "0" value (which should represent
an
unchecked box) to 1, the validation works fine (all check boxes are
checked).


What is wrong with this:

On Error GoTo Err_Command44_Click
If (Eval("[Forms]![SAR Data Entry]![Confirmation]=0")) And _
(Eval("[Forms]![SAR Data Entry]![DOCUMENT ANALYSIS]=0")) And _
(Eval("[Forms]![SAR Data Entry]![Interview]=0")) And _
(Eval("[Forms]![SAR Data Entry]![Observation]=0")) And _
(Eval("[Forms]![SAR Data Entry]![Physical Examination]=0")) And _
(Eval("[Forms]![SAR Data Entry]![QUESTIONNAIRE]=0")) And _
(Eval("[Forms]![SAR Data Entry]![Sampling]=0")) And _
(Eval("[Forms]![SAR Data Entry]![TRANSACTION TESTING]=0")) And _
(Eval("[Forms]![SAR Data Entry]![TO BE DETERMINED]=0")) Then
MsgBox "A Value for 'Testing Methods' is Required.", vbOKOnly,
"Message
title"
End If
If (Eval("[Forms]![SAR Data Entry]![AU] Is Null")) Then
MsgBox "A Value for 'AU' is Required.", vbOKOnly, "Message title"
End If
If (Eval("[Forms]![SAR Data Entry]![SAR Question] Is Null")) Then
MsgBox "A Value for 'SAR Question' is Required.", vbOKOnly,
"Message
title"
End If
If (Eval("[Forms]![SAR Data Entry]![Weakness] Is Null")) Then
MsgBox "A Value for 'Weakness' is Required.", vbOKOnly, "Message
title"
Else
DoCmd.Close
End If
Exit_Command44_Click:
Exit Sub

Err_Command44_Click:
MsgBox Err.Description
Resume Exit_Command44_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
D

dsc2bjn

Thanks!!

That works well.

Graham Mandeno said:
Notice that Doug said:
If the total was 0, that would mean that no check boxes were checked. If
the
total was > 1, that would mean that more than 1 checkbox was checked.
Click to expand...

So, instead of testing for a total that is not exactly 1, you should test
for a total of zero.

Change <> 1 to = 0
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

dsc2bjn said:
I am looking to ensure AT LEAST one box is checked. The user could check
all
9 boxes. That is the reason for evaluating the "group" was to make sure
all
the check boxes were not blank.

Douglas J. Steele said:
There should be no need to use the Eval function.

To check that only one check box is checked, you can use something like:

If Abs([Forms]![SAR Data Entry]![Confirmation] + _
[Forms]![SAR Data Entry]![DOCUMENT ANALYSIS] + _
[Forms]![SAR Data Entry]![Interview] + _
[Forms]![SAR Data Entry]![Observation] + _
[Forms]![SAR Data Entry]![Physical Examination] + _
[Forms]![SAR Data Entry]![QUESTIONNAIRE] + _
[Forms]![SAR Data Entry]![Sampling]=0 +_
[Forms]![SAR Data Entry]![TRANSACTION TESTING] + _
[Forms]![SAR Data Entry]![TO BE DETERMINED]) <> 1 Then
MsgBox "You haven't checked only 1 checkbox.", vbOKOnly, "Message
title"
End If

This takes advantage of the fact that True is -1 and False is 0 in
Access.
If the total was 0, that would mean that no check boxes were checked. If
the
total was > 1, that would mean that more than 1 checkbox was checked.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a form with 9 check boxes. Each check box represents a data
element
within the same table. Only 1 of the check boxes must be checked in
order
for the record to be accepted into the data table.

I have written a validation which works for other required values;
however,
when the same logic is applied to the check box nothing happens. The
message
box does not appear. If I change the "0" value (which should represent
an
unchecked box) to 1, the validation works fine (all check boxes are
checked).


What is wrong with this:

On Error GoTo Err_Command44_Click
If (Eval("[Forms]![SAR Data Entry]![Confirmation]=0")) And _
(Eval("[Forms]![SAR Data Entry]![DOCUMENT ANALYSIS]=0")) And _
(Eval("[Forms]![SAR Data Entry]![Interview]=0")) And _
(Eval("[Forms]![SAR Data Entry]![Observation]=0")) And _
(Eval("[Forms]![SAR Data Entry]![Physical Examination]=0")) And _
(Eval("[Forms]![SAR Data Entry]![QUESTIONNAIRE]=0")) And _
(Eval("[Forms]![SAR Data Entry]![Sampling]=0")) And _
(Eval("[Forms]![SAR Data Entry]![TRANSACTION TESTING]=0")) And _
(Eval("[Forms]![SAR Data Entry]![TO BE DETERMINED]=0")) Then
MsgBox "A Value for 'Testing Methods' is Required.", vbOKOnly,
"Message
title"
End If
If (Eval("[Forms]![SAR Data Entry]![AU] Is Null")) Then
MsgBox "A Value for 'AU' is Required.", vbOKOnly, "Message title"
End If
If (Eval("[Forms]![SAR Data Entry]![SAR Question] Is Null")) Then
MsgBox "A Value for 'SAR Question' is Required.", vbOKOnly,
"Message
title"
End If
If (Eval("[Forms]![SAR Data Entry]![Weakness] Is Null")) Then
MsgBox "A Value for 'Weakness' is Required.", vbOKOnly, "Message
title"
Else
DoCmd.Close
End If
Exit_Command44_Click:
Exit Sub

Err_Command44_Click:
MsgBox Err.Description
Resume Exit_Command44_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
D

dsc2bjn

Ok...I found a glitch...well sort of...

Validation of the those fields works well, when they are the only fields
being evaluated; however, I have four required fields (the entire 9 check
boxes being counted as one field).

When none of the "required" fields are populated, the error message show for
the first 3 required fields, but the check box validation is skipped and the
form closes.

I assume this is because the check boxes are "inactive" (greyed out), until
a value is actually entered into another field (be it one of the required
fields or a non-required field).

Any suggestions would be appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Do not allow form to close on validation error 6
VBA works in orignal but fails in copy of database 1
Validation of subform 16
Word macro that can call cells out of Excel for a find and replace 0
VBA - exporting serial letters to single documents 0
Spell check of form and subform 1
Spell check form and subform 0
Eval a SubForm? 6

Top