473,320 Members | 1,572 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

beacon
579 512MB
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."

Expand|Select|Wrap|Line Numbers
  1. Private Sub CommandButton1_Click()      
  2.     Dim total As Integer  'declare an integer for total
  3.     total = 0                  'initialize total equal to zero
  4.     Select Case total     'begin switch
  5.         Case 1
  6.             If Me.Yes1.Value = True Then   'if option button is true...
  7.                 total = total + 1                   'assign points to total
  8.             Else
  9.                 total = total + 0                   'otherwise assign no points
  10.         Case 2
  11.             If Me.Yes2.Value = True Then
  12.                 total = total + 2
  13.             ElseIf Me.No2.Value = True Then
  14.                 total = total - 1
  15.             Else: total = total + 0
  16.         Case 3
  17.             If Me.Yes3.Value = True Then
  18.                 total = total + 1
  19.             Else
  20.                 total = total + 0
  21.         Case 4
  22.             If Me.Yes4.Value = True Then
  23.                 total = total + 2
  24.             ElseIf Me.No4.Value = True Then
  25.                 total = total - 1
  26.             Else
  27.                 total = total + 0
  28.         Case 5
  29.             If Me.Yes5.Value = True Then
  30.                 total = total - 1
  31.             ElseIf Me.No5.Value = True Then
  32.                 total = total + 2
  33.             Else
  34.                 total = total + 0
  35.         Case 6
  36.             If Me.Yes6.Value = True Then
  37.                 total = total - 1
  38.             ElseIf Me.No6.Value = True Then
  39.                 total = total + 1
  40.             Else
  41.                 total = total + 0
  42.         Case 7
  43.             If Me.Yes7.Value = True Then
  44.                 total = total + 1
  45.             Else
  46.                 total = total + 0
  47.         Case 8
  48.             If Me.Yes8.Value = True Then
  49.                 total = total + 1
  50.             Else
  51.                 total = total + 0
  52.         Case 9
  53.             If Me.Yes9.Value = True Then
  54.                 total = total + 1
  55.             Else
  56.                 total = total + 0
  57.         Case 10
  58.             If Me.Yes10.Value = True Then
  59.                 total = total + 1
  60.             Else
  61.                 total = total + 0
  62.         Case Default
  63.             total = 0
  64.     End Select
  65.  
  66. 'Somewhere in here, print out total in the total textbox.
  67.  
  68. End Sub
  69.  
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
Jan 23 '08 #1
21 17374
kadghar
1,295 Expert 1GB
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:
Expand|Select|Wrap|Line Numbers
  1. total = 0
  2. select case qnum
  3.     case 1
  4.        ' (...)
  5. end select
well. hope that helps.

Oh, and change the last case with Case Else (didnt noticed that before)
Jan 23 '08 #2
Killer42
8,435 Expert 8TB
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?
Jan 24 '08 #3
QVeen72
1,445 Expert 1GB
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 :

Expand|Select|Wrap|Line Numbers
  1. Private Sub CommandButton1_Click()      
  2.     Dim total As Integer  'declare an integer for total
  3.     total = 0                  'initialize total equal to zero
  4.     Select Case total     'begin switch
  5.         Case 1
  6.             If Me.Yes1.Value = True Then   'if option button is true...
  7.                 total = total + 1                   'assign points to total
  8.             Else
  9.                 total = total + 0                   'otherwise assign no points
  10.            End If
  11.         Case 2
  12.             If Me.Yes2.Value = True Then
  13.                 total = total + 2
  14.             ElseIf Me.No2.Value = True Then
  15.                 total = total - 1
  16.             Else
  17.                 total = total + 0
  18.             End If
  19.         Case 3
  20. .
  21. .
  22. .
  23. .
  24.         Case Else
  25.             total = 0
  26.     End Select
  27.  
  28. End Sub
  29.  
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
Jan 24 '08 #4
it would be better if u tell wat all fields u hav and hw u hav created pages..is there only one form.
Jan 24 '08 #5
beacon
579 512MB
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
Jan 24 '08 #6
kadghar
1,295 Expert 1GB
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)


Expand|Select|Wrap|Line Numbers
  1. select case QNum
  2.     case 1
  3.          if optionbuton1.value then total = total + 1
  4.          if optionbuton2.value then  total = total + 2
  5.          if optionbuton3.value then  total = total - 1
  6.          QuestionTextbox = "Question 2"
  7.          optionbuton1.text = "1stOptionOfQuestion2"
  8.          optionbuton2.text = "2ndOptionOfQuestion2"
  9.          optionbuton3.text = "3drOptionOfQuestion2"
  10.     case 2
  11.      (...)
  12.     case [last question]
  13.          if optionbuton1.value then total = total + 2
  14.          if optionbuton3.value then  total = total -2
  15.          msgbox "your total is: " & total
  16. end select
  17. QNum = QNum +1
