• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 418
  • Last Modified:

Running total using an option group and continuous forms

I am working on a questionnaire and I have an option group with 2 option buttons, it is displayed on a continuous form. I have 65 questions broken out on tab controls in groups of 13. I am trying to get a box to sum the score as they are answered. I added a text box with the following on each page:

For page 1 - questions 1 through 13
TotalScore=DSum("Score","Questions","Question between 1 and 13 AND CandidateID= " & [CandidateID] & "")
For page 2 - questions 1 through 26
TotalScore=DSum("Score","Questions","Question between 1 and 26 AND CandidateID= " & [CandidateID] & "")

Etc.

Score being the field I am summing (0 or 1)
Questions as the table
Question as the unique identifier for each question

I have tried to put a refresh on the TotalScore - after update and on exit on the option group. But I am always one count off (or behind).

Any help would be greatly appreciated.
0
plpking1
Asked:
plpking1
  • 5
  • 5
1 Solution
 
Stephen_PerrettCommented:
try putting

Me.Refresh

to refresh the whole form

Steve
0
 
plpking1Author Commented:
That works great thanks. Do you have any idea while as I am pressing the button the score always change to 1 and then after I update it changes to the actual sum?
0
 
plpking1Author Commented:
Sorry, actually it changes to a random number, not always 1. I put the Me.Refresh on after update on the option group. Is there a better place to put this?
Thanks again
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
Stephen_PerrettCommented:
After Update on the option group sounds like the best place if you want to automatically get an updated total after each question has been completed. Not sure why you get that strange number. Perhaps you could try clearing the textbox before running the DSums

with something like

Me!TotalScore.value = Null
Me.Repaint
TotalScore=DSum...

to see if that changes anything.

Steve
0
 
plpking1Author Commented:
Thanks - when I do this I get the following error:
Run-Time error '2448'
You Can't assign a value to this object

Thanks again for your help
0
 
Stephen_PerrettCommented:
OK, I'm not sure what you have there. The error suggests that TotalScore is not the name of a textbox.

Please provide a little more detail so I can get a clearer grasp what you are working with.

Where is the DSum function (e.g. in the control source of a textbox or in after update code)?

Do you have more than one textbox to show the total?
 
What is TotalScore (e.g. a variable or a textbox name)?

What have you named your Textbox(es)?

Steve
0
 
plpking1Author Commented:
Hi - So, I took the textbox TotalScore and took off the Dsum in the Control Source.
Put the following in the after update of the Option Group

Private Sub Frame3_AfterUpdate()
Me!TotalScore.Value = Null
Me.Repaint
TotalScore = DSum("Score", "Questions", "Question between 1 and 13 AND CandidateID= " & [CandidateID])

I do not get the error any longer but I am still a step behind in the totals. For instance if I have clicked all the boxs to 1 and then change back to 0, I am left with a tiotal of one.

Any ideas where I go from here?  Thanks in advance.


0
 
Stephen_PerrettCommented:
That looks a better option than DSum in textboxes

You still need the Me.Refresh to update the record source data

Try:
Private Sub Frame3_AfterUpdate()
    Me.Refresh
    'Me!TotalScore.Value = Null  '<-------I have commented these out because I
    'Me.Repaint                          '<------don't think they are needed any more
    Me!TotalScore.Value = DSum("Score", "Questions", "Question between 1 and 13 AND CandidateID= " & [CandidateID])

You can delete the redundant code if not required

Steve
0
 
plpking1Author Commented:
THANK YOU! THANK YOU! THANK YOU! This has been driving me crazy. Works like a charm now. A+
0
 
Stephen_PerrettCommented:
Great!

Thanks and all the best

Steve
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now