Disabling unwanted Check Boxes 1

[DAmoss]

Hi,

I have a set of 19 questions in my database, each question has a set of 5 choices (5 boolean fields) which are all defined as Check Boxes (True/False):

[Ans1], [Ans2], [Ans3], [Ans4] & [Ans5]

When I tick one of these [Ans] checkboxes on my form I want to be able to make sure that all the other ones are set to 'False' and that they are also disabled on my form. Unticking the one 'True' answer would re-enable all of them for selection again.

Can someone give a heads up on how to go about coding this open routine in vba?

I can hardcode each question in vba checking each field, but this would be extremely long-winded, there must be a way of writing any open ended sub-routine that allows me to pass in these boolean fields, set their values and disable them accordingly?

Thanks in advance
Don

 

[randy700]

Look at option groups. When you select one of the option buttons, the rest are unselected. The value of the selected option will be saved to your table.


Randy

 

[DAmoss]

Unfortunately your suggestion is no good for what I require, using the group option as suggested only passes a result back to one field.

I need the five boolean answer fields to be updated, not just a result being passed back to one field. One of my boolean fields can be true whilst the other four have to be set to false.

As I explained, I needed a sub-routine (in vba) that toggles one boolean field to True and the other four to False & Disables them.

Can anyone else help please?

 

[PHV]

[Ans1], [Ans2], [Ans3], [Ans4] & [Ans5]
Have a look here:

http://r937.com/relational.html

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

 

[randy700]

What you're suggesting is improper database design. Take a look at the link provided by PHV. Your table violaes the first rule of normalization.


Randy

 

[traingamer]

I can hardcode each question in vba checking each field, but this would be extremely long-winded, there must be a way of writing any open ended sub-routine that allows me to pass in these boolean fields, set their values and disable them accordingly?

Yes and no. You would still have to have an After Update event on each checkbox, but you could then pass a parameter to a routine which will then set and unset the boxes.



Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[DAmoss]

PHV
Pointing to an article on relational database design does not give me the answer to my question. can you give me an actual example of a vba routine that answers my question?

randy700
This is not improper database design, I am using Access as a software tool to solve an analysis problem. My data table does not violate any normalisation rules, the only problem here is that you have strayed from my original question, let's stick to that please.

Just to explain a bit further, this is a questionnaire table that contains 5 possible answers (all True/False)and as such I need to anaylse all the true values as well as ALL of the false values, having just one answer field doesn't hack it.

I hate to say this, but there is an increasing annoyance appearing on this help website where people don't actually answer a persons question directly, but they just tell them they are wrong in what they are trying to do ... wrong.

traingamer:
THANK YOU for sticking to my original question, you are correct in what you say, it is exactly that kind of open 'parameter' routine that I was hoping someone could show me an example of. I've managed to write one that works for one of the checkboxes, but I don't want to repeat it for all of the other checkboxes (if you see what i mean)

So just to clarify, I need an example of a procedure that takes in any 5 boolean fields, that then checks which booelan field is set to 'True'. and then disables the other 4. If none of the boolean fields are 'True' it enables all of the fields.

Thanks in advance to all who can help me.

 

[MajP]

When I tick one of these [Ans] checkboxes on my form I want to be able to make sure that all the other ones are set to 'False' and that they are also disabled on my form. Unticking the one 'True' answer would re-enable all of them for selection again
Just to explain a bit further, this is a questionnaire table that contains 5 possible answers (all True/False)and as such

If the above is correct then the following statement seems to be wrong.

I need to anaylse all the true values as well as ALL of the false values, having just one answer field doesn't hack it

I would think having one field simplifies the solution and perfectly "Hacks it".

According to the first quote there can only be one true answer. Unless I misunderstood you do not have 5 booean choices, you have a "select the one true choice from the list". Thus you have zero degrees of freedom. All subsequent values are predetermined. If one is true ,2,3,4,5 are false.

So in fact all I need to know is the value of the correct choice. All other fields are calculated fields and thus I would argue that you are in fact not normalized.

Example

questionNumber	intAnswer	questionText
1	        1	  question one?
2	        3	  question two?
3	        4	  question three?
4	        5	  question four?
5	        2	  question five?
19	        3	  question Nineteen?

now if I wanted I could build the query

SELECT tblQuestionAnswer.questionNumber, tblQuestionAnswer.questionText, IIf([intanswer]=1,True,False) AS Choice1, IIf([intanswer]=2,True,False) AS Choice2, IIf([intanswer]=3,True,False) AS Choice3, IIf([intanswer]=4,True,False) AS Choice4, IIf([intanswer]=5,True,False) AS Choice5
FROM tblQuestionAnswer;

and your result looks like

questionNumber	questionText	Choice1	Choice2	Choice3	Choice4	Choice5
1	question one?	-1	0	0	0	0
2	question two?	 0	0	-1	0	0
3	question three?	 0	0	0	-1	0
4	question four?	 0	0	0	0	-1
5	question five?	 0	-1	0	0	0
19	question Nineteen?	0	0	-1	0	0

So I would do this with a single option group, with my lables equal the question choices.

