Using Parameters

In named data macros, you can define parameters to pass in information to the named data macro and use them in the data blocks and data actions. With parameters, you can pass in information to the named data macro from other data macros, forms, user interface macros, or Visual Basic. In the Back Office Software System sample web database, many of the named data macros attached to the web tables include parameters. Let's study an example that uses dates for parameters. Open the tblSchedule in Datasheet view. Next, click the Table contextual ribbon tab under Table Tools. Finally, click the Named Macro button in the Named Macros group, click Edit Named Macro in the drop-down list, and then click DeleteDateRangeRecords.

Access opens the Logic Designer and displays the logic that we created for this named data macro.

The logic for the DeleteDateRangeRecords named data macro is as follows:

Parameter Name: ParamStartDateDelete
Parameter Description: Beginning date to delete records
Parameter Name: ParamEndDateDelete
Parameter Description: Ending date to delete records
Comment Block: This named data macro deletes all records in tblSchedule within a specific
date range. The start and end dates for the range are given by parameters.
ForEachRecord In tblSchedule
 Where Condition = [ScheduleDate]>=[ParamStartDateDelete] And
		   [ScheduleDate]<=[ParamEndDateDelete]
 Alias
 DeleteRecord
    Record Alias

At the top of the macro design surface, you can see two parameters that we created. You must provide a name for each parameter that you create, up to 64 characters in length, but the description is optional. We recommend that you enter a description for each parameter that you create, for documentation purposes. To create a parameter for a named data macro, click the Create Parameter link in the upper-right corner of the Parameters block. Access adds an additional parameter line in the Parameters block.

This named data macro deletes all records in the tblSchedule table, but only within a specific date range. The date range is determined by the two date parameters. In the Where condition argument for the ForEachRecord data block, we use these parameter values just like table fields by adding brackets around them. Our Where condition is as follows:

By using parameters, this named data macro can be very flexible because we are not hardcoding specific dates into the logic. We can use this named data macro at any time in the application by passing in any date range, and Access takes care of the work by deleting only the records in the tblSchedule table within that date range.