ACC: Storing Calculated Values Using ControlSource Property

ID: Q109704

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

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.

If it is not displayed, open the field list by clicking Field List on the View menu.

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.

Create the following new macro, and then save it as Test1:


       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:


       [Total]= Ccur([Unit Price]*[Quantity])

When this macro runs, it will fill the Total field with the calculation's results.

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.

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.

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.

View the form in Form view.

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.

REFERENCES

For more information about the ControlSource property, type "controlsource" in the Office Assistant, click Search, and then click to view "ControlSource Property."

For more information about binding controls to fields, type "binding controls" in the Office Assistant, click Search, and then click to view "Bind an unbound control to a field."

For more information about creating calculated controls, type "calculated controls" in the Office Assistant, click Search, and then click to view "Create a calculated control."

Keywords : FmsHowto
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Issue type : kbinfo