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?

 

[huv123]

BTW the table I added of possible combinations did not include errors. The errors would also include what you considered medium left and medium right.

 

[TheAceMan1]

[blue] . . . since there is [purple]not such thing medium[/purple], only "no preference" . . .[/blue]

Now . . . from your post origination:

[blue]If they have a [purple]medium preference[/purple] for one hand over another put only one tick in that column.[/blue]

I constructed the table from your post origination.

[blue]What can we do if your not sure what you want?[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[ProgramError]

Aceman1 was right about the medium thing

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

but hay ho! Lets get the thing on the way to being solved.

firstly I need to correct a line in my first coding becuase I just used random names for the checkboxes.
It should be

Counter = Abs(Check1) + Abs(Check2) + Abs(Check4) + Abs(Check8)

It seems to me there are only four states or results here.

0000 nothing selected - would this be allowed? do you want a result back from this or will it be ommitted?

0011 two left selections = left preference

1100 two right selections = right preference

0101 one in left side and one in right side in any combination - no preference or medium preference (I'm going to call this no preference I think)

So using a little of theaceman1's amended coding, I would firstly store the four fields in a temp table with four fields for each of the checkboxes. Then finally convert into one value and save this value in the master table before moving to a new record.

I hope this gets you on your way...

Option Compare Database
Option Explicit

Public Counter As Byte
public binval as byte

Public Function IncCounter()
  IncCounter = False
  Counter = Abs(Check1) + Abs(Check2) + Abs(Check4) + Abs(Check8)
  If Counter <= 2 Then
    'counter ok
  Else
    'counter not ok
    IncCounter = True
  End If
End Function

public sub CheckAnswerAndStore()

'combine the information into one value using binary notation
binVal = (abs(Check1) * 1) + (abs(Check2) * 2) + (abs(Check3) * 4) + (abs(Check4) * 8)

' select the case depending on the result
case select binVal ' this be be wrong syntax please check

case 0
msgbox "no answer"

case 12
msgbox "right preference"
 
case 3
msgbox left preference

else case
'all other results
 msgbox "no preference"

end select
end sub

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 Submit_click()
' check if zero or two checkboxes selected if so store result
if binval = 0 or counter = 2 then 
CheckAnswerAndStore
else
msgbox "Place error message here"
end if
' Now append the answer to the main table
End Sub

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

Substituting the messages for your own coding.
With modification, this could be used to decode the stored binval's too.
Something to think about.... Do you want the user to continue to a new question with a submit button or after two checkboxes have been ticked (last option doesn't take into account no input or allow a change of mind situation).

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

 

[huv123]

Sorry TheAceMan I have no idea why I wrote that (in the first post). Probably because I was exhausted from working through the night on this

 

[TheAceMan1]

huv123 . . .

Don't feather yourself . . . It happens!

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[ProgramError]

Sorry missed the question

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?

yes. Because the computer wouldn't know which box to untick.

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

 

[huv123]

Sorry I have been so quiet - Ive had 12 hour days and no time to look at this but I will this weekend!

 

[huv123]

Okay I have now had a chance to look at it because there are a few updates/comments I was wondering if someone could run me through what to do again. I am not familiar with this code so I dont know what I should personalise and what I should leave alone.

Here is the updated table.

LI L2 R1 R2
Bin8 Bin4 Bin2 Bin1

00 0 0 0 0 No Preference
00 1 0 1 0 No Preference
02 1 0 0 1 No Preference
03 0 1 1 0 No Preference
04 1 0 1 0 No Preference
05 1 1 0 0 Strong Left
06 0 0 1 1 Strong Right
07 1 1 1 0 Error
08 0 1 1 1 Error
09 1 0 1 1 Error
10 1 1 0 1 Error
11 1 1 1 1 Error
12 0 1 0 0 Error
13 0 0 1 0 Error
14 1 0 0 0 Error
15 0 0 0 1 Error

 

[huv123]

Programmer: 0000 <- I need a result back of Does not experience or something similar

BTW This is definitely the wrong syntax: case select binVal ' this be be wrong syntax please check

"So using a little of theaceman1's amended coding, I would firstly store the four fields in a temp table with four fields for each of the checkboxes. Then finally convert into one value and save this value in the master table before moving to a new record" Sorry can you explain this?

In other words while I get the gist of the statements I have never seen this code before and odnt know how to use it or what to put where.

 

[huv123]

Sorry ProgramError/TheAceMan but could someone help me when you get a chance please?

 

[TheAceMan1]

huv123 . . .

First it appears your not fully understanding the table.

[blue]DecNum[/blue] is the decimal equivalent of the binary weights per selected check boxes. Example: 1010 bin = 10 dec. 0101 bin = 5 dec. You simply add up the weights (8, 4, 2, 1) where a one (1) appears. Where 1 means checked, and 0 means not checked, for a total of 16 possible states for the checkboxes.

The [blue]DecNum[/blue] now describes the state of the checkboxes and gives us a handle on detection. Decimal 10 means the selections were 1010 or L1 checked, L2 not checked, R1 checked, R2 not checked.

Also note: your latest post of the table redistributes the binary weights for the boxes.

[blue][tt]I origionally had:

    [purple][b]R2  R1  L2  L1[/b][/purple]
Dec Bin Bin Bin Bin
Num  8   4   2   1    Condition
*** *** *** *** ***  ************

Your latest post shows:

   [purple][b] L1  L2  R1  R2[/b][/purple]
Dec Bin Bin Bin Bin
Num  8   4   2   1    Condition
*** *** *** *** ***  ************[/tt][/blue]

For reference here's the new table:

[blue][tt]     L1  L2  R1  R2   
Dec Bin Bin Bin Bin
Num  8   4   2   1    Condition
*** *** *** *** ***  ************
00   0   0   0   0   No Preference
[COLOR=white red]01   0   0   0   1   Error[/color]  
[COLOR=white red]02   0   0   1   0   Error[/color]  
03   0   0   1   1   Strong Right
[COLOR=white red]04   0   1   0   0   Error[/color]
05   0   1   0   1   No Preference
06   0   1   1   0   No Preference
[COLOR=white red]07   0   1   1   1   Error[/color]  
[COLOR=white red]08   1   0   0   0   Error[/color]
09   1   0   0   1   No Preference
10   1   0   1   0   No Preference
[COLOR=white red]11   1   0   1   1   Error[/color]
12   1   1   0   0   Strong Left
[COLOR=white red]13   1   1   0   1   Error[/color]
[COLOR=white red]14   1   1   1   0   Error[/color]
[COLOR=white red]15   1   1   1   1   Error[/color][/tt][/blue]

Now . . . according to the table your biggest problem is a single checkbox selection.

[blue]How does the user get started when their [purple]1st selection is invalid?[/purple][/blue]

You could let this go and perform a final check in the forms [blue]BeforeUpdate[/blue] event, keeping the participent on the record if validation fails. Since you have to bypass single selection, why not just validate when the participent is done. This gives them a free and less confusing selection base to work from. Let alone its much easier to code. Versus locking if this, unlicking if that, start over if this, can't do if that, kind of thing.
[blue]Rereading this entire thread I've watch you tie your hands more with each post.[/blue]

The following is a simple routine in the forms [blue]BeforeUpdate[/blue] event, preventing the participent from advancing to the next question should validation fail ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]   Dim Msg As String, Style As Integer, Title As String
   Dim NL As String, DL As String, binVal As Integer
      
   binVal = (binVal Or -8 * Me![purple][b]L1[/b][/purple] Or -4 * Me![purple][b]L2[/b][/purple] _
                    Or -2 * Me![purple][b]R1[/b][/purple] Or -1 * Me![purple][b]R2[/b][/purple])
   
   Select Case binVal
      Case 3
         Me![purple][b]Ans[/b][/purple] = "Strong Right"
      Case 12
         Me![purple][b]Ans[/b][/purple] = "Strong Left"
      Case 0, 5, 6, 9, 10
         Me![purple][b]Ans[/b][/purple] = "No Preference"
      Case Else 'All Errors
         NL = vbNewLine
         DL = NL & NL
         
         Msg = "Invalid Checkbox Combination!" & DL & _
               "The checkbox combinations you can make are:" & DL & _
               "   Two Left" & NL & _
               "   Two Right" & NL & _
               "   One each Left or Right" & NL & _
               "   None" & DL & _
               "Try to remember this for the remaining questions!"
         Style = vbInformation + vbOKOnly
         Title = "Invalid Combination Detected! . . ."
         MsgBox Msg, Style, Title
         Cancel = True
         Me!L1.SetFocus
   End Select[code][blue]

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

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[huv123]

Woohoo I get it. At least the binary/decimal part - I havent touched it in 10 years I had to relearn binary numbers and now I understand if I give L1=8, L2=4, R1=2 and R2=0 that 1010 would be 8+2 = 10. Sorry for the confusion.

Now I tried out the code and think that the Q1Answer bit is too complicated and not needed for my putposes.

WIth respect to putting it in the before update event of the form I totally agree.

If I want all the questions checked as I leave the form, should the code now look like something like this (I have just done it for the first two questions but it seems like it works although there may be a neater way:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim Msg As String
Dim Style As Integer
Dim Title As String
Dim NL As String
Dim DL As String
Dim binVal As Integer
      
   binVal = (binVal Or -8 * Me!Q1L1 Or -4 * Me!Q1L2 Or -2 * Me!Q1R1 Or -1 * Me!Q1R2)
   
   Select Case binVal
   
   Case 1, 4, 7, 8, 11, 13, 14, 15
   
         NL = vbNewLine
         DL = NL & NL
         
         Msg = "Question 1: Invalid Checkbox Combination!" & DL & _
               "The checkbox combinations you can make are:" & DL & _
               "   Two Left" & NL & _
               "   Two Right" & NL & _
               "   One each Left or Right" & NL & _
               "   None" & DL & _
               "Try to remember this for the remaining questions!"
         Style = vbInformation + vbOKOnly
         Title = "Invalid Combination Detected! . . ."
         MsgBox Msg, Style, Title
         Cancel = True
         
         End Select
        
binVal = (binVal Or -8 * Me!Q2L1 Or -4 * Me!Q2L2 Or -2 * Me!Q2R1 Or -1 * Me!Q2R2)

Select Case binVal
      
      Case 1, 4, 7, 8, 11, 13, 14, 15
         NL = vbNewLine
         DL = NL & NL
         
         Msg = "Question 2: Invalid Checkbox Combination!" & DL & _
               "The checkbox combinations you can make are:" & DL & _
               "   Two Left" & NL & _
               "   Two Right" & NL & _
               "   One each Left or Right" & NL & _
               "   None" & DL & _
               "Try to remember this for the remaining questions!"
         Style = vbInformation + vbOKOnly
         Title = "Invalid Combination Detected! . . ."
         MsgBox Msg, Style, Title
         Cancel = True
                  
   End Select

Me!Q1L1.SetFocus  [red] Irregardless of the q that causes the trip I am happy for the cursor to be set as the first checkbox in the first question [/red]

End Sub

I just want to make sure that this tells the form to first check Question 1, then check Question 2 etc. so that it wont stop as soon as it gets "tripped".

How does this look?

 

[huv123]

Hi TheAceman,

What do you think?

Thanks

Ahuva

 

[TheAceMan1]

huv123 . . .

According to what you have it should work. However, there a question regarding your table structure.

I suggest you ease over to forum700, apprise them of your table structure (according to what your doing) and ask for comments. This thread is already too long . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[huv123]

Hi THe Aceman


It seems like one error (i.e. in question 1 if only when one box is selected) but no other boxes in any other question is selected (which is file) will trip all the message boxes to popup even thoguh case 0(0000) is not selected?

 

[TheAceMan1]

huv123 . . .

This is why I've prompted you to the other forum. I mentioned this is my 3rd post from the top. You'll have other issues if this isn't resolved! . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[huv123]

Im finding it to describe what the issue is if I dont know why my table would be violating rules of relational database design and why even if there are problems I would get that error?

 

[TheAceMan1]

huv123 . . .

Try the [blue]Exit Sub[/blue] where you see it:

[blue]Private Sub Form_BeforeUpdate(Cancel As Integer)
   Dim Msg As String, Style As Integer, Title As String
   Dim NL As String, DL As String, binVal As Integer
      
   binVal = (binVal Or -8 * Me!Q1L1 Or -4 * Me!Q1L2 Or -2 * Me!Q1R1 Or -1 * Me!Q1R2)
   
   Select Case binVal
      Case 1, 4, 7, 8, 11, 13, 14, 15
         NL = vbNewLine
         DL = NL & NL
         
         Msg = "Question 1: Invalid Checkbox Combination!" & DL & _
               "The checkbox combinations you can make are:" & DL & _
               "   Two Left" & NL & _
               "   Two Right" & NL & _
               "   One each Left or Right" & NL & _
               "   None" & DL & _
               "Try to remember this for the remaining questions!"
         Style = vbInformation + vbOKOnly
         Title = "Invalid Combination Detected! . . ."
         MsgBox Msg, Style, Title
         Cancel = True
         [purple][b]Exit Sub[/b][/purple]
   End Select
        
   binVal = (binVal Or -8 * Me!Q2L1 Or -4 * Me!Q2L2 Or -2 * Me!Q2R1 Or -1 * Me!Q2R2)

   Select Case binVal
      Case 1, 4, 7, 8, 11, 13, 14, 15
         NL = vbNewLine
         DL = NL & NL
         
         Msg = "Question 2: Invalid Checkbox Combination!" & DL & _
               "The checkbox combinations you can make are:" & DL & _
               "   Two Left" & NL & _
               "   Two Right" & NL & _
               "   One each Left or Right" & NL & _
               "   None" & DL & _
               "Try to remember this for the remaining questions!"
         Style = vbInformation + vbOKOnly
         Title = "Invalid Combination Detected! . . ."
         MsgBox Msg, Style, Title
         Cancel = True
   End Select

   Me!Q1L1.SetFocus

End Sub[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

The problem is you have a table with:
Q1L1
Q1L2
Q1R1
Q1R2
Q2L1
Q2L2
Q2R1
Q2R2
'
'
'
Q?L1
Q?L2
Q?R1
Q?R2

or dedicated checkboxes for each question! Not a good way to go espcially since each question has the same number of checkboxes.

Your getting the error because the code is checking (or will be) all questions at one time, instead of the current question record! This changes the code to where you have to detect what question line/record your on!

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[huv123]

Did you mean to put the exit sub after each cancel=true? I did this however I noticed two new issues:

1. If there are multiple errors in the form then only the first error message box pops up, you press OK and the form closes. I really need to see a message box one after the after for each error.

2. If a message box is tripped I want that the form will not close after the message box is pressed OK and that the person cant leave the form until any issues are resolved.

As in if Msg = Positive, cancel close, do not save any previous responses, and return back to questionnaire screen, which they will have to go through each time they press the "Finish" button

Else if no message boxes pop-up, close form and save data.