SetValue Macro Action

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.

Setting

The SetValue action has the following arguments.

Action argument Description
Item 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.
Expression

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.

Remarks

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.

ShowTip

You can use the SetValue action to hide or show a form in Form view. Enter Forms!formname.Visible in the Item box and No or Yes in the Expression box. Setting a modal (modal: A window or dialog box that requires the user to take some action before the focus can switch to another form or dialog box. Dialog boxes and messages are usually modal.) form's Visible property to No hides the form and makes it modeless. Setting the property to Yes displays the form and makes it modal again.

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.

Example

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.

Action Arguments: Setting Comment
Echo Echo On: No Stop screen updating while the macro is running.
Close

Object Type: Form

Object Name: Product List

Save: No

Close the Product List form.
OpenForm

Form Name: Products

View: Form

Data Mode: Add

Window Mode: Normal

Open the Products form.
SetValue

Item: [Forms]![Products]![SupplierID]

Expression: SupplierID

Set the SupplierID control to the current supplier on the Suppliers form.
GoToControl Control Name: CategoryID Go to the CategoryID control.
 
 
Applies to:
Access 2007