MS-Access / Getting Started

---

Filtering Records

You can create a macro to limit the records you want to print by adding a Where Condition to the OpenReport action. For example, suppose that you want to preview the Alpha Entry records for all incidents with a Code in the danger range, 11000 to 19999. Start a new macro with the Macro Builder from an Event property or from the Create tab and do the following:

1. Choose OpenReport in the Action column.
2. In the Report Name argument, select Alpha Entries from the list of available reports.
3. Choose Print Preview as the View argument.
4. Enter [Alpha Entry]![Code] Between 11000 And 19999 in the Where Condition argument or click the Build button to get help from the Expression Builder.
   TIP Don't use an equal sign in the Where Condition argument.
5. Save the macro and then click Run.

You can see in the Print Preview that only three of the incidents reported fall within the danger range.

Setting Values and Properties

SetValue is a useful macro action that sets the value of a field, control, or property of a form, a form datasheet, or a report. You can set a value for almost any control, form, and report property in any view with the SetValue action.

NOTE: If you don't see the SetValue action in the drop-down action list, on the Design tab's Show/ Hide group, click the Show All Actions command.

The action has two arguments, both required: Item and Expression.

• The Item argument contains the name of the field, control, or property whose value you want to set. When you enter the name in the Item argument box, you use the control name if the control is on the form or report from which the macro is called. If the control is on another form or report, you must use the full identifier including the form or report name: [Forms!] formname!controlname. If you're setting the value of a property, add the property name to the identifier preceded by a period: [Forms!] formname!controlname.property. If it's a form or report property, omit the control name and the preceding exclamation point (!).
• The Expression argument contains the value you want to set for the item. Again, use full syntax when referring to any Access objects in the expression. Use the Expression Builder if you need help. Don't precede the expression with an equal sign.

Setting Control Values

In addition to entering the value itself, you can set the value of a control based on the value of another control in the same or a different form or report. You can also use the result of a calculation or the value returned by an option group to set the value of a control.

For example, when you're adding new records to a person's file in the Alpha Entry recordset in the subform of the Alpha Card form, you can compute the value of the Purge field. Depending on the Code value, the entry might be purged from the person's file after a certain length of time-seven years in this example. To save data entry time, you can write a macro that examines the Code value and uses the DateAdd function to set the Purge date by adding a specified number of years to the Date field value.

NOTE: The first argument in the DateAdd function, yyyy, indicates that the interval you want to increment is the year part of the date value. The second argument is the number of years to add, and the third names the control that contains the original date.

Setting Control Properties

Many of the properties of forms, reports, and controls can be set by running a macro. For example, you can hide a control from view on the form or disable it so that the user can't enter data in it. You can also change colors, fonts, and other appearance properties.

As an example of setting a property with a macro, in the Police database, you can disable the Drivers_License control if the subject of the Alpha Card report is younger than age 16. To do this, set the Enabled property to No. When a control is disabled, it still appears on the screen, but it's dimmed and you can't reach it by pressing tab or by clicking it.

To make sure that you enter the correct identifier, you can use the Expression Builder. After adding the SetValue action to the macro, click Build (...) next to the Item argument to open the Expression Builder, and then do the following:

1. Double-click the Forms folder, and then double-click the All Forms folder in the left panel to open the list of forms in the current database.
2. Choose the Alpha Card form. A list of all controls and labels in the form appears in the center panel.
3. Choose Drivers_License. A list of all the properties that apply to the Drivers_ License text box control appears in the right-most column.
4. Choose Enabled from the list of properties and click Paste. When you click OK, the expression is placed in the upper panel in the Expression Builder.

To complete the macro, click OK to return to the macro design window and enter No in the Expression argument and add a condition to the action row that runs the macro only if the Age value is less than 16. If you are not already working from the Property Sheet to embed the macro, attach the macro to the Age control's After Update event property.

You will probably want to add another macro to reenable the Drivers License control when you move to the next record.

If the property value is a string expression, enclose it in quotation marks in the Expression argument box.

To set report section properties, refer to the section by its number.