Clicky

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

asked 11/04/2011 08:49

jdershewitz's gravatar image

jdershewitz ♦♦


12 Answers:
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)
link

answered

peter57r's gravatar image

peter57r

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"
link

answered 2011-11-04 at 05:59:16

mbizup's gravatar image

mbizup

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?
link

answered 2011-11-04 at 06:04:23

jdershewitz's gravatar image

jdershewitz

"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.
link

answered 2011-11-04 at 06:04:58

peter57r's gravatar image

peter57r

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?
link

answered 2011-11-04 at 06:07:56

jdershewitz's gravatar image

jdershewitz

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

answered 2011-11-04 at 06:09:00

jdershewitz's gravatar image

jdershewitz

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.

link

answered 2011-11-04 at 06:11:44

peter57r's gravatar image

peter57r

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.
link

answered 2011-11-04 at 06:16:46

mbizup's gravatar image

mbizup

I can go that route.  Good thinking - not idea but it works.  Can you provide the code?
link

answered 2011-11-04 at 06:20:56

jdershewitz's gravatar image

jdershewitz

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
link

answered 2011-11-04 at 06:21:31

mbizup's gravatar image

mbizup

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

answered 2011-11-04 at 06:55:32

jdershewitz's gravatar image

jdershewitz

Exactly what I was looking for
link

answered 2011-11-04 at 06:57:46

jdershewitz's gravatar image

jdershewitz

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×1
×14
×6
×33

Asked: 11/04/2011 08:49

Seen: 211 times

Last updated: 11/04/2011 05:39

Categories