Is much of copy-paste, but it works.
Give it a try.

HTH
Jan 24 '08 #7
beacon
579 512MB
Expand|Select|Wrap|Line Numbers
  1. select case QNum
  2.     case 1
  3.          if optionbuton1.value then total = total + 1
  4.          if optionbuton2.value then  total = total + 2
  5.          if optionbuton3.value then  total = total - 1
  6.          QuestionTextbox = "Question 2"
  7.          optionbuton1.text = "1stOptionOfQuestion2"
  8.          optionbuton2.text = "2ndOptionOfQuestion2"
  9.          optionbuton3.text = "3drOptionOfQuestion2"
  10.     case 2
  11.      (...)
  12.     case [last question]
  13.          if optionbuton1.value then total = total + 2
  14.          if optionbuton3.value then  total = total -2
  15.          msgbox "your total is: " & total
  16. end select
  17. QNum = QNum +1
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...
Jan 24 '08 #8
kadghar
1,295 Expert 1GB
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
Jan 25 '08 #9
Killer42
8,435 Expert 8TB
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...

Expand|Select|Wrap|Line Numbers
  1. Dim Total As Long, Question As Long
  2. For Question = 1 To 10
  3.   If Me.Controls("Yes" & Question).Value Then
  4.     Total = Total + YesWeight(Question)
  5.   ElseIf Me.Controls("No" & Question).Value Then
  6.     Total = Total + NoWeight(Question)
  7.   Else
  8.     Total = Total + UnknownWeight(Question)
  9.   End If
  10. Next
  11.  
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.
Jan 25 '08 #10
beacon
579 512MB
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.
Jan 25 '08 #11
daniel aristidou
491 256MB
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....
Expand|Select|Wrap|Line Numbers
  1.  'Dim a value for each question 
  2. 'Dim these just under class 
  3. 'ie not within a sub
  4. Dim Question1 as integer
  5. Dim Question2 as integer
  6. etc
  7. 'For each option button
  8. private sub ans1Yes_click()
  9. Question1 = 1
  10. end sub
  11.  
  12. 'No
  13. private sub ans1No_click()
  14. Question1 = 2
  15. end sub
  16.  
  17. 'Dont know
  18. private sub ans1dontknow_click()
  19. Question1 = 0
  20. end sub
  21.  
  22. 'Etc
  23. 'On the total button_click add
  24. Private sub total_click() 
  25. total.caption = Question1 + question2 'etc
  26. End Sub
  27.  
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.
Jan 25 '08 #12
Killer42
8,435 Expert 8TB
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.
Jan 27 '08 #13
daniel aristidou
491 256MB
. 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
Jan 27 '08 #14
Killer42
8,435 Expert 8TB
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?
Jan 29 '08 #15
Hi,

I was referred to this page because I have a very similar task to you. I also posted the question at the MSN forum, and got a reply that might help you (if you still need help!)

Expand|Select|Wrap|Line Numbers
  1. Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
  2. Dim total As Integer
  3. For Each ctl As Control In Me.Controls
  4.    For Each child As Control In ctl.Controls
  5.       If TypeOf child Is RadioButton AndAlso CType(child, RadioButton).Checked = True Then
  6.          total += CType(child.Tag, Integer)
  7.       End If
  8.    Next child
  9. Next ctl
  10. MessageBox.Show("Total is " & total.ToString)
  11. End Sub
When I use radio buttons and group them together and apply the relevant values, this code works nicely to total the score and display it in a message box, although I'm now modifying it to show a form with a certain response.

Can anybody explain how this code works to me?
Jan 29 '08 #16
kadghar
1,295 Expert 1GB
Hi,
...
Can anybody explain how this code works to me?

Hi!

Sure, it uses a FOR EACH to check every control in the form.

For each one, it uses an IF to see if it's a radiobutton and if its checked, and if it is, it does something (increase score for example). Then it shows a msgbox with your total.

HTH
Jan 30 '08 #17
Hi!

Sure, it uses a FOR EACH to check every control in the form.

For each one, it uses an IF to see if it's a radiobutton and if its checked, and if it is, it does something (increase score for example). Then it shows a msgbox with your total.

