Select Case/If Statement to total a group of option buttons

[beacon]

Hello to everybody,

I have a section on a form that has 10 questions, numbered 1-10, with 3 option buttons per question. Each of the option buttons have the same response (Yes, No, Don't know), but each answer is given a different point value for each question.

For instance, question 1 might ask "Do you like candy?" and if you answer Yes you will get +1, No will earn you -1, and Don't know will net you zero points. The next question might earn you -1 for Yes, +2 for No, and so on.

I would like to total the points for each section in a disabled, but visible textbox at the end of the 10 questions. Problem is, I'm fairly sure that using an if statement will make my already large form run very slowly. I'm leaning toward using a select case (or switch) statement, but I'm having some trouble initializing it and then deciding what it is I'm actually testing against.

Right now, and keep in mind that this is my most recent frustrated attempt, I have a select case statement with nested if statements, but I'm getting an error message that says "Case without Select Case."

[code=vb]
Private Sub CommandButton1_ Click()
Dim total As Integer 'declare an integer for total
total = 0 'initialize total equal to zero
Select Case total 'begin switch
Case 1
If Me.Yes1.Value = True Then 'if option button is true...
total = total + 1 'assign points to total
Else
total = total + 0 'otherwise assign no points
Case 2
If Me.Yes2.Value = True Then
total = total + 2
ElseIf Me.No2.Value = True Then
total = total - 1
Else: total = total + 0
Case 3
If Me.Yes3.Value = True Then
total = total + 1
Else
total = total + 0
Case 4
If Me.Yes4.Value = True Then
total = total + 2
ElseIf Me.No4.Value = True Then
total = total - 1
Else
total = total + 0
Case 5
If Me.Yes5.Value = True Then
total = total - 1
ElseIf Me.No5.Value = True Then
total = total + 2
Else
total = total + 0
Case 6
If Me.Yes6.Value = True Then
total = total - 1
ElseIf Me.No6.Value = True Then
total = total + 1
Else
total = total + 0
Case 7
If Me.Yes7.Value = True Then
total = total + 1
Else
total = total + 0
Case 8
If Me.Yes8.Value = True Then
total = total + 1
Else
total = total + 0
Case 9
If Me.Yes9.Value = True Then
total = total + 1
Else
total = total + 0
Case 10
If Me.Yes10.Value = True Then
total = total + 1
Else
total = total + 0
Case Default
total = 0
End Select

'Somewhere in here, print out total in the total textbox.

End Sub
[/code]

I have noticed that some of the questions, the ones that give the same point value for answers as other questions, could possibly be grouped together in a single case, but because the answers are grouped by question, I thought it best to include my code this way to make things clear.

Thanks for any help any of you can throw my way...beacon

[kadghar]

It seems to me that the select case should be refered to the number of question you're in. e.g. lets define an integer called QNum.
so when you're in the first question, Qnum = 1, in the second one, qnum = 2 and so on.

then just:
[CODE=vb]total = 0
select case qnum
case 1
' (...)
end select[/CODE]
well. hope that helps.

Oh, and change the last case with Case Else (didnt noticed that before)

[Killer42]

First, if this is VB6, then you're in luck. Making the controls into a control array (actually, a number of arrays) will make your life much easier. Then you can use a loop, and the loop index will point to each of your (groups of) controls in turn, re-using the same code).

Second, Select Case produces (as far as I know) the same code as If... ElseIf... and so should make no difference to your performance.

Thirdly, you do understand, right, that the code as posted will never produce any total other than zero? Just think of the Select Case as a bunch of IFs based on tests of the same variable, and you should see what I mean. Remember, it's not a loop.

Uh oh... that Case Default looks like bad news. You're using VB2005 or 2008, right?

[QVeen72]

Hi,


Your Complie error is because of all the "IF"'s do not have an End IF (Within Case).. Also, Last Part of the Select is "Case Else",
Not sure about "Case Default"
Check this :

[code=vb]
Private Sub CommandButton1_ Click()
Dim total As Integer 'declare an integer for total
total = 0 'initialize total equal to zero
Select Case total 'begin switch
Case 1
If Me.Yes1.Value = True Then 'if option button is true...
total = total + 1 'assign points to total
Else
total = total + 0 'otherwise assign no points
End If
Case 2
If Me.Yes2.Value = True Then
total = total + 2
ElseIf Me.No2.Value = True Then
total = total - 1
Else
total = total + 0
End If
Case 3
.
.
.
.
Case Else
total = 0
End Select

End Sub
[/code]

And I'am not sure your Logic is appropriate, I Guess, there is no reason to use a "Select" on Total.. I mean, You want to Total depending on the user reaction, so no point to use "Select Case "

Regards
Veena

[ameesha]

it would be better if u tell wat all fields u hav and hw u hav created pages..is there only one form.

[beacon]

it would be better if u tell wat all fields u hav and hw u hav created pages..is there only one form.

Why would that be better? I'm not having any trouble with the rest of the form...just with this one section.

It's actually VBA that I'm using, so I doubt I'll be able to use a control array. I know this is a Visual Basic forum, but I thought I'd give it a shot and see if there was any way to accomplish what I set out to do because the problem has come up before.

Keep in mind that the code I gave was not logically derived. It was a half-hearted attempt to give an idea of what I was trying to do, which was total points for a given set of questions with multiple choices.

Unless anyone has any other ideas I'm just going to scrap it and move on...Thanks for the help though

[kadghar]

Why would that be better? I'm not having any trouble with the rest of the form...just with this one section.

It's actually VBA that I'm using, so I doubt I'll be able to use a control array. I know this is a Visual Basic forum, but I thought I'd give it a shot and see if there was any way to accomplish what I set out to do because the problem has come up before.

Keep in mind that the code I gave was not logically derived. It was a half-hearted attempt to give an idea of what I was trying to do, which was total points for a given set of questions with multiple choices.

Unless anyone has any other ideas I'm just going to scrap it and move on...Thanks for the help though

A good idea for a small survey or a test, is using a Select Case as you did. Use one integer to know what question you are in and other for the score. The Click event of your Submit Answer should look like this: (define de QNum and make it 1 when you load the first question, define the total outside the sub too)


[CODE=vb]select case QNum
case 1
if optionbuton1.va lue then total = total + 1
if optionbuton2.va lue then total = total + 2
if optionbuton3.va lue then total = total - 1
QuestionTextbox = "Question 2"
optionbuton1.te xt = "1stOptionOfQue stion2"
optionbuton2.te xt = "2ndOptionOfQue stion2"
optionbuton3.te xt = "3drOptionOfQue stion2"
case 2
(...)
case [last question]
if optionbuton1.va lue then total = total + 2
if optionbuton3.va lue then total = total -2
msgbox "your total is: " & total
end select
QNum = QNum +1[/CODE]

Is much of copy-paste, but it works.
Give it a try.

HTH

[beacon]

[CODE=vb]select case QNum
case 1
if optionbuton1.va lue then total = total + 1
if optionbuton2.va lue then total = total + 2
if optionbuton3.va lue then total = total - 1
QuestionTextbox = "Question 2"
optionbuton1.te xt = "1stOptionOfQue stion2"
optionbuton2.te xt = "2ndOptionOfQue stion2"
optionbuton3.te xt = "3drOptionOfQue stion2"
case 2
(...)
case [last question]
if optionbuton1.va lue then total = total + 2
if optionbuton3.va lue then total = total -2
msgbox "your total is: " & total
end select
QNum = QNum +1[/CODE]

Before I try it, I have a question about something. The case that I'm using will be for the question that I'm currently trying to assign a value to, right?

Also, what were you saying about the variable outside of the statement? I know what it says, but I'm not sure if I understand. After each of the case statement (case 1 for example), are you saying that I should update total?

Thanks...

[kadghar]

Before I try it, I have a question about something. The case that I'm using will be for the question that I'm currently trying to assign a value to, right?

Also, what were you saying about the variable outside of the statement? I know what it says, but I'm not sure if I understand. After each of the case statement (case 1 for example), are you saying that I should update total?

Thanks...

no, this code will run each time you click on Submit answer, you must declare outside 2 global variables : QuestionNumber and Total

When the test starts, QNumber should be set to 1 and Total to 0 (this is not really necesary, since 0 is the original value of a new variable), and Question 1 should be in the Question Texts as well as its options.

Each time you click on Submit Answer, it'll check the QNumber in the select case, Then its going to add to Total the number of points awarded, change the Question text, change the options and add 1 to the Question Number

When QNumber reaches the last number, it'll only add the points awarded for the last question and show you a msgbox with your final results.

HTH

[Killer42]

It's actually VBA that I'm using, so I doubt I'll be able to use a control array

Yeah, VBA does have certain limitations, I guess.

Personally though, I think it would be worthwhile to try and code a fairly neat little routine which will be easy to work with later. For example, I'd start (I don't work with VBA, so be gentle with me if I suggest the impossible) by defining arrays to hold the "weights" or scores of the yes/no/um answers. The code should be able to get by with some relatively compact loops.

