SearchForRecord Macro Action
You can use the SearchForRecord action to search for a specific record in a table, query, form or report.
The SearchForRecord action has the following arguments.
|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.
PreviousSearch backward from the current record.
NextSearch forward from the current record.
FirstSearch forward from the first record. This is the default value for this argument.
LastSearch backward from the last record.
|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,
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:
- 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 action is similar to the FindRecord action, but SearchForRecord has more powerful search features. The FindRecord action is primarily used for finding strings, and it duplicates the functionality of the Find dialog box. The SearchForRecord 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 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
CategoryIDmust 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.
The following macro first opens the Categories table by using the OpenTable action. The macro then uses the SearchForRecord action to find the first record in the table where the Description field equals "Beverages."
Table Name: Categories
Data Mode: Edit
Object Type: Table
Object Name: Categories
Where Condition: Description = "Beverages"