validating yes/no fields on a form

  • Thread starter Thread starter Will Sellers
  • Start date Start date
W

Will Sellers

I have a survey form that displays 14 records in a sub form each record has
2 yes/no fields. I need to make sure that there is at least 1 yes/no for
each of the 14 records such that the total count equals 14. If it does not
then I need to tell the user that their answers are incomplete. If the test
results equals 14 then I accept the data from the subform.
Any suggestions on how to do this will be appreciated.
 
Will,

I think this will work...
In the Form Footer, put an unbound textbox, let's say you name it
Validator, with its Control Source property set to:
=Sum(([Your1stYesNo]+[Your2ndYesNo])=0)
.... and then, on the applicable form event, e.g. Unload event, or Click
event of a button that is used to complete the survey, or whatever, you
can check the value of this textbox. For example...
Private Sub Form_Unload (Cancel As Integer)
If Me.Validator = 0 Then
' proceed
Else
MsgBox "At least one tick needed in each row", , "Incomplete"
Cancel = True
End If
 
OK I will try this. But since I need to test for the presence of 14
yes(checked)
should I change the validator value to = 14?

Steve Schapel said:
Will,

I think this will work...
In the Form Footer, put an unbound textbox, let's say you name it
Validator, with its Control Source property set to:
=Sum(([Your1stYesNo]+[Your2ndYesNo])=0)
... and then, on the applicable form event, e.g. Unload event, or Click
event of a button that is used to complete the survey, or whatever, you
can check the value of this textbox. For example...
Private Sub Form_Unload (Cancel As Integer)
If Me.Validator = 0 Then
' proceed
Else
MsgBox "At least one tick needed in each row", , "Incomplete"
Cancel = True
End If

--
Steve Schapel, Microsoft Access MVP


Will said:
I have a survey form that displays 14 records in a sub form each record has
2 yes/no fields. I need to make sure that there is at least 1 yes/no for
each of the 14 records such that the total count equals 14. If it does not
then I need to tell the user that their answers are incomplete. If the test
results equals 14 then I accept the data from the subform.
Any suggestions on how to do this will be appreciated.
Click to expand...
Click to expand...
 
Reading your requirements carefully, I think that you need to do a bit
more than Steve indicates. I take it that either _or_ _both_ of the
tick boxes for each record may be ticked. Just summing the ticks on
the subform won't work, because two ticks for one record would
"compensate" for no ticks in another. Bear in mind also that True, in
a numeric context, evaluates to -1.

I would do it like this:

For each record on your subform, I would create a new, calculated,
Control, defined as = - OR(Rec1Box1,Rec1Box2), where "Rec1" becomes
"Rec2" for the next record, etc. These can be made invisible. The
value of this Control for each _pair_ of Y/N fields will be 1 if
either or both of them is True. In your footer (or elsewhere on the
form), you would have an addtional calculated field, defined as the
sum of all the new, invisible, line by line, Controls. Check the value
of this in the Form_BeforeUpdate Event and abort the update if its
value is not 14.


I have a survey form that displays 14 records in a sub form each record has
2 yes/no fields. I need to make sure that there is at least 1 yes/no for
each of the 14 records such that the total count equals 14. If it does not
then I need to tell the user that their answers are incomplete. If the test
results equals 14 then I accept the data from the subform.
Any suggestions on how to do this will be appreciated.
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
No, Steve is right, because he is using the fact that False, in a
numeric context, evaluates to 0. It's not totally clear, because of
where the line break came, but you need one of the
(([Your1stYesNo]+[Your2ndYesNo])=0) terms for each record. This term
will be True (-1) if _neither_ of the pair of fields is True, and
False (0) otherwise, so the sum of all the terms will be zero if at
least one of each pair of boxes is ticked and nonzero otherwise.
I split the task up into two halves and reversed the test, but Steve's
and my solutions are logically equivalent. I hadn't read his post
carefully enough before I wrote mine.

OK I will try this. But since I need to test for the presence of 14
yes(checked)
should I change the validator value to = 14?

Steve Schapel said:
Will,

I think this will work...
In the Form Footer, put an unbound textbox, let's say you name it
Validator, with its Control Source property set to:
=Sum(([Your1stYesNo]+[Your2ndYesNo])=0)
... and then, on the applicable form event, e.g. Unload event, or Click
event of a button that is used to complete the survey, or whatever, you
can check the value of this textbox. For example...
Private Sub Form_Unload (Cancel As Integer)
If Me.Validator = 0 Then
' proceed
Else
MsgBox "At least one tick needed in each row", , "Incomplete"
Cancel = True
End If

--
Steve Schapel, Microsoft Access MVP


Will said:
I have a survey form that displays 14 records in a sub form each record has
2 yes/no fields. I need to make sure that there is at least 1 yes/no for
each of the 14 records such that the total count equals 14. If it does not
then I need to tell the user that their answers are incomplete. If the test
results equals 14 then I accept the data from the subform.
Any suggestions on how to do this will be appreciated.
Click to expand...
Click to expand...
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Steve's solution did exactly what I wanted but I cannot get the event
procedure to work.
I setup the validator control in the footer of the subform.
I can see the calculation working (great)
When I exit the last field in the parent form I use the "on exit" event to
test
the validator (Steve's code).
Problem : I get a runtime error 2766 object doesn't contain automation
object 'your1styesno'
It doesn't like the me.validator.
 
Will,

If you are running the code from an event on the main form, and the
Validator control is on the subform, then Me.Validator won't be
recognised because it is not on the main form. Sorry, I failed to
notice that you were using a subform. Try it like this...
Me.NameOfYourSubformControl.Form!Validator

Also, just to clarify that 'Your1stYesNo' etc are supposed to be
replaced in your expression with the actual names of your checkboxes.

I personally would probably not use the Exit event of a control on the
main form for this. The Exit event of the subform control itself may be
appropriate?
 
Back
Top