SearchForRecord Macro Action

You can use the SearchForRecord macro action in Access desktop databases to search for a specific record in a table, query, form or report.

Setting

The SearchForRecord macro action has the following arguments.

Action argument	Description
Object Type	Enter or select the type of database object that you are searching in. You can select Table, Query, Form, or Report.
Object Name	Enter or select the specific object that contains the record to search for. The drop-down list shows all database objects of the type you selected for the Object Type argument.
Record	Specify the starting point and direction of the search. Setting Description Previous Search backward from the current record. Next Search forward from the current record. First Search forward from the first record. This is the default value for this argument. Last Search backward from the last record.
Where Condition	Enter the criteria for the search using the same syntax as an SQL WHERE clause, only without the word "WHERE". For example, Description = "Beverages" To create a criterion that includes a value from a text box on a form, you must create an expression that concatenates the first part of the criterion with the name of the text box containing the value for which to search. For example, the following criterion will search the Description field for the value in the text box named txtDescription on the form named frmCategories. Note the equal sign (=) at the beginning of the expression, and the use of single quotation marks (') on either side of the text box reference: ="Description = '" & Forms![frmCategories]![txtDescription] & "'"

Remarks

• In cases where more than one record matches the criteria in the Where Condition argument, the following factors determine which record is found:

  • The Record argument setting    See the table in the Settings section for more information about the Record argument.

  • The sort order of the records    For example, if the Record argument is set to First, changing the sort order of the records might change which record is found.

• The object specified in the Object Name argument must be open before this action is run. Otherwise, an error occurs.

• If the criteria in the Where Condition argument are not met, no error occurs and the focus remains on the current record.

• When searching for the previous or next record, the search does not "wrap" when it reaches the end of the data. If there are no further records that match the criteria, no error occurs and the focus remains on the current record. To confirm that a match was found, you can enter a condition for the next action, and make the condition the same as the criteria in the Where Condition argument.

• To run the SearchForRecord action in a VBA module, use the SearchForRecord method of the DoCmd object.

• The SearchForRecord macro action is similar to the FindRecord macro action, but SearchForRecord has more powerful search features. The FindRecord macro action is primarily used for finding strings, and it duplicates the functionality of the Find dialog box. The SearchForRecord macro action uses criteria that are more like those of a filter or an SQL query. The following list demonstrates some things you can do with the SearchForRecord macro action:

  • You can use complex criteria in the Where Condition argument, such as

Description = "Beverages" and CategoryID = 11

• You can refer to fields that are in the record source of a form or report but aren't displayed on the form or report. In the preceding example, neither Description nor CategoryID must be displayed on the form or report for the criteria to work.

• You can use logical operators, such as <, >, AND, OR, and BETWEEN. The FindRecord action only matches strings that equal, start with, or contain the string being searched for.

Example

The following macro first opens the Categories table by using the OpenTable action. The macro then uses the SearchForRecord macro action to find the first record in the table where the Description field equals "Beverages."

Action	Arguments
OpenTable	Table Name: Categories View: Datasheet Data Mode: Edit
SearchForRecord	Object Type: Table Object Name: Categories Record: First Where Condition: Description = "Beverages"