Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Posts
    38

    Question Unanswered: useing sum or adding totals of tables in form

    I am having trouble with the code below I have a box in my form that is the total_score of all my questions and I want it to autoupdate and then input that total into the table under its heading total score, but I am having trouble I found that if I use

    say question one score is 1 and question 2 score is 1 then when I use

    =([Question_1]+0+[Question_1]) i get 2

    but when I use

    =([Question_1]+[Question_1]) i get 11

    and when I use

    =sum([Question_1]+[Question_1]) I get 22

    and when I use

    =sum([Question_1]+0+[Question_1]) I get 4

    what gives can anyone explain where I am going wrong also where should I be putting this to get the totals I am putting the code in the control_source yet by doing that I dont know how to update it to the table?

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    I'm assuming that in the line:

    =([Question_1]+[Question_1]) i get 11

    you really mean to have:

    =([Question_1]+[Question_2]) i get 11

    Create a textbox on your form which will hold the sum. Bind this TextBox to a table field (if you like). If you don't want your users to see the sum then set the textbox Visible property to False.

    In the BeforeUpdate event of your Form place the following code:

    Code:
    Me.myHiddenTextBox = CInt(Me.Question_1.Value) + CInt(Me.Question_2.Value)
    This of coarse is assuming that Question_1 and Question_2 are infact the Frames which house your Yes/No/NA CheckBoxes.

    there ya go...

  3. #3
    Join Date
    Jan 2004
    Posts
    38
    That they are I do want the user to see the total on the screen as well as update the table?
    I was wondering is this to be the name of the text box that has the total in it myHiddenTextBox?

    Its telling me that it is unable to find the macro ME
    Last edited by Crash1hd; 01-12-04 at 21:30.

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    Yes....but of coarse you can name the textbox whatever you want.

  5. #5
    Join Date
    Jan 2004
    Posts
    38
    Ok so I am useing this

    Me.Total_Score = CInt(Me.Question_1.Value) + CInt(Me.Question_2.Value)

    as the name of the table is Total_Score but its not updateting the table?

  6. #6
    Join Date
    Nov 2003
    Posts
    1,487
    Ok so I am useing this

    Me.Total_Score = CInt(Me.Question_1.Value) + CInt(Me.Question_2.Value)

    as the name of the table is Total_Score but its not updateting the table?
    It will update the textbox and the table when you either go to or create a new record or quit the DB. If you want to update the textbox as soon as the answers are supplied then you should us a sub procedure to carry out the summing task then Call this procedure from the OnClick event of each Frame in your form. Here is the Sub Procedure (copy & paste it to the Declarations section of the code module for your form which is directly under the Option Compare Database and Option Explicit statements.):

    Code:
    Private Sub TotalUpAnswers()
        Me.Total_Score = CInt(Me.Question_1.Value) + CInt(Me.Question_2.Value)
    End Sub
    Now , copy & paste the code line below into the OnClick event in each of your Frame:

    Code:
        Call TotalUpAnswers
    Below is what your Click events should look like:

    Code:
    Private Sub Question_1_Click()
       Call TotalUpAnswers
       'Any other code here...
    End Sub
    ___________________________________________________________
    
    Private Sub Question_2_Click()
       Call TotalUpAnswers
       'Any other code here...
    End Sub
    There ya go...

  7. #7
    Join Date
    Jan 2004
    Posts
    38
    OK I figured out where I was going wrong I named the frames

    QFrame1

    not Question_1 lol so where you have

    Me.Total_Score = CInt(Me.Question_1.Value) + CInt(Me.Question_2.Value)

    I should have

    Me.Total_Score = CInt(Me.QFrame1.Value) + CInt(Me.QFrame2.Value)

    so of course it kept giving me errors again I thank you for all the help

  8. #8
    Join Date
    Jan 2004
    Posts
    38
    ok new problem how do I have three values yes no and n/a show in the table as 1 0 and 0 and have it know the difference?

  9. #9
    Join Date
    Jan 2004
    Posts
    38
    Ok I figured that out on my own yeah I am starting to understand writen code for asp for a couple of years now and now that I realize that access code is just vbscript well its not so hard lol the stuff that was confusing me was IIF vs IF and stuff like that lol if anyone can stream line the code below please let me know

    Private Sub TotalUpAnswers()
    If Me.QFrame3.Value <> "Null" Then
    Me.Total_Score = CInt(Me.QFrame1.Value) + CInt(Me.QFrame2.Value) + CInt(Me.QFrame3.Value) + CInt(Me.QFrame4.Value) + CInt(Me.QFrame5.Value) + CInt(Me.QFrame6.Value) + CInt(Me.QFrame7.Value) + CInt(Me.QFrame8.Value) + CInt(Me.QFrame9.Value) + CInt(Me.QFrame10.Value) + CInt(Me.QFrame11.Value) + CInt(Me.QFrame12.Value) + CInt(Me.QFrame13.Value)
    If Me.QFrame1.Value = -1 Then
    Me.Total_Score = Me.Total_Score + 1
    End If
    If Me.QFrame2.Value = -1 Then
    Me.Total_Score = Me.Total_Score + 1
    End If
    If Me.QFrame3.Value = -1 Then
    Me.Total_Score = Me.Total_Score + 1
    End If
    End If
    End Sub

    Private Sub QFrame1_Click()
    Call TotalUpAnswers
    End Sub
    Private Sub QFrame2_Click()
    Call TotalUpAnswers
    End Sub
    Private Sub QFrame3_Click()
    Call TotalUpAnswers
    End Sub

    Thats it

  10. #10
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Originally posted by CyberLynx
    It will update the textbox and the table when you either go to or create a new record or quit the DB. If you want to update the textbox as soon as the answers are supplied then you should us a sub procedure to carry out the summing task then Call this procedure from the OnClick event of each Frame in your form. Here is the Sub Procedure (copy & paste it to the Declarations section of the code module for your form which is directly under the Option Compare Database and Option Explicit statements.):

    Code:
    Private Sub TotalUpAnswers()
        Me.Total_Score = CInt(Me.Question_1.Value) + CInt(Me.Question_2.Value)
    End Sub
    Now , copy & paste the code line below into the OnClick event in each of your Frame:

    Code:
        Call TotalUpAnswers
    Below is what your Click events should look like:

    Code:
    Private Sub Question_1_Click()
       Call TotalUpAnswers
       'Any other code here...
    End Sub
    ___________________________________________________________
    
    Private Sub Question_2_Click()
       Call TotalUpAnswers
       'Any other code here...
    End Sub
    There ya go...
    Hey Cyber,
    My hat off to you. I like your style in helping out someone and giving clear concise answers with examples. Could you help me with a little one? I have a database that when I have input all the data for the records I have an Update query which just mainly Unflags the query so the records don't show active. But I have to click 4 times for the update. Is there a way to update that in code? Just say it has a check box that needs to go from checked=to print to unchecked=no print. Please show me the way and I will appreciate it so much.
    Bud

  11. #11
    Join Date
    Nov 2003
    Posts
    1,487
    Please... start a new post with your question. It really shouldn't be mixed in within this thread. By creating a new post it will provide answers to your question which will perhaps benifit many people and be relatively easy to find (rather than be nested in a thread somewhere).

    Thanks Bud

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •