Option groups - possible to have duplicate values?

[ahuvas]

Im stumped.

I have to create a form based on a questionnaire. I have to recreate the questionnaire exactly and cannot change anything.

One of the questions has 6 possible answers - however in order to score the questionnaire two of the possible answers are given the same value of 1. Dont ask me why but this is the way it has to be.


When I tried to create an option group it generates an error message cant have duplicate values. To get around this I assigned one of the possible answers a dummy value 9, and then changed it after the option group was created.

However when I select that answer although the correct value is placed in the table, the user can see the other option that has the same value is also selected (i.e. a dot appears in the circle).

Is there anyway to avoid the user seeing this?

 

[Uncle Gizmo]

You could try changing the value back to nine, and then use some code in the option groups after update property to allocate 1 to the table when this value is nine.

I guess you would need to separate the option group from the table field it is bound to currently.

The code you need to put in the after update event will be something like this:

Select Case = opgYourOptionGroup '(The whole group Name)

Case = 1
Me.YourTableFieldName = 1

Case = 2
Me.YourTableFieldName = 2

etc......

Case = 9
Me.YourTableFieldName = 1

End Select

This is what they term "Air Code" (off the top of my head), you may have to adjust it Slightly to make it work.

 

[ahuvas]

Thank you Uncle Gizmo I was thinking of doing something this.

 

[ahuvas]

Okay lets say my form is called cleveland Questionnaire and is bound to a table called participantdetails.

I have some option groups that needed to be recoded and the new value dropped into the table that the form is bound to into a variable called Q2Cleveland.. Lets call one of the option groups
Q2ClevelandRecode.


So do I left the control source empty for this group and then

put an expression in its after update event.

Private Sub Q2ClevelandRecode_AfterUpdate()

Select Case Q2ClevelandRecode

Case = 0
Me.Q2Cleveland.Value = 0

Case Is = 1
Me.Q2Cleveland.Value = 0

Case Is = 2
Me.Q2Cleveland.Value = 0

Case Is = 3
Me.Q2Cleveland.Value = 1

End Select

End Sub


Is this right? I tried it and it didnt work although I didnt get any errors either?

I think it probably has something to do with after update/ private sub thing.

Are you able to advise me? Also in general when we talk about me.something do we refer to the control name or the field name underneath it? I usually name them the same thing so Im not sure what would happen if they were different.

 

[ahuvas]

Actually I was just thinking about it now and if its unbound even if it does as it says it would i.e. backend conversion I wouldnt be able to see the results in the form later?

Do you think it would be better to create 2 fields for every question and use the statement to convert 1 to the other? The I could use the second set of fields to score the questionnaire?

 

[Brianwarnock]

Only the option group is unbound.
Not done this before but followed Uncle's post and table updated and update shows in form.

Brian

 

[ahuvas]

Are you able to post your practice version? I dont understand it the option group is unbound how it retains any values?

 

[ahuvas]

Actually it worked for me (I put the value 1 in the table) when I recreated the option group and selected "keep values for later use" however if I repoen the form it does not hold the chouce that the participant placed in there (because it is unbound)?

 

[Brianwarnock]

As the result of the choice is now stored in the table, and is displayed on the form, why would you need to show it in the option group?

Brian

 

[Uncle Gizmo]

You would need to reverse the previous code, design it as a function in your form module, and call that function from the on current event of your form.

Select Case = opgYourOptionGroup '(The whole group Name)

Case = 1
Me.YourTableFieldName = 1

Would change to something like:

private function fSetOptionGrp()

Select Case = Me.YourTableFieldName

Case = 1
opgYourOptionGroup = 1

repeat as before.

End Function

 

[ahuvas]

As the result of the choice is now stored in the table, and is displayed on the form, why would you need to show it in the option group?

Brian

Because nothing is displayed on the form? Once the value the participant originally selected in the option group is recoded and placed in the appropriate field in the table that is bound to the form, once you close it and reopen it it looks like the form was never filled out?

 

[Brianwarnock]

The form is bound to the table and has the appropriate field in it as well as the option group ?

Brian

 

[ahuvas]

Nope the option grou is the field? hehe we are at cross purposes

 

[Brianwarnock]

Guess so, I'll back out now.

Brian

 

[ahuvas]

I dont have a problem with you being involved (you have always been a big help to me!) Im just not sure we are thinking about the same thing. I have attached a pared down version of the database. Look at ClevelandQuestionnaire form to see what I mean.

For example question 2, although there are 4 possibly options all the answers in the option group are actually given a score of 0 except for the 4th one which is given a score of 1 when grading the questionnaire and so on...

I will then use a query to calculate a global score.

Also if the participants answered no to question 1 or 8 they will not be required to answer questions 2,3,4 or 9 respectively. I have written code that causes the option groups to disappear and write the value 99 in thsoe fields - 99 is recognised by some of the stats programs as N/A not "Failed to answer"

 

[ahuvas]

Sorry here it is.

 

[ahuvas]

Hi Uncle Gizmo I dont think this work will approach either. THis is a marking guide for a questionnaire that is almost identical (and the precursor) to this one which should give you an idea of what I am looking to do.

http://www.ssc.ca/documents/case_studies/2006/documents/sleep_BQ_e.pdf

Unfortunately the exact questionnaire is not available.

I cant find a copy of the questionnaire online on which this actual question was generated but the case is similar. There were two options in the options group that produced the same "score" i.e. 1 while the other options represented a value of 2, 3,and 4 respectively.

 

[Brianwarnock]

Ok took another look see, but I'm a simple soul and tend to do things in a simple way. I would have just stored all the input info onto the database and done the analysis later, not during data input, I'm not saying which way is better just what I would have done.

Brian

 

[ahuvas]

The reason that I need to do it to the values now is that I need to produce a report that automatically scores the questionnaire for me. In this way the research assistant can print off the results straight away from any participants without having to mark it by hand

 

[Brianwarnock]

One inputs the data on a Form and it is all stored on the table(s).
A report is requested and a query runs to produce the data required on the report, including all calculations marking etc.

Brian