Check Boxes

[Majic]

I have created a database and have 2 check boxes for Pass and Fail. I
have both Pass and Fail unchecked to reduce mistakes. I want to force
the user to have to manually check either boxes.
Please let me know how I can do this.

Thank you

 

[Jeff Boyce]

Rather than use two check boxes (because BOTH could be checked), use an
option group and radio buttons (only ONE of which may be "checked" at a
time).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I have created a database and have 2 check boxes for Pass and Fail. I
have both Pass and Fail unchecked to reduce mistakes. I want to force
the user to have to manually check either boxes.
Please let me know how I can do this.

Thank you

That's a violation of table normalization rules. The value of the Fail
checkbox can be derived from the value of the Pass checkbox (or vice versa) -
I'd presume that it would be simply *wrong* to have a record where Pass and
Fail are either both True or both False.

I'd suggest instead using a yes/no field Pass, and make it Required. The user
will then need to either have it checked or unchecked.

John W. Vinson [MVP]

 

[Allen Browne]

Two Access experts have already explained why you must redesign the table.
Can I add a suggestion on how to do it?

The core issue is that you have 3 possible states you need to handle:
- Pass
- Fail
- Not yet known
The reason you wanted to have a check boxes was to handle the 3rd state
(i.e. to force the user to choose pass or fail), and then you ran into
trouble with the 4th state (both pass and fail) which would be nonsense.

The best solution would be to use a Number field instead of 2 yes/no fields:
a) It's one field (so the nonsense cannot arise.)
b) It can start out blank, and you can enforce the rule that the user must
choose.

1. In table design, create a field named (say) Competent.
Data type Number.

2. In the lower pane of table design, set these properties on the General
tab:
Field Size Integer
Default Value {delete the zero.}
Required No

3. On the Lookup tab, set:
Display Control Combo
Row Source Type Value List
Row Source -1;Pass;0;Fail
Bound Column 1
Column Count 2
Column Heads No
Column Widths 0

Save the table. You now have a record where the field starts out blank, and
the user can enter P or F, and the combo completes the rest for you. The 3
states are stored like this:
Pass -1 (the value for True, so competent.)
Fail 0 (the value for False, so not competent.)
left blank Null (the value for unknown.)
You can therefore query on this field like you could on a yes/no field, but
it handles the 3rd state which the yes/no field does not.

Now in your form, you want to warn the user if they left the field blank.
Use the BeforeUpdate event procedure of the *form* to do that:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg as String
If IsNull(Me.Competent) Then
strMsg = "You did not fill in the result." & vbCrLf & _
"Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Incomplete") <>
vbYes Then
Cancel = True
Me.Competent.SetFocus
End If
End If
End Sub

Just to reassure you, I use this kind of thing all the time for yes/no data.
Here's an article explaining why:
http://allenbrowne.com/NoYesNo.html

 

[Majic]

Two Access experts have already explained why you must redesign the table.
Can I add a suggestion on how to do it?

The core issue is that you have 3 possible states you need to handle:
- Pass
- Fail
- Not yet known
The reason you wanted to have a check boxes was to handle the 3rd state
(i.e. to force the user to choose pass or fail), and then you ran into
trouble with the 4th state (both pass and fail) which would be nonsense.

The best solution would be to use a Number field instead of 2 yes/no fields:
a) It's one field (so the nonsense cannot arise.)
b) It can start out blank, and you can enforce the rule that the user must
choose.

1. In table design, create a field named (say) Competent.
Data type Number.

2. In the lower pane of table design, set these properties on the General
tab:
Field Size Integer
Default Value {delete the zero.}
Required No

3. On the Lookup tab, set:
Display Control Combo
Row Source Type Value List
Row Source -1;Pass;0;Fail
Bound Column 1
Column Count 2
Column Heads No
Column Widths 0

Save the table. You now have a record where the field starts out blank, and
the user can enter P or F, and the combo completes the rest for you. The 3
states are stored like this:
Pass -1 (the value for True, so competent.)
Fail 0 (the value for False, so not competent.)
left blank Null (the value for unknown.)
You can therefore query on this field like you could on a yes/no field, but
it handles the 3rd state which the yes/no field does not.

Now in your form, you want to warn the user if they left the field blank.
Use the BeforeUpdate event procedure of the *form* to do that:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg as String
If IsNull(Me.Competent) Then
strMsg = "You did not fill in the result." & vbCrLf & _
"Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Incomplete") <>
vbYes Then
Cancel = True
Me.Competent.SetFocus
End If
End If
End Sub

Just to reassure you, I use this kind of thing all the time for yes/no data.
Here's an article explaining why:
http://allenbrowne.com/NoYesNo.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.







- Show quoted text -

Thank you so much I will try this.