For instance, to calculate the total...

[CODE=vb]
Dim Total As Long, Question As Long
For Question = 1 To 10
If Me.Controls("Ye s" & Question).Value Then
Total = Total + YesWeight(Quest ion)
ElseIf Me.Controls("No " & Question).Value Then
Total = Total + NoWeight(Questi on)
Else
Total = Total + UnknownWeight(Q uestion)
End If
Next
[/CODE]This assumes the option buttons are named Yes1, Yes2, Yes3... and No1, No2, No3 etc.

It also assumes that you can load up the arrays of scores at startup. If not, you might be able to call a routine at the start of this piece of code to load them.



By the way, by "outside the sub" kadghar was referring to declaring variables at a wider scope. Those declared inside a Sub exist only within that Sub. In "true" VB, you can declare them at the Form or Module level, or you can declare them as Public (previously known as "Global"). I don't know how much of this applies in VBA, though.

And about this being a VB forum - we're happy to deal with VBA as well, it's just that you won't find as much expertise in VBA around here. (If you do have VBA experience, it would be helpful if you could hang around and tackle the questions that come up). You may find more experienced VBA people in the Access forum. In fact I see they've now renamed it to the "Access / VBA forum", which may be significant.

[beacon]

Thanks for the help...

I usually try to tackle the VBA questions that I have an answer for, but my expertise is limited as well, so I'm not always the best resource.

