Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2008
    Posts
    15

    Unanswered: Sum All Fields in Form

    Hi;

    I have a form and i have many fields in my form eg.. (m1,m2,m3,.....m40)

    How can i sum all fields but i dont want eg.. = m1+m2+m3...m40

    Thanks ...

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    say you have a checksum control
    you could assign a value to this control (lets be dull and call it CheckSum)

    and then with a bit of code in background call a function say CalcChecksum

    function CalcCheckSUM() as double
    CalcCheckSUM=m1.value+m2.value+.m3value+m4.value.. .....+mN.value
    end function

    you could call the countchecksum function from any one of several places


    I'd suggest in the forms before update event......

    if the checksum doens't match then cancel the update


    so that could be
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    cancel = CalcCheckSUM() <>checksum.value 'if the two values are different then stop the record being updated (cancel=true), otherwise cancel=false so record can be updated
    'this is effectively the same as
    if CalcCheckSUM()<>Checksum.value then
    cancel=true
    else
    cancel=false
    endif

    End Sub

    HTH

  3. #3
    Join Date
    Mar 2008
    Posts
    15
    Thank you;

    But i have so many fields not 40 maybe 100

    for example..

    for each ctl in form.controls

    if left(ctl.name,1) = "M" then
    .....
    ..
    sum all fields ' m1+m2+m3+mx ( maybe x=100)

    end if
    next

    i did not make this..

    And im sorry for my bad English..
    Last edited by leexlee; 04-21-08 at 05:07.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Or you could write a VBA function to loop through the controls:

    Dim FinalAnswer as Double
    For i = 1 to 40
    FinalAnswer = FinalAnswer + Me.Controls("m" & i)
    Next i
    Me.FormSubTotalControl = FinalAnswer
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Mar 2008
    Posts
    15
    Ohh perfect answer;

    Thank you Startrekker and healdem..

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're welcome
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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