If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
validating yes/no fields on a form
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. |
#2
|
|||
|
|||
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 Sellers wrote: 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. |
#3
|
|||
|
|||
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" wrote in message ... 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 Sellers wrote: 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. |
#4
|
|||
|
|||
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. On Sat, 6 Nov 2004 02:35:57 -0500, "Will Sellers" wrote: 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. Please respond to the Newsgroup, so that others may benefit from the exchange. Peter R. Fletcher |
#5
|
|||
|
|||
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. On Sat, 6 Nov 2004 06:49:33 -0500, "Will Sellers" wrote: 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" wrote in message ... 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 Sellers wrote: 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. Please respond to the Newsgroup, so that others may benefit from the exchange. Peter R. Fletcher |
#6
|
|||
|
|||
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. "Peter R. Fletcher" pfletch(at)fletchers(hyphen)uk.com wrote in message news 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. On Sat, 6 Nov 2004 02:35:57 -0500, "Will Sellers" wrote: 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. Please respond to the Newsgroup, so that others may benefit from the exchange. Peter R. Fletcher |
#7
|
|||
|
|||
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? -- Steve Schapel, Microsoft Access MVP Will Sellers wrote: 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
"SELECT" | REVBJONES | Using Forms | 18 | August 30th, 2004 04:47 AM |
Text form fields | Jerry | General Discussion | 5 | August 24th, 2004 10:43 PM |
Fields from multiple Tables on 1 Form | Alan Armitage | Using Forms | 2 | July 15th, 2004 11:13 AM |
how to combine fields on a form in a query as criteria? | Henro | New Users | 3 | June 8th, 2004 03:50 PM |
Can merge fields be entered into text form fields? | Walt | Mailmerge | 5 | May 18th, 2004 10:16 PM |