FindRecord Macro Action

You can use the FindRecord action to find the first instance of data that meets the criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) specified by the FindRecord arguments. This data can be in the current record (record: A collection of data about a person, a place, an event, or some other item. Records are the logical equivalents of rows in a table.), in a succeeding or prior record, or in the first record. You can find records in the active table datasheet (datasheet: Data from a table, form, query, view, or stored procedure that is displayed in a row-and-column format.), query datasheet, form datasheet, or form.


The FindRecord action has the following arguments.

Action argument Description
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 (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.), 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 (field: An element of a table that contains a specific item of information, such as a last name. A Title field might contain Mr. or Ms. Databases such as Microsoft SQL Server refer to fields as columns.). 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 (case-sensitive: Capable of distinguishing between uppercase and lowercase letters. A case-sensitive search finds only text that is an exact match of uppercase and lowercase letters.). 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 (bound control: A control used on a form, report, or data access page to display or modify data from a table, query, or SQL statement. The control's ControlSource property stores the field name to which the control is bound.), 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 (macro: An action or set of actions that you can use to automate tasks.) 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 (Code window: A window in the Microsoft Visual Basic Editor in which you write, display, and edit Visual Basic code. You can open as many Code windows as you have modules.). You can't use the FindRecord action to search for text in modules (module: A collection of declarations, statements, and procedures stored together as one named unit. There are two types of modules: standard modules and class 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 (command button: A control that runs a macro, calls a Visual Basic function, or runs an event procedure. A command button is sometimes called a push button in other programs.) 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 (focus: The ability to receive user input through mouse or keyboard actions or the SetFocus method. Focus can be set by the user or by the application. The object that has focus is usually indicated by a highlighted caption or title bar.) 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.

Security  Avoid using the SendKeys statement or an AutoKeys macro with sensitive or confidential information. A malicious user could intercept the keystrokes and compromise the security of your computer and data.

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.

Applies to:
Access 2007