HTH
Aha, I understand it now! And what do 'child', 'ctl', 'control in' and 'ctype' refer to?
Jan 30 '08 #18
kadghar
1,295 Expert 1GB
Aha, I understand it now! And what do 'child', 'ctl', 'control in' and 'ctype' refer to?
He's supposing you've wrote the value of each answer as the TAG, so it calls it to sum it to the total, and since is recomended to use Option Strict, he also makes the Type Change into an integer.
Jan 30 '08 #19
Killer42
8,435 Expert 8TB
Expand|Select|Wrap|Line Numbers
  1. For Each ctl As Control In Me.Controls
  2.    For Each child As Control In ctl.Controls
  3.       ' ...
  4.    Next child
  5. Next ctl
  6.  
I just realised. If this code works, it must mean that one of the following must be true.
  • Controls which don't have any children report themself as the only child, or
  • The writer always places the radio buttons within a frame/group/whatever. Perhaps this is mandatory in this version of VB?
Jan 30 '08 #20
kadghar
1,295 Expert 1GB
Expand|Select|Wrap|Line Numbers
  1. For Each ctl As Control In Me.Controls
  2.    For Each child As Control In ctl.Controls
  3.       ' ...
  4.    Next child
  5. Next ctl
  6.  
I just realised. If this code works, it must mean that one of the following must be true.
  • Controls which don't have any children report themself as the only child, or
  • The writer always places the radio buttons within a frame/group/whatever. Perhaps this is mandatory in this version of VB?
Since he's checking if the children are the radiobuttons, i'd say is the second one.
Jan 30 '08 #21
beacon
579 512MB
If it were possible to frame radio buttons in VBA, in the same fashion as you can in VB 5 or 6, then that code would work brilliantly.

However, if you could frame radio buttons, I probably wouldn't have posed the question. There are frames in VBA, but they don't serve the same purpose as they do in VB 5 or 6 (at least not from my experience). They are purely asthetic unless otherwise defined, which I have found to be otherwise out of my league.

I wasn't offended by what you said Killer...I hadn't tested it out because I couldn't come up with a solution for the switch (or select case, sorry C++), so my claim was unfounded.

I decided to order a few books on VBA today to see if they can't shed some light on the efficiency issues of certain tools, as opposed to others. As it stands right now, I have 4 dropdown combo boxes that are filled from an array with 900+ indices, so, in addition to a statement that would total the values of 10 radio buttons, my form is sure to slow down even more than it already has.

As of now, it takes nearly 2 minutes to load the form and will undoubtedly be used by people who don't have time for such inefficiency. I don't really care to please them because I was happy to finally get a combo box to fill with values (there aren't a lot of tutorials on how to do so in Word and I feel like I was lucky to stumble upon/rig the solution).

I will test out m2165(?)'s suggestion and post my results when I return to work tomorrow...

Thanks again everybody!
Jan 30 '08 #22

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: brett | last post by:
Here is my SQL string: "SELECT to_ordnum, to_orddate," _ & "(SELECT SUM((DDPROD.pr_stanmat * DDPROD.pr_prfact) * (DOBOM2.b2_quant * DDORD.or_quant)) FROM DDPROD INNER JOIN DOBOM2 ON...
12
by: Kevin Lyons | last post by:
Hello, I am trying to get my select options (courses) passed correctly from the following URL: http://www.dslextreme.com/users/kevinlyons/selectBoxes.html I am having difficulty getting the...
6
by: Ben Hallert | last post by:
Hi guys, I'm trying to figure out what bone headed mistake I made on something I put together. I've got a form (named 'context') that has a variable number of select-multiple inputs on it. ...
3
by: Stewart | last post by:
Dear comp.lang.javascript, I have more than once wanted to manipulate the contents of select boxes dynamically, whilst the boxes contain <optgroup> tags. Manipulation of a select box containing...
9
by: Christian Wetzig | last post by:
hi, i have a table with test_field of type "TEXT". when i do: select test_field from test where id=1; the connection (with psql) hangs and no output is received, so i have to kill psql. ...
2
by: onetitfemme | last post by:
How come this works http://www.geocities.com/tekmonk2005/testSelectJS00.html works fine in Konqueror, but this http://www.geocities.com/tekmonk2005/testSelectJS02.html doesn't in FireFox,...
2
by: kansaskannan | last post by:
I have an Access 2000 database which lists customers by name, and how much (in dollars) they have purchased of various products. How do I write a SQL statement to select customers who make up the...
5
by: alanb | last post by:
Hi, hope someone can help, I need to be able to keep a running total of radio buttons selected, as a user goes through a set of 16 questions, devided in to 4 catorgories, then on "submit" have the...
2
by: Joe C. | last post by:
hello, just joined the group, i've run into a wall and am seeking some help. here is my query: SELECT DATEPART(year, TTmain.TTDate) AS 'Year', SUM(TTmain.TTAmt) + SUM(TTmain.TTFee) AS...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.