option group validation rule

[Brigitte P]

I'm developing a testing dbase in A XP for our staff. There are 12 tests
with each test having between 4 and 17 questions. Since the number of
quesitons is uneven, I cannot set the required property on table level. So I
thought I would use validation rules on field level. However, the tests are
set up with option groups, which according to Access help does not accept
these rules. I tried to set <>0 on the option group level, but it doesn't
work. How can I ensure that the test taker selects one option for each
question (they are a, b, c, d, e ..... or True False).
Thanks for any help.
Brigitte P.

 

[John Vinson]

I'm developing a testing dbase in A XP for our staff. There are 12 tests
with each test having between 4 and 17 questions. Since the number of
quesitons is uneven, I cannot set the required property on table level. So I
thought I would use validation rules on field level. However, the tests are
set up with option groups, which according to Access help does not accept
these rules. I tried to set <>0 on the option group level, but it doesn't
work. How can I ensure that the test taker selects one option for each
question (they are a, b, c, d, e ..... or True False).
Thanks for any help.
Brigitte P.

You're confusing levels!

Access Tables do NOT contain option groups. An Option Group is not a
type of data; it's a display tool. What's stored in the table is an
Integer or a Long Integer. This field can be displayed on a Form any
way you like - in a Textbox, an Option Group, a Combo Box or whatever.

Take a look at a quite different way of storing questionnaire type
data:

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'


John W. Vinson[MVP]

 

[Brigitte P]

Thanks. I think I didn't express myself right. I looked at the
rogersaccesslibray and got some good ideas in the past from this site. But
our users don't want to look up, they want to click a checkbox, so the
option group seemed to be the solution and it works great (I've done already
6 tests that automatically display the results and several reports or staff
supervisors).
My data is stored as a long integer in the table, and I understood that the
option group is display only, but it gives mutually exclusive categories. I
probably don't fully understand how an option group does that, but all I
need is to force the user to select one option for each question (either a,
b, c - which are the lables attached to the option field, or True False),
that is, don't let them leave the form without having all questions
completed.
I did this originally on table level by setting required to yes, but then
tests showed up with less questions which ruins what I had planned.
Therefore, I tried to set the validation rule for each quesiton and that
doesn't work for option groups and also not for individual checkboxes
(because they would lose the mutual exclusive capability). I tried If
[optiongroupxx] is Null Then MsgBox ..... but this doesn't work either
because the display is obviously not Null. So the question: Is there a way I
can force the user to complete all quesitons by selecting an answer from
each option group?
Thanks for your patience with this.
Brigitte P.

 

[John Vinson]

Thanks. I think I didn't express myself right. I looked at the
rogersaccesslibray and got some good ideas in the past from this site. But
our users don't want to look up, they want to click a checkbox, so the
option group seemed to be the solution and it works great (I've done already
6 tests that automatically display the results and several reports or staff
supervisors).
ok...

My data is stored as a long integer in the table, and I understood that the
option group is display only, but it gives mutually exclusive categories. I
probably don't fully understand how an option group does that,

Well, it has a different number assigned to each checkbox (or other
control, buttons for example) in it. Whichever checkbox the user
selects, that number is stored in the single numeric field. Since a
numeric field can only store one number, only one box can be checked.
The C# code (or whatever it is) behind the action of the control is
sort of irrelevant!

but all I
need is to force the user to select one option for each question (either a,
b, c - which are the lables attached to the option field, or True False),
that is, don't let them leave the form without having all questions
completed.

I did this originally on table level by setting required to yes, but then
tests showed up with less questions which ruins what I had planned.

SO you are still using one FIELD per question... and paying the price
(in complexity of the programming you must do) for using
non-normalized data structures.

Therefore, I tried to set the validation rule for each quesiton and that
doesn't work for option groups and also not for individual checkboxes
(because they would lose the mutual exclusive capability). I tried If
[optiongroupxx] is Null Then MsgBox ..... but this doesn't work either
because the display is obviously not Null. So the question: Is there a way I
can force the user to complete all quesitons by selecting an answer from
each option group?

What you'll need to do is put code in the *FORM'S* BeforeUpdate event:
e.g.

Private Sub Form_BeforeUpdate(Cancel as Integer)
If IsNull(Me!optQuestion1) _
Or IsNull(Me!optQuestion2) _
Or IsNull(Me!optQuestion3) _
<etc>
Then
MsgBox "Please fill out all questions"
Cancel = True
End If