Most of my VBA experience actually comes from using Access, so when I switch over to Word, which doesn't have as many friendly controls as Access, it's kinda difficult to find a solution that will a) actually work and/or b) be efficient and not take 10 minutes to load.

I'm a student who just so happens to have a job that allows me to put some of my programming experience to work, which is nice, but not always on track considering that I'm not technically an IT person with unlimited security clearance.

Instead of getting to program in VB6 or .Net, or C++ or PHP, I'm relegated to trying to alternative (i.e. ghetto fabulous) solutions to the problems I'm given.

All soap box issues aside, I do appreciate the help and I will try to make your suggestions work.

[daniel aristidou]

Hey... um instead of using loops etc why not just add the value on selection of the option button?
You could do it like so....
[CODE=vb] 'Dim a value for each question
'Dim these just under class
'ie not within a sub
Dim Question1 as integer
Dim Question2 as integer
etc
'For each option button
private sub ans1Yes_click()
Question1 = 1
end sub

'No
private sub ans1No_click()
Question1 = 2
end sub

'Dont know
private sub ans1dontknow_cl ick()
Question1 = 0
end sub

'Etc
'On the total button_click add
Private sub total_click()
total.caption = Question1 + question2 'etc
End Sub
[/CODE]

just realised this doesnt account for when u deselect them opt button this is easyly worked around...i belive you can work the rest out.

[Killer42]

Hey... um instead of using loops etc why not just add the value on selection of the option button?...

Off-hand, I'd say there's absolutely no reason why you couldn't do it this way. A lot of programming decisions do come down to simply which way you prefer.

That being said, there are one or two reasons why I wouldn't code it that way. Being a VB6 programmer, I have no idea whether the same things apply in VBA of course.

The main thing is the amount of code required. By doing everything in one compact calculation routine, there's very little to change if, for example, you added further questions. If you are writing separate chunks of code for each control, then you would have to add more in this case. Plus, if you decided to set default answers by setting the values of the option buttons at design time, then I guess you would need to either call all the click routines at startup, or simply set the values in the code. Again, none of this is necessarily bad - it's just not the way I would do it.

[daniel aristidou]

. Again, none of this is necessarily bad - it's just not the way I would do it.

Just thought it would be better that way because of the concern he has for the slowness of the form.
Also avoids too many ifs and case selections

[Killer42]

Just thought it would be better that way because of the concern he has for the slowness of the form.
Also avoids too many ifs and case selections

Keep in mind though, that was a concern that the code might make the form slow. I'd love to know whether it actually works out that way.

I'm fairly sure that using an if statement will make my already large form run very slowly...

No offence intended beacon, but I wouldn't give that "fairly sure" too much weight without some testing to back it up. Assuming that VBA is interpreted rather than compiled, who knows what effect the size or structure of the code will have?