SetValue Action

Microsoft Access Visual Basic

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 window. 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 was called) or Forms!formname!controlname. This is a required argument. Expression The expression 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.

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 (.mdb), click the Build button Button image to the right of the Item or Expression box 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.

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 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 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, 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 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 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 SetValue 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 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 Visual Basic.

This action isn't available in Microsoft Visual Basic. Set the value directly in Visual Basic.

Example

Show 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. Then the GoToControl action 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 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.