But maybe I misunderstand. Because even though you can calculate this with a query I would not think thay you would need to do this to analyze the results. If I have 4 people taking the test, and question 1 had responses of 2,3,3,5. Then I know for question 1 that the results are
True False
choice1 0 4
Choice2 1 3
Choice3 2 2
Choice4 0 4
Choice5 1 3

 

[MajP]

This does what you need

Private Sub ans1_Click()
  Call changeChecks(1, ans1.Value)
End Sub

Private Sub ans2_Click()
  Call changeChecks(2, ans2.Value)
End Sub

Private Sub ans3_Click()
  Call changeChecks(3, ans3.Value)
End Sub

Private Sub ans4_Click()
  Call changeChecks(4, ans4.Value)
End Sub

Private Sub ans5_Click()
  Call changeChecks(5, ans5.Value)
End Sub

Public Sub changeChecks(chkNum As Integer, ansVal As Boolean)
  Dim intCounter As Integer
  For intCounter = 1 To 5
    If (Not chkNum = intCounter) And ansVal = True Then
      With Me.Controls("ans" & intCounter)
        .Value = False
        .Enabled = False
      End With
    ElseIf ansVal = False Then
      With Me.Controls("ans" & intCounter)
        .Enabled = True
      End With
    End If
  Next intCounter
End Sub

However, vb is not a good solution for a bad data structure.

 

[MajP]

demo frmUncheck

http://www.4shared.com/file/75270872/59b37f83/Uncheck.html

 

[DAmoss]

MajP
Thank you for your concise reply, yes there can only be one 'True' choice out of all of the five given options and yes we could assume that the other four are set to 'False'. but in this case assumptions are not good enough, I have to make sure that the other four fields are indeed set to false and that the (false) tickboxes are disabled.

This stops multiple data entry errors occuring and more importantly this makes sure that when the answer table is eventually exported out into Excell, each answer column of data can be processed by a different person, who will indeed count up both the true & falses accordingly.

I think we all need to take a step back and remember that my original question was not about 'normalization' or writing queries, it was about writing a vba sub-routine that answered my question (be it the right or wrong way to go about it), that is why I posted it in this forum.

Anyway enough of all that ... thank you for the example code and the link to the other example, I will digest them accordingly.

And finally, to prove that I can sometimes come up with a solution of my own (when i really think about it) I came up with the following simplistic code that works when called in the checkbox afterupdate event:

The first parameter passed is the 'True' field value, the other four are the 'False' fields.

Private Sub ToggleTBoxes(vTrueFld, vTBox2, vTBox3, vTBox4, vTBox5 As String)
Dim vState As Boolean

vState = Me(vTrueFld).Value

If (vState = True) Then
Me(vTBox2).Value = False
Me(vTBox3).Value = False
Me(vTBox4).Value = False
Me(vTBox5).Value = False
Me(vTBox2).Enabled = False
Me(vTBox3).Enabled = False
Me(vTBox4).Enabled = False
Me(vTBox5).Enabled = False
Else
Me(vTBox2).Value = False
Me(vTBox3).Value = False
Me(vTBox4).Value = False
Me(vTBox5).Value = False
Me(vTBox2).Enabled = True
Me(vTBox3).Enabled = True
Me(vTBox4).Enabled = True
Me(vTBox5).Enabled = True
End If

End Sub

Again, thank you for all your help
Don

 

[MajP]

I do not think anyone "needs to take a step-back". You have to remember, most of responders on this site are reluctant to help do some fancy "trim work" when the house's foundation is crumbling. It routinely becomes one patch job after another, and it just snow-balls. You will routinely see people post on this site asking for a overly complex vba solution because their data structure is incorrect. If the data and design were initially correct, a complex solution would not be needed.

I do not think you grasped it, but my initial suggestion of using a single field and a option group satisfies every requirement listed below and uses a normalized structure
and requires no vba.

yes there can only be one 'True' choice out of all of the five given options and yes we could assume that the other four are set to 'False'. but in this case assumptions are not good enough,
-there is no assumption because the other fields are all calculated values.

I have to make sure that the other four fields are indeed set to false and that the (false) tickboxes are disabled.
-Not required with an option group. It takes care of it automaticailly.

This stops multiple data entry errors occuring and more importantly this makes sure that when the answer table is eventually exported out into Excell,
-see my query. Results look as if you had multiple answers, but only one value exists.

each answer column of data can be processed by a different person, who will indeed count up both the true & falses accordingly
-See query results

 

[DAmoss]

MajP

I think we will have to beg to differ on this one, you need to understand that my data table requires that I have the 5 boolean fields and not just 1.

I know that your initial suggestion will work to a certain extent, but you are not aware of what happens to the data afterwards (and I am not going to get into that debate)

As for the 'most of responders on this site are reluctant to help' comment, I come to this website because I know someone will be able to help me when I get stuck, I would help them accordingly if the table was turned.

One final point, the database works perfectly, the enabling and disabling of the tickboxes works beautifully and it produces all the correct reports and data exports as expected.

Cheers
Don