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

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

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.
2 Solutions
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.
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

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.

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