Just be sure that none of the Option Groups have a Default property
set, and that the table fields to which they are bound likewise have
the (annoying!!!) "default default" of 0 removed.

John W. Vinson[MVP]

 

[Brigitte P]

Thanks John, I'll just have to do this the long way; the OR is what didn't
come to my mind. Maybe I don't understand normalization as well as I thought
I did. I used the model from test given by publishers to faculty for use in
their classes. The tests we are having are a wild mix of questions and
answer options. There are 13 different tests, and each test has anything
from 4 - 17 questions that have either a multiple choice (anything from abc
through a....j (which makes possibly 10 options) or a T/F option as an
answer, in short anything from 2 through 10 options. This gives me possibly
about 150 questions that have endless possibilities for answers.
I didn't design the tests (and I think this is poor test design), but I have
to accept what our nursing management had created. I first thought one
table per questionnair which seemed clumsy but in hindsight may have fixed
my problem. So I created one table with staff ID and test number and a Test
ID (unique Autonumber), Test Date and time, and then Q1 - 17 (17 being the
maximum). All questionnairs are saved to the one table, with 5 - 17 left
blank if the test has only 4 questions. Obviously, test demographics and
staff demographics come from linked tables once I get ready for reporting.
I score each test via an individual query because most tests have different
pass limits, usually around 80% - using simple IIf statements in the query.
Then I have a Union query that collects all scores and is the basis for a
query that runs the result report which may look like this:
Staff Name, Job Description, Ward (in the name header)
Detail section:
Test Date Test Name Score Pass/Fail
Recertification Due Date (usually one year from the last pass date)
1/1/05 Fire Drill 6 Fail
1/3/05 Fire Drill 9 Pass
1/3/06
1/2/05 Pain Management 8 Pass 1/2/06
2/5/05 IV Test 3 Fail
2/7/05 IV Test 6 Pass
2/7/06

This allows each user to check their scores immediately and repeat the test
if needed until they pass I also pull reports for their supervisors that
show the test history and recertification dates for their individual staff.
Anyway, if it's not too complex, how could I have normalized this test to
accomodate all these endless possibilities. As I said, I used the model from
textbook publishers.
Thanks for further input; I know I have a lot to learn (and I do this only
on an as needed basis and not as my main job).
Brigitte

Thanks. I think I didn't express myself right. I looked at the
rogersaccesslibray and got some good ideas in the past from this site. But
our users don't want to look up, they want to click a checkbox, so the
option group seemed to be the solution and it works great (I've done already
6 tests that automatically display the results and several reports or staff
supervisors).
ok...

My data is stored as a long integer in the table, and I understood that the
option group is display only, but it gives mutually exclusive categories. I
probably don't fully understand how an option group does that,

Well, it has a different number assigned to each checkbox (or other
control, buttons for example) in it. Whichever checkbox the user
selects, that number is stored in the single numeric field. Since a
numeric field can only store one number, only one box can be checked.
The C# code (or whatever it is) behind the action of the control is
sort of irrelevant!

but all I
need is to force the user to select one option for each question (either a,
b, c - which are the lables attached to the option field, or True False),
that is, don't let them leave the form without having all questions
completed.

I did this originally on table level by setting required to yes, but then
tests showed up with less questions which ruins what I had planned.

SO you are still using one FIELD per question... and paying the price
(in complexity of the programming you must do) for using
non-normalized data structures.

Therefore, I tried to set the validation rule for each quesiton and that
doesn't work for option groups and also not for individual checkboxes
(because they would lose the mutual exclusive capability). I tried If
[optiongroupxx] is Null Then MsgBox ..... but this doesn't work either
because the display is obviously not Null. So the question: Is there a way I
can force the user to complete all quesitons by selecting an answer from
each option group?

What you'll need to do is put code in the *FORM'S* BeforeUpdate event:
e.g.

Private Sub Form_BeforeUpdate(Cancel as Integer)
If IsNull(Me!optQuestion1) _
Or IsNull(Me!optQuestion2) _
Or IsNull(Me!optQuestion3) _
<etc>
Then
MsgBox "Please fill out all questions"
Cancel = True
End If

Just be sure that none of the Option Groups have a Default property
set, and that the table fields to which they are bound likewise have
the (annoying!!!) "default default" of 0 removed.

John W. Vinson[MVP]

 

[Brigitte P]

It was a bit easier than I thought it would be. I simply put in the
AfterUpdate property: If IsNull(Me!Q1) then MsgBox"Complete Question 1"
Cancel = True End If. Same goes for all questions through 17.
Since all questions for all tests write to the same table, they all have the
same name, thus I just copy the code from one form to the next and delete or
add lines as needed for each individual test form. It works out great.
My dbase is probably still not normalized like it should be, and I would
like to learn more about it, but my immediate problem is solved. I would
like to do a better job with normalization in my next project, but I just
don't know how this could have been done differently in this dbase.
I'm glad to learn, and I always get my brain jumpstarted by the answers from
nice people like you and others in the user groups. THANK YOU!
Brigitte

Thanks John, I'll just have to do this the long way; the OR is what didn't
come to my mind. Maybe I don't understand normalization as well as I thought
I did. I used the model from test given by publishers to faculty for use in
their classes. The tests we are having are a wild mix of questions and
answer options. There are 13 different tests, and each test has anything
from 4 - 17 questions that have either a multiple choice (anything from abc
through a....j (which makes possibly 10 options) or a T/F option as an
answer, in short anything from 2 through 10 options. This gives me possibly
about 150 questions that have endless possibilities for answers.
I didn't design the tests (and I think this is poor test design), but I have
to accept what our nursing management had created. I first thought one
table per questionnair which seemed clumsy but in hindsight may have fixed
my problem. So I created one table with staff ID and test number and a Test
ID (unique Autonumber), Test Date and time, and then Q1 - 17 (17 being the
maximum). All questionnairs are saved to the one table, with 5 - 17 left
blank if the test has only 4 questions. Obviously, test demographics and
staff demographics come from linked tables once I get ready for reporting.
I score each test via an individual query because most tests have different
pass limits, usually around 80% - using simple IIf statements in the query.
Then I have a Union query that collects all scores and is the basis for a
query that runs the result report which may look like this:
Staff Name, Job Description, Ward (in the name header)
Detail section:
Test Date Test Name Score Pass/Fail
Recertification Due Date (usually one year from the last pass date)
1/1/05 Fire Drill 6 Fail
1/3/05 Fire Drill 9 Pass
1/3/06
1/2/05 Pain Management 8 Pass 1/2/06
2/5/05 IV Test 3 Fail
2/7/05 IV Test 6 Pass
2/7/06

This allows each user to check their scores immediately and repeat the test
if needed until they pass I also pull reports for their supervisors that
show the test history and recertification dates for their individual staff.
Anyway, if it's not too complex, how could I have normalized this test to
accomodate all these endless possibilities. As I said, I used the model from
textbook publishers.
Thanks for further input; I know I have a lot to learn (and I do this only
on an as needed basis and not as my main job).
Brigitte


categories.

I

Well, it has a different number assigned to each checkbox (or other
control, buttons for example) in it. Whichever checkbox the user
selects, that number is stored in the single numeric field. Since a
numeric field can only store one number, only one box can be checked.
The C# code (or whatever it is) behind the action of the control is
sort of irrelevant!

(either

a,

b, c - which are the lables attached to the option field, or True False),
that is, don't let them leave the form without having all questions
completed.

I did this originally on table level by setting required to yes, but then
tests showed up with less questions which ruins what I had planned.

SO you are still using one FIELD per question... and paying the price
(in complexity of the programming you must do) for using
non-normalized data structures.

Therefore, I tried to set the validation rule for each quesiton and that
doesn't work for option groups and also not for individual checkboxes
(because they would lose the mutual exclusive capability). I tried If
[optiongroupxx] is Null Then MsgBox ..... but this doesn't work either
because the display is obviously not Null. So the question: Is there a way I
can force the user to complete all quesitons by selecting an answer from
each option group?

What you'll need to do is put code in the *FORM'S* BeforeUpdate event:
e.g.

Private Sub Form_BeforeUpdate(Cancel as Integer)
If IsNull(Me!optQuestion1) _
Or IsNull(Me!optQuestion2) _
Or IsNull(Me!optQuestion3) _
<etc>
Then
MsgBox "Please fill out all questions"
Cancel = True
End If

Just be sure that none of the Option Groups have a Default property
set, and that the table fields to which they are bound likewise have
the (annoying!!!) "default default" of 0 removed.

John W. Vinson[MVP]

 

[John Vinson]

I'm glad to learn, and I always get my brain jumpstarted by the answers from
nice people like you and others in the user groups. THANK YOU!

Thank YOU, Brigitte! Glad you got it working. And yes, we'd be glad to
help with the initial design of your next database.


John W. Vinson[MVP]