FindRecord Macro Action
You can use the FindRecord action to find the first instance of data that meets the criteria specified by the FindRecord arguments. This data can be in the current record, in a succeeding or prior record, or in the first record. You can find records in the active table datasheet, query datasheet, form datasheet, or form.
The FindRecord action has the following arguments.
|Find What||Specifies the data you want to find in the record. Enter the text, number, or date you want to find or type an expression, which is preceded by an equal sign (=), in the Find What box in the Action Arguments section of the Macro Builder pane. You can use wildcard characters. This is a required argument.|
|Match||Specifies where the data is located in the field. You can specify a search for data in any part of the field (Any Part of Field), for data that fills the entire field (Whole Field), or for data located at the beginning of the field (Start of Field). The default is Whole Field.|
|Match Case||Specifies whether the search is case-sensitive. Click Yes (conduct a case-sensitive search) or No (search without matching uppercase and lowercase letters exactly). The default is No.|
|Search||Specifies whether the search proceeds from the current record up to the beginning of the records (Up); down to the end of the records (Down); or down to the end of the records and then from the beginning of the records to the current record, so all records are searched (All). The default is All.|
|Search As Formatted||Specifies whether the search includes formatted data. Click Yes (Microsoft Office Access 2007 searches for the data as it is formatted and displayed in the field) or No (Access searches for the data as it is stored in the database, which isn't always the same as it's displayed). The default is No.
You can use this feature to restrict the search to data in a particular format. For example, click Yes and type 1,234 in the Find What argument to find a value of 1,234 in a field formatted to include commas. Click No if you want to type 1234 to search for the data in this field.
To search for dates, click Yes to find a date exactly as it is formatted, such as 08-July-2003. If you click No, enter the date for the Find What argument in the format that is set in the regional settings in Windows Control Panel. This format is shown in the Short date format box found on the Date tab in the regional settings. For example, if the Short date format box is set to M/d/yy, you can enter 7/8/03, and Access will find all entries in a Date field that correspond to July 8, 2003, regardless of how this field is formatted.
Note The Search As Formatted argument takes effect only if the current field is a bound control, the Match argument is set to Whole Field, the Only Current Field argument is set to Yes, and the Match Case argument is set to No.
If you set Match Case to Yes or Only Current Field to No, you must also set Search As Formatted to Yes.
|Only Current Field||Specifies whether the search is confined to the current field in each record or includes all fields in each record. Searching in the current field is faster. Click Yes (confine the search to the current field) or No (search in all fields in each record). The default is Yes.|
|Find First||Specifies whether the search starts at the first record or at the current record. Click Yes (start at the first record) or No (start at the current record). The default is Yes.|
When a macro runs the FindRecord action, Access searches for the specified data in the records (the order of the search is determined by the setting of the Search argument). When Access finds the specified data, the data is selected in the record.
The FindRecord action is equivalent to clicking Find on the Home tab, and its arguments are the same as the options in the Find and Replace dialog box. If you set the FindRecord arguments in the Macro Builder pane and then run the macro, you will see the corresponding options selected in the Find and Replace dialog box when you click Find.
Access retains the most recent FindRecord arguments during a database session so that you don't need to enter the same criteria repeatedly as you perform subsequent operations with the FindRecord action. If you leave an argument blank, Access uses the most recent setting for the argument, as set either by a previous FindRecord action or in the Find and Replace dialog box.
When you want to find a record by using a macro, use the FindRecord action, not the RunCommand action with its argument set to run the Find command.
Note While the FindRecord action corresponds to the Find command on the Home tab for tables, queries, and forms, it doesn't correspond to the Find command on the Edit menu in the Code window. You can't use the FindRecord action to search for text in modules.
If the currently selected text is the same as the search text at the time the FindRecord action is carried out, the search begins immediately following the selection in the same field as the selection, and in the same record. Otherwise, the search begins at the start of the current record. This enables you to find multiple instances of the same search criteria that might appear in a single record.
However, note that if you use a command button to run a macro containing the FindRecord action, the first instance of the search criteria will be found repeatedly. This behavior occurs because clicking the command button removes the focus from the field containing the matching value. The FindRecord action will then begin searching from the start of the record. To avoid this problem, run the macro by using a technique that doesn't change the focus, such as a custom toolbar button or a key combination defined in an AutoKeys macro, or set the focus in the macro to the field containing the search criteria before you carry out the FindRecord action.
The same behavior also occurs if you use a command button to run a macro containing the FindNext action.
To run the FindRecord action in a Visual Basic for Applications (VBA) module, use the FindRecord method of the DoCmd object.
For more complex searches, you may want to use the SearchForRecord macro action.