FindRecord Action

Microsoft Access Visual Basic

Show All Show All

FindRecord 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.

Setting

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, which is preceded by an equal sign (=), in the Find What box in the Action Arguments section of the Macro window. 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 Access searches for the data as it's formatted and displayed in the field) or No (Access searches for the data as it's 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's formatted, such as 09-March-2001. 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 3/9/01, and Access will find all entries in a Date field that correspond to March 9, 2001, 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. The current field search 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.

Remarks

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 Edit menu, and its arguments are the same as the options in the Find In Field dialog box, available by clicking Find on the Edit menu. If you set the FindRecord arguments in the Macro window and then run the macro, you'll see the corresponding options selected in the Find In Field 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 FindRecord operations. 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 In Field 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 Find on the Edit menu for tables, queries, and forms, it doesn't correspond to Find 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 macro 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.

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 Visual Basic, use the FindRecord method of the DoCmd object.