ACC2000: How to Use the ControlSource Property to Store Calculated Values

ID: Q209172

The information in this article applies to:

Microsoft Access 2000

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

This article applies to a Microsoft Access database (.mdb) and a Microsoft Access project (.adp).

SUMMARY

This article describes the ControlSource property and demonstrates how you can use the SetValue macro action to store calculated values in the underlying table of a control.

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 a report is linked to a field in an underlying table or query. You use a bound control to display, enter, and update data in a field. For example, the following control is a bound control:


   Name: Last Name
   ControlSource: LastName

The control displays the data from the LastName field, and stores any changes that you make in the control to the LastName field in the underlying table of the form.

An unbound control has a ControlSource property that is blank. This means that the control is not connected to a field from an 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 you close the form or the report.

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 on a form to display calculations that do not need to be stored in the underlying table.

Storing Calculations in a Field

If you want to store calculated values in the underlying table of a control, use the SetValue macro action, as demonstrated in the following example.

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. 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 the ControlSource property of each control contains the name of the underlying field of the control.

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


   Macro Name      Action
   ------------------------
   Test1           SetValue

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

Note that this macro is equivalent to the expression:


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

When this macro runs, it fills the Total field with the results of the calculation.

Open the Order Details Form in Design View. Set the OnCurrent property of the form to the Test1 macro. This causes the macro to run when you open the form and every time that you move from one record to another.

Set the AfterUpdate property of the Quantity text box to the Test1 macro. This causes the Test1 macro to run whenever you modify the contents of the Quantity control.

Set the AfterUpdate property of the UnitPrice text box to the Test1 macro. This causes the Test1 macro to run whenever you modify the contents of the UnitPrice control.

View the form in Form view.

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

REFERENCES

For more information about creating calculated controls, click Microsoft Access Help on the Help menu, type calculated control in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: inf

Keywords : kbdta FmsHowto
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto