Sum three fields "on the fly" & place results on an Access form

Posted on 2007-08-08
Last Modified: 2008-01-09
Is there a simple way, in Access,  to have three fields automatically calculated (summed) with the results displayed on a form.  The results will be "on the fly" and not stored.  The answer will change whenever any of the three fields are changed.  The reports will do all of the other calculations and reports.

Thanks for any help you can give.
Question by:sherman6789
    LVL 39

    Accepted Solution

    You can use a total query as the record source for the form or You can use DSum.

    DSum(expr, domain, [criteria])

    expr An expression that identifies the numeric field whose values you want to total. It can be a string expression identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field. In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.

    domain A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for a query that does not require a parameter.

    criteria An optional string expression used to restrict the range of data on which the DSum function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DSum function evaluates expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise, the DSum function returns a Null.
    LVL 1

    Assisted Solution

    In the query populating the form I'd calculate the sum there.

    Like this:
    SumTotal: [FIELD1]+[FIELD2]+[FIELD3]

    Then in the AfterUpdate event of the form controls I'd refresh, like this:
    Option Compare Database

    Private Sub FIELD1_AfterUpdate()
    End Sub

    Private Sub FIELD2_AfterUpdate()
    End Sub

    Private Sub FIELD3_AfterUpdate()
    End Sub

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now