You can use the SetValue action to set the value of a Microsoft Office Access 2007 field (field: An element of a table that contains a specific item of information, such as a last name. A Title field might contain Mr. or Ms. Databases such as Microsoft SQL Server refer to fields as columns.), control (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.), or property (property: A named attribute of a control, a field, or an object that you set to define one of the object's characteristics (such as size, color, or screen location) or an aspect of its behavior (such as whether the object is hidden).) on a form (form: An Access database object on which you place controls for taking actions or for entering, displaying, and editing data in fields.), a form datasheet (datasheet: Data from a table, form, query, view, or stored procedure that is displayed in a row-and-column format.), or a report (report: An Access database object that you can print, which contains information that is formatted and organized according to your specifications. Examples of reports are sales summaries, phone lists, and mailing labels.).
Note You cannot use the SetValue action to set the value of an Access property that returns an object (database objects: An Access database contains objects such as tables, queries, forms, reports, pages, macros, and modules. An Access project contains objects such as forms, reports, pages, macros, and modules.).
Note This action will not be allowed if the database is not trusted. For more information about enabling macros, see the links in the See Also section of this article.
The SetValue action has the following arguments.
||The name of the field, control, or property whose value you want to set. Enter the field, control, or property name in the Item box in the Action Arguments section of the Macro Builder pane. You must use the full syntax to refer to this item, such as controlname (for a control on the form or report from which the macro (macro: An action or set of actions that you can use to automate tasks.) was called) or Forms!formname!controlname. This is a required argument.
The expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) Access uses to set the value for this item. You must always use the full syntax to refer to any objects in the expression. For example, to increase the value in a Salary control on an Employees form by 10 percent, use
Forms!Employees!Salary*1.1. This is a required argument.
Note You shouldn't use an equal sign (=) before the expression in this argument. If you do, Access evaluates the expression and then uses this value as the expression in this argument. This can produce unexpected results if the expression is a string (string expression: An expression that evaluates to a sequence of contiguous characters. Elements of the expression can be: functions that return a string or a string Variant (VarType 8); a string literal, constant, variable, or Variant.).
For example, if you type ="String1" for this argument, Access first evaluates the expression as String1. Then it uses String1 as the expression in this argument, expecting to find a control or property named String1 on the form or report that called the macro.
Note In an Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose.) (.mdb or .accdb), click the Build button to use the Expression Builder to create an expression for either of these arguments.
You can use this action to set a value for a field or control on a form, a form datasheet, or a report. You can also set the value for almost all control, form, and report properties in any view. To find out whether a particular property can be set by using a macro and which views it can be set in, see the Help topic for that property in the Visual Basic Editor.
You can also set the value for a field in a form's underlying table even if the form doesn't contain a control bound (bound control: A control used on a form, report, or data access page to display or modify data from a table, query, or SQL statement. The control's ControlSource property stores the field name to which the control is bound.) to the field. Use the syntax Forms!formname!fieldname in the Item box to set the value for such a field. You can also refer to a field in a report's underlying table by using the syntax Reports!reportname!fieldname, but there must be a control on the report bound to this field, or the field must be referred to in a calculated control (calculated control: A control that is used on a form, report, or data access page to display the result of an expression. The result is recalculated each time there is a change in any of the values on which the expression is based.) on the report.
If you set the value of a control on a form, the SetValue action doesn't trigger the control's form-level validation rules (validation rule: A property that defines valid input values for a field or record in a table, or a control on a form. Access displays the message specified in the ValidationText property when the rule is violated.), but it does trigger the underlying field's table-level validation rules if the control is a bound control. The SetValue action also triggers recalculation, but the recalculation may not happen immediately. To trigger immediate repainting (repaint: To redraw the screen. The Repaint method completes any pending screen updates for a specified form.) and force the recalculation to completion, use the RepaintObject action. The value you set in a control by using the SetValue action is also not affected by an input mask (input mask: A format that consists of literal display characters (such as parentheses, periods, and hyphens) and mask characters that specify where data is to be entered as well as what kind of data and how many characters are allowed.) set in the control's or underlying field's InputMask property.
To change the value of a control, you can use the SetValue action in a macro specified by the control's AfterUpdate event property. However, you can't use the SetValue action in a macro specified by a control's BeforeUpdate event property to change the value of the control (although you can use the SetValue action to change the value of other controls). You can also use the SetValue action in a macro specified by the BeforeUpdate or AfterUpdate property of a form to change the value of any controls in the current record.
Note You can't use the SetValue action to set the value of the following controls:
- Bound controls and calculated controls on reports.
- Calculated controls on forms.
Changing the value of or adding new data in a control by using the SetValue action in a macro doesn't trigger events such as BeforeUpdate, BeforeInsert, or Change that occur when you change or enter data in these controls in the user interface. These events also don't occur if you set the value of the control by using a Visual Basic for Applications (VBA) module.
This action isn't available in a VBA module. Set the value directly in VBA.
Set the value of a control by using a macro
The following macro opens the Add Products form from a button on the Suppliers form. It shows the use of the Echo, Close, OpenForm, SetValue, and GoToControl actions. The SetValue action sets the SupplierID control on the Products form to the current supplier on the Suppliers form. The GoToControl action then moves the focus to the CategoryID field, where you can begin to enter data for the new product. This macro should be attached to the Add Products button on the Suppliers form.
||Echo On: No
||Stop screen updating while the macro is running.
Object Type: Form
Object Name: Product List
|Close the Product List form.
Form Name: Products
Data Mode: Add
Window Mode: Normal
|Open the Products form.
|Set the SupplierID control to the current supplier on the Suppliers form.
||Control Name: CategoryID
||Go to the CategoryID control.