Checkboxes nightmare

[huv123]

I am trying to build a form for a question. It asked people about preferences about which hand they use to perform during a number of tasks.

Vertically are the tasks i.e. writing, using a spoon etc..

Horizontally are 2 columns (Left and Right) each of which contains two check boxes. In this way each task corresponds to 4 checkboxes - 2 for left and 2 for right.

The participant is told that if their preference for particular task is so strong that they would never use another hand unless forced to they should put two ticks in the column corresponding to the hand that they use.

If they have a medium preference for one hand over another put only one tick in that column.

If they dont care which hand they use, put one tick in each hand (i.e. one in left and one in right).

If you dont perform the task at all leave all the checkboxes unticked.

You should not be able to tick all four boxes or two ticks in one column and one in the other because it doesnt make sense.
Furthermore, the way its scored is that:

1. you total the number of ticks in the "left" and "right". 2. You also have to substact the left total from the right total ("difference").
3. You then divide the "difference" by the "total" and multiply by 100 to give you your score.


I can probably work out how to do the calculations because I could make a query to calculate all these things. However when I am creating the table do I have to create 4 variables for each question i.e. Q1L1, Q2L2, Q1R1, Q1R2 etc..? Is this the best way to do it?

How do I put rules in place so the participant cannot pick all 4 or pick 2 and 1?

 

[TheAceMan1]

How are ya huv123 . . .

[blue]How do I put rules in place so the participant cannot pick all 4 or pick 2 and 1?[/blue]

Off the top of my head I see a child table with:

[tt] [blue]QAID[/blue] as Long (PrimaryKey)
[blue]Question[/blue] as Text
[blue]typAns[/blue] as [purple]LookUp Combobox[/purple]
[blue]L1[/blue] as Yes/No
[blue]L2[/blue] as Yes/No
[blue]R1[/blue] as Yes/No
[blue]R2[/blue] as Yes/No[/tt]
[ol][li]The LookUp is used to set the checkboxes on the form thru code (users make their selections here).[/li]
[li]The checkboxes on the form are [blue]hidden[/blue] to prevent user editing.[/li]
[li]The lookup selections are:
[tt][blue] Strong Left
Strong Right
Medium Left
Medium Right
Dont Care[/blue][/tt][/li]
[li]On the form, the [blue]AfterUpdate[/blue] event of the combobox performs the following:

[blue]   Dat = Me!ComboboxName
   
   If Dat = "Strong Left" Then
      Me!L1 = True
      Me!L2 = True
   ElseIf Dat = "Strong Right" Then
      Me!R1 = True
      Me!R2 = True
   ElseIf Dat = "Medium Left" Then
      Me!L1 = True
   ElseIf Dat = "Medium Right" Then
      Me!R1 = True
   Else [green]'Dont Care[/green]
      Me!L1 = True
      Me!R1 = True
   End If[/blue]

[/li][/ol]
There are other ways to do this. All dependent on your table schema. This is just a starting point.

[blue]Your Thoughts! . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[huv123]

Unfortunately the check boxes must be there - I cannot use combo boxes etc if I understand you properly. It has to be as exactly as described because this was how the questionnaire was developed and validated. Psychologists do not like it when you mess with the design.

 

[TheAceMan1]

huv123 . . .

In that case show the boxes, however set their [blue]Locked[/blue] property to [blue]Yes[/blue] to prevernt keyboard editing . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[huv123]

Yes,, I was thinking that is the way to go.

Make four fields per question.
Q1L1
Q1l2
Q1L3
Q1L4

In order to make the total of the each columns do a query
Total left = Q1L+Q1L2 etc...

and in order to validate their entries put in a if then else statement where if Q1L1=1 and q1L2=1 then Q1R1=Locked and Q1R2=Locked

However with the locked property, you know how you can right Text32.Visible = True
Do you write it Text32.Locked = True?

 

[TheAceMan1]

huv123 . . .

You'll lock the checkboxes (in design view) only if you use the combobox I presented. It sets the boxes approriately. If not then yes . . . you'll have to validate.

You need to give better thought to your table structure. I suggest you beam over to forum700. The table design guru's are there (you really need to get this done 1st). Then come back here if you have any form problems . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[huv123]

What do you see as the problem being with the table structure?

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[huv123]

Okay I was more or less able to validate all the options except one. Basically for every question the user has to pick at least two boxes. This can be:
1. Two from the left column
2. Two from the right colum
3. One from each the left and right columns.

I was able to validate the first two conditions with this code:

Private Sub Q1L1_AfterUpdate()

If Me.Q1L1 = -1 And Me.Q1L2 = -1 Then

Me.Q1R1.Locked = True
Me.Q1R2.Locked = True
Me.Q1R1.Controls(0).ForeColor = RGB(221, 221, 221)
Me.Q1R2.Controls(0).ForeColor = RGB(221, 221, 221)
Me.Q1R1.Value = 0
Me.Q1R2.Value = 0
Me.Q1Answer.Value = "Strong Left"

Else

Me.Q1R1.Locked = False
Me.Q1R2.Locked = False
Me.Q1R1.Controls(0).ForeColor = RGB(0, 0, 0)
Me.Q1R2.Controls(0).ForeColor = RGB(0, 0, 0)
Me.Q1R1.Value = Null
Me.Q1R2.Value = Null
Me.Q1Answer.Value = Null

End If

End Sub

This code is repeated for the after update of L2, R1 and R2 making the appropriate changes. In this way if they pick two from one side (which ever the order i.e. L1 first then L2 or L2 first and then L1) it will stop then from being able to choose from the other column.

However If they only one checkbox from one of the columns (i.e. L1), I can only check when they are closing the form if that they have either chosen L2, R1 or R2 as well. If not I want a message box to pop-up to tell them that they have to check the question.

I tried some code for this but can seem to get it right. This will have to be checked 4 times for each question
since the user may have chosen L2 but nothing else, R1 but nothing else or R2 but nothing else.

There are also 9 questions.


Do you know how to code so that we can check that the user picked at least 2 checkboxes from each questions on form close? Also how do I organize multiple (9 questions) if, then statements?

 

[huv123]

The other thing is that I havent been able to cause the after update event to fill in the text box hen the participant is:

A. "Neither Left nor right" (i.e. when Pick L1 and either R1 or R2

or

B. "Dont experience" - Dont pick any of the boxes.

My main problem is that these two things have to be checked every time one of the boxes is selected. How do I structure then if then else statement so that for each box's after update event. I can check for:

1. That the other column is greye out if both boxes in this boxes column is now selected snd that the value "Strong L/R" is entered into the text box.

2. If by selecting this box we can now determine that the person doesnt care (being at least one box in the other column is selected) that we return a "dont care" value to the text box

and finally if when the form closes none of the values are selected for a question (neither L1, L2, R1 or R2 is selected for question 1) that we return a "never experience" value to Question 1 text box.

 

[huv123]

CRAP Now when I put the code on all four boxes it will let me select both L1 and L2 or R1 and R2 but not L1 and R1 or L1 and R2 and so on..(i.e. not on from each column). I am so confused. I cant understand why the code would cause this.

If I tick one from the left column and then try to tick one from the right column it causes the left column box to become unticked.

 

[huv123]

Here is the code as it stands right now. I really need help

Private Sub Q1L1_AfterUpdate()
If Me.Q1L1 = -1 And Me.Q1L2 = -1 Then

Me.Q1R1.Locked = True
Me.Q1R2.Locked = True
Me.Q1R1.Controls(0).ForeColor = RGB(221, 221, 221)
Me.Q1R2.Controls(0).ForeColor = RGB(221, 221, 221)
Me.Q1R1.Value = 0
Me.Q1R2.Value = 0
Me.Q1Answer.Value = "Strong Left"

Else

Me.Q1R1.Locked = False
Me.Q1R2.Locked = False
Me.Q1R1.Controls(0).ForeColor = RGB(0, 0, 0)
Me.Q1R2.Controls(0).ForeColor = RGB(0, 0, 0)
Me.Q1R1.Value = Null
Me.Q1R2.Value = Null
Me.Q1Answer.Value = Null

End If

End Sub

Private Sub Q1L2_AfterUpdate()

If Me.Q1L1 = -1 And Me.Q1L2 = -1 Then

Me.Q1R1.Locked = True
Me.Q1R2.Locked = True
Me.Q1R1.Controls(0).ForeColor = RGB(221, 221, 221)
Me.Q1R2.Controls(0).ForeColor = RGB(221, 221, 221)
Me.Q1R1.Value = 0
Me.Q1R2.Value = 0
Me.Q1Answer.Value = "Strong Left"

Else

Me.Q1R1.Locked = False
Me.Q1R2.Locked = False
Me.Q1R1.Controls(0).ForeColor = RGB(0, 0, 0)
Me.Q1R2.Controls(0).ForeColor = RGB(0, 0, 0)
Me.Q1R1.Value = Null
Me.Q1R2.Value = Null
Me.Q1Answer.Value = Null

End If

End Sub


Private Sub Q1R1_AfterUpdate()
If Me.Q1R1 = -1 And Me.Q1R2 = -1 Then

Me.Q1L1.Locked = True
Me.Q1L2.Locked = True
Me.Q1L1.Controls(0).ForeColor = RGB(221, 221, 221)
Me.Q1L2.Controls(0).ForeColor = RGB(221, 221, 221)
Me.Q1L1.Value = 0
Me.Q1L2.Value = 0
Me.Q1Answer.Value = "Strong Right"

Else

Me.Q1L1.Locked = False
Me.Q1L2.Locked = False
Me.Q1L1.Controls(0).ForeColor = RGB(0, 0, 0)
Me.Q1L2.Controls(0).ForeColor = RGB(0, 0, 0)
Me.Q1L1.Value = Null
Me.Q1L2.Value = Null
Me.Q1Answer.Value = Null

End If

End Sub

Private Sub Q1R2_AfterUpdate()
If Me.Q1R1 = -1 And Me.Q1R2 = -1 Then

Me.Q1L1.Locked = True
Me.Q1L2.Locked = True
Me.Q1L1.Controls(0).ForeColor = RGB(221, 221, 221)
Me.Q1L2.Controls(0).ForeColor = RGB(221, 221, 221)
Me.Q1L1.Value = 0
Me.Q1L2.Value = 0
Me.Q1Answer.Value = "Strong Right"

Else

Me.Q1L1.Locked = False
Me.Q1L2.Locked = False
Me.Q1L1.Controls(0).ForeColor = RGB(0, 0, 0)
Me.Q1L2.Controls(0).ForeColor = RGB(0, 0, 0)
Me.Q1L1.Value = Null
Me.Q1L2.Value = Null
Me.Q1Answer.Value = Null

End If

End Sub

 

[xicana]

You think maybe it has something to do to setting the value of the checkboxes to Null when both your conditions are not met? I haven't tested your code, but that kinda stood out as I went over it.

Sandy

 

[xicana]

Hey Huv123,

Below I've pasted some code that I've tested and seems to get what you want. I didn't grey out any of the options, but with the code i put together I don't believe you need to - however you can edit what I did to make them grey out if you like.

Private Sub ValidateLeft()
    If Me.Q1R1 = 0 And Me.Q1R2 = 0 And Me.Q1L1 = 0 And Me.Q1L2 = 0 Then
        Me.Q1Answer = "Never Experience"
    ElseIf Me.Q1L1 = -1 And Me.Q1L2 = -1 Then
        Me.Q1R1 = 0
        Me.Q1R2 = 0
        Me.Q1Answer = "strong left"
    ElseIf Me.Q1L1 = -1 And Me.Q1R1 = -1 Then
        Me.Q1R2 = 0
        Me.Q1L2 = 0
        Me.Q1Answer = "Neither"
    ElseIf Me.Q1L1 = -1 And Me.Q1R2 = -1 Then
        Me.Q1R1 = 0
        Me.Q1L2 = 0
        Me.Q1Answer = "Neither"
    ElseIf Me.Q1L2 = -1 And Me.Q1R1 = -1 Then
        Me.Q1L1 = 0
        Me.Q1R2 = 0
        Me.Q1Answer = "Neither"
    ElseIf Me.Q1L2 = -1 And Q1R2 = -1 Then
        Me.Q1L1 = 0
        Me.Q1R1 = 0
        Me.Q1Answer = "Neither"
        
    End If
    
        
    
End Sub

Private Sub ValidateRight()
    If Me.Q1R1 = 0 And Me.Q1R2 = 0 And Me.Q1L1 = 0 And Me.Q1L2 = 0 Then
        Me.Q1Answer = "Never Experience"
    ElseIf Me.Q1R1 = -1 And Me.Q1R2 = -1 Then
        Me.Q1L1 = 0
        Me.Q1L2 = 0
        Me.Q1Answer = "strong right"
    ElseIf Me.Q1R1 = -1 And Me.Q1L1 = -1 Then
        Me.Q1R2 = 0
        Me.Q1L2 = 0
        Me.Q1Answer = "Neither"
    ElseIf Me.Q1R1 = -1 And Me.Q1L2 = -1 Then
        Me.Q1R2 = 0
        Me.Q1L1 = 0
        Me.Q1Answer = "Neither"
    ElseIf Me.Q1R2 = -1 And Me.Q1L1 = -1 Then
        Me.Q1R1 = 0
        Me.Q1L2 = 0
        Me.Q1Answer = "Neither"
    ElseIf Me.Q1R2 = -1 And Me.Q1L2 = -1 Then
        Me.Q1R1 = 0
        Me.Q1L1 = 0
        Me.Q1Answer = "Neither"
        
    End If
    
        
    
End Sub

Private Sub Q1L1_AfterUpdate()
    ValidateLeft
End Sub


Private Sub Q1L2_AfterUpdate()
    ValidateLeft
End Sub


Private Sub Q1R1_AfterUpdate()
    ValidateRight
End Sub

Private Sub Q1R2_AfterUpdate()
    ValidateRight
End Sub

From the way you labeled your checkboxes (Q1L1, Q1L2, etc..)it seems like you're going to create 4 checkboxes for each of nine questions....is that all going to be stored in 1 table? And, are you going to have multiple people taking this survey? Are you going to have them all stored in the same table as well? You don't need to give me answers I kinda just wanted to pose some questions you may want to think about (if you haven't already). If you have it down - then don't mind me!

Sandy

 

[TheAceMan1]

huv123 . . .

Whats the difference in checkbox selections setting the answer, compared to a combobox selection as the answer that properly sets the checkboxes? . . .

In any case if a binary weight is added to each checkbox, the following truth table can be constructed.

[tt][blue] R2 R1 L2 L1
Dec Bin Bin Bin Bin
Num 8 4 2 1 Condition
*** *** *** *** *** ************
00 0 0 0 0 No Selection
01 0 0 0 1 Medium Left
92 0 0 1 0 Medium Left
03 0 0 1 1 Strong Left
04 0 1 0 0 Medium Right
05 0 1 0 1 Don't Care
06 0 1 1 0 Don't Care
[COLOR=white red]07 0 1 1 1 Strong Left-Medium Right ERROR [/color]
08 1 0 0 0 Medium Right
09 1 0 0 1 Don't Care
10 1 0 1 0 Don't Care
[COLOR=white red]11 1 0 1 1 Strong Left-Medium Right ERROR [/color]
12 1 1 0 0 Strong Right
[COLOR=white red]13 1 1 0 1 Strong Right-Medium Left ERROR
14 1 1 1 0 Strong Right-Medium Left ERROR
15 1 1 1 1 Strong Right-Strong Left ERROR [/color][/blue][/tt]

This reveals five possible error conditions you need to test for. Numerically they are:
[blue]7, 11, 13, 14, 15[/blue]. The trick in code is assigning the binary weights. Then its a simple matter of pinging against those numbers.

So . . . to get on with this . . . In the code module of the form, copy/paste the following routine:

[blue]Public Sub ValidateCkecks()
   Dim Msg As String, Style As Integer, Title As String, DL As String
   Dim binVal As Integer
   
   DL = vbNewLine & vbNewLine
   
   binVal = (binVal Or -1 * Me!Q1L1)
   binVal = (binVal Or -2 * Me!Q1L2) 
   binVal = (binVal Or -4 * Me!Q1R1)
   binVal = (binVal Or -8 * Me!Q1R2)
   
   If binVal = 7 Or binVal = 11 Or binVal = 13 Or _
      binVal = 14 Or binVal = 15 Then
      Msg = "Your last checkbox selection will result in " & _
            "an improper combination, and will be removed!" & DL & _
            "Keep this in mind as you continue . . ."
      Style = vbInformation + vbOKOnly
      Title = "Wrong Checkbox Combination Error! . . ."
      MsgBox Msg, Style, Title
      Me(Screen.ActiveControl.Name) = False
   End If
   
End Sub[/blue]

Next in the [blue]AfterUpdate[/blue] event of each checkbox, copy/paste the following line:

[blue]   Call ValidateCkecks[/blue]

Also be aware: since I'm sure your using a continuous form, [blue]you can't lock the checkboxes[/blue] (at least not in this fashion). Besides, the code only allows valid entries.

Thats it! . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

huv123 . . .

I Posted the wrong/incomplete routine for [blue]ValidateCkecks[/blue]. Replace with the following:

[blue]Public Sub ValidateCkecks()
   Dim Msg As String, Style As Integer, Title As String, DL As String
   Dim binVal As Integer
   
   DL = vbNewLine & vbNewLine
   
   binVal = (binVal Or -1 * Me!Q1L1)
   binVal = (binVal Or -2 * Me!Q1L2)
   binVal = (binVal Or -4 * Me!Q1R1)
   binVal = (binVal Or -8 * Me!Q1R2)
   
   If binVal = 7 Or binVal = 11 Or binVal = 13 Or _
      binVal = 14 Or binVal = 15 Then
      Msg = "Your last checkbox selection will result in " & _
            "an improper combination, and will be removed!" & DL & _
            "Keep this in mind as you continue . . ."
      Style = vbInformation + vbOKOnly
      Title = "Wrong Checkbox Combination Error! . . ."
      MsgBox Msg, Style, Title
      Me(Screen.ActiveControl.Name) = False
   ElseIf binVal = 5 Or binVal = 6 Or binVal = 9 Or binVal = 10 Then
      Me.Q1Answer = "Don't Care"
   ElseIf binVal = 1 Or binVal = 2 Then
      Me.Q1Answer = "Medium Left"
   ElseIf binVal = 4 Or binVal = 8 Then
      Me.Q1Answer = "Medium Right"
   ElseIf binVal = 3 Then
      Me.Q1Answer = "Strong Left"
   ElseIf binVal = 11 Then
      Me.Q1Answer = "Strong Right"
   Else
      Me.Q1Answer = "No Answer"
   End If
   
End Sub[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[ProgramError]

Had a quick read through the thread asnd it seems to me you don't want more than two checkboxes ticked at once for every question.

How about using a counter to confirm the number of boxes ticked after updating and if over 2 then don't allow any more the change.



Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[ProgramError]

The following code works.
I used the following on 4 checkboxes with a table containing the question number and one field for each of the check boxes (The four boxes could be combined into one field once you move onto a new record).

Option Compare Database
Option Explicit

Public Counter As Byte

Public Function IncCounter()
  IncCounter = False
  Counter = Abs(Check10) + Abs(Check0) + Abs(Check12) + Abs(Check14)
  If Counter <= 2 Then
    'counter ok
  Else
    'counter not ok
    IncCounter = True
  End If
End Function

Sub Check1_beforeUpdate(Cancel As Integer)
  If IncCounter Then Cancel = IncCounter
End Sub

Sub Check2_beforeUpdate(Cancel As Integer)
  If IncCounter Then Cancel = IncCounter
End Sub

Sub Check4_beforeUpdate(Cancel As Integer)
  If IncCounter Then Cancel = IncCounter
End Sub

Sub Check8_beforeUpdate(Cancel As Integer)
  If IncCounter Then Cancel = IncCounter
End Sub

Private Sub Form_Open(Cancel As Integer)
' initialise counter
  Counter = Abs(Check10) + Abs(Check0) + Abs(Check12) + Abs(Check14)
End Sub

If two boxes are true and the user trieds to make another box true the it is cancelled. You could even add an error message to it if you wish.


Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[TheAceMan1]

Howdy ProgramError . . .

That takes care of the checkboxes. What about setting the [blue]Q1Anawer[/blue] textbox?

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[huv123]

Hi

I didnt realise that this thread was being updated because the notification only came this morning however the aceman to your truth table, each question should look like this, since there is not such thing medium, only "no preference" - you need two boxes ticked at all time - wither two on left, two on right or one in left column and one in right column:


01 0 0 0 0 Dont do the task
02 1 0 1 0 No preference l or right
03 0 1 0 1 No preference l or right
04 1 0 0 1 No preference l or right
05 1 1 0 0 Strong Left
06 0 0 1 1 Strong Right
07 0 1 1 0 No preference l or right

I think that is all the combinations

Its also important to note that I cant put any default value on these fields because 0 - as in I didnt select it because I dont do the task is not hte same thing as 0 because I already picked the two boxes in the left and now I cant pick the two boxes in the right

Also the reality is that at the end of in all I do ia a simple calculation with the boxes to get my final overall score.

BAsically you total the number of ticks in the left column and the number of ticks right columns.

You minus the value of the left column from the value of the right colum and divide by the total score (i.e. L + R) and then times by 100. I can then use a case statement to return a text value telling me based on the final score if overall they are left or right handed,l

All the statements above should be for validating to make sure the user did the right hting.

Thank you aceman and program error. Program - DOes that mean that if they change their mind they have to first untick one of the two boxes and it will restore "activity" in the other two boxes?