Microsoft KB Archive/109704

= ACC: Storing Calculated Values Using ControlSource Property =

Article ID: 109704

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q109704



SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.

This article describes the ControlSource property as it applies to controls, and demonstrates how you can use the SetValue macro action to store calculated values in a control's underlying table.



MORE INFORMATION
A control can be classified as bound, unbound, or calculated depending on the contents of its ControlSource property.

A bound control on a form or report is linked to a field in an underlying table or query. This type of control is used to display, enter, and update data in a field. For example, the following control is a bound control:

  Name: Last Name ControlSource: LastName

NOTE: In Microsoft Access version 1.x, the Name property is called the ControlName property.

This control displays the data from the LastName field, and stores any changes made in the control to the LastName field in the form's underlying table.

An unbound control has a ControlSource property that is blank, meaning that it is not connected to a field from the underlying table, a query, or an expression. An unbound control can display data, but it cannot save data. The data contained in an unbound control is temporary; it is lost when the form or report is closed.

A calculated control displays a value derived from data in one or more fields from the underlying table or query or from other controls. The calculation is the result of an expression assigned to the ControlSource property for that control. A calculated control can display data that is the result of an expression, but it cannot save data. For example, the following control is a calculated control:

  Name: MyControl ControlSource: =[LastName] & ", " & [FirstName]

The MyControl control displays a full name derived from an expression concatenating the last name and first name. The full name is not stored in any table. Calculated controls are often used to display calculations on a form that do not need to be stored in the underlying table.

Storing Calculations in a Field
The following example demonstrates how to use the SetValue macro action to store calculated values in a control's underlying table.

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb (or NWIND.MDB in Microsoft Access 2.0 or earlier). You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

 Open the sample database Northwind.mdb. Open the Order Details table in Design view.  Add the following field to the table:

      Field Name:   Total Data Type:   Currency  Save and then close the table. Create a new, blank form based on the Order Details table. Save the Form as Order Details Form.</li> If it is not displayed, open the field list by clicking Field List on the View menu.</li> Drag the Quantity, UnitPrice, and Total fields from the field list to the form. These are bound controls on the form. Note that each control's ControlSource property contains the name of the control's underlying field.

NOTE: In Microsoft Access 2.0 or earlier, there is a space in Unit Price.</li>  Create the following new macro, and then save it as Test1:

<pre class="fixed_text">      Macro Name      Action Test1          SetValue

Test1 Actions SetValue Item: [Total] Expression: Ccur([UnitPrice]*[Quantity])

NOTE: In Microsoft Access 2.0 or earlier, there is a space in Unit Price.

Note that this macro is equivalent to the expression:

<pre class="fixed_text">      [Total]= Ccur([Unit Price]*[Quantity])

When this macro runs, it will fill the Total field with the calculation's results. </li> Open the Order Details Form in Design View. Set the form's OnCurrent property to the Test1 macro. This causes the macro to run when the form is opened, and every time you move from one record to another.</li> Set the Quantity text box's AfterUpdate property to the Test1 macro. This causes the Test1 macro to run whenever the contents of the Quantity control are modified.</li> Set the UnitPrice text box's AfterUpdate property to the Test1 macro. This causes the Test1 macro to run whenever the contents of the UnitPrice control are modified.</li> View the form in Form view.</li></ol>

Note that the Test1 macro runs every time you move from one record to another, or whenever you modify a value in the Quantity or UnitPrice fields.

<div class="references_section">