ACC: Storing Calculated Values Using ControlSource Property

• 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 to 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.

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 cannot save data. For example, the following control is a calculated control:

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

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

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.

1. Open the sample database Northwind.mdb.

2. Open the Order Details table in Design view.

3. Add the following field to the table:

          Field Name:   Total
          Data Type:    Currency
   
   

4. Save and then close the table.

5. Create a new, blank form based on the Order Details table. Save the Form as Order Details Form.

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

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

8. 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.
   
   

9. 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.

10. 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.

11. 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.

12. View the form in Form view.

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 kbusage kbfaq
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbinfo