We help IT Professionals succeed at work.

Need to assign values to a series of option buttons in MS Access then sum the total

jdershewitz
jdershewitz asked
on
I have a Microsoft Access 2007 database with 4 forms.  Each form has a question with 5 option buttons below it.  A user selects an option, then hits command button “Enter”.  That form closes and the next form opens.  The option buttons all have point values:

Option a = 1 point
Option b =  2 points
Option c = 3 points
Option d = 4 points
Option e = 5 points

Example:

Question 1: What is your favorite sport?

a.      Football
b.      Basketball
c.      Hockey
d.      Baseball
e.      None

So if the user selects option c they get 3 points

Again, there are 4 forms, so 4 total questions.  

I would like to add up all the points from all 4 forms, then at the end say something like “you have [x] points total”.  

I'm totally lost.  Can this be done with a series of case statements? Is this possible since each question and option group is on a different form? Or do I need to store all the values in some table? Help!

Thanks
Comment
Watch Question

BRONZE EXPERT

Commented:
Are the results all  being stored in a single table that identifies the user?
( I supposes I would add - if not, why not?)

If so then you can get the total by adding up all the scores for a given user.

Dsum("Scorefield", "Tablename", "UserID = " Me.userid)
SILVER EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Option groups have values associated with the selections made, which can be used for calculating your total.

If the values are not being stored, something you can try is instead of closing the forms, hide them.

On the last form, with something like a "submit" button, do the following:

Total = Forms!FormA.OptionGroupName1 + Forms!FormB.OptionGroupName2 + Forms!FormC.OptionGroupName3 + Forms!FormD.OptionGroupName4

if your forms and option groups have similar names such as frmSurvey1, frmSurvey2, frmSurvey3, etc, you can clean that up by using a loop.

After the calculation is done, you can close all forms like this, again using a loop if your naming convention makes that possible.

DoCmd.Close acForm, "FormName"

Author

Commented:
Actually the results do not need to be stored.  I'm just wondering if it's required to do what I want to do.  Any idea on the option button / case statement stuff?
BRONZE EXPERT

Commented:
"That form closes and the next form opens"

"Actually the results do not need to be stored."

So where do you think there is any data to add up then ?
The only data in a database is in tables.

Author

Commented:
hmmm I can see using the hide idea...but how do I assign values to the option buttons? I'm having a total brain freeze...can you provide the code?

Author

Commented:
I was thinking the values can just be summed within the visual basic? Pardon my ignorance...just started using Access yesterday!
BRONZE EXPERT

Commented:
Is there any reason why you are not using a single form with 5 option groups?
You would not need to store anything f you did that because you could just add up the score on the single form.

SILVER EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
You don't need additional code to create the option values.

In your form's design view, click one of the option radio buttons, and look at its property sheet.  Under the Data tab, there is an Option Value property.

When that option is selected by the user, the option group as a whole gets that value.

So you just need to assign the values you want to each of the options.

Author

Commented:
I can go that route.  Good thinking - not idea but it works.  Can you provide the code?
SILVER EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
Pete's idea of all of the option groups on a single form is more in line with my idea of what a survey form should look like.

The code for getting a total, assuming you have set the Option Values appropriately in the option groups' property sheets would be similar to what I posted for seperate forms.  You would just drop the form prefixes.  

So this:

<<
Total = Forms!FormA.OptionGroupName1 + Forms!FormB.OptionGroupName2 + Forms!FormC.OptionGroupName3 + Forms!FormD.OptionGroupName4
>>

Would become this:

Total = me.OptionGroupName1 + me.OptionGroupName2 + me.OptionGroupName3 + Forms!me.OptionGroupName4

Author

Commented:
Excellent.  Thanks guys very much.  I'll give it a shot later today and let you know how it turns out.  Thanks!

Author

Commented:
Exactly what I was looking for

Explore More ContentExplore courses, solutions, and other research materials related to this topic.