Using DoCmd.FindRecord

A basic requirement for a database is to help the user find the record he is looking for. In the record navigator pane at the bottom of a form or datasheet a Search box has been added. If you type a search string in this box and press enter, it will find the first record which has a field that contains the string, or part of it, in one of the fields.

record navigator

If you want more control on the way the search is executed you can use DoCmd.FindRecord. As an example you might want to limit search to a certain preselected field, e.g. search on postal code


Private Sub cmdFind_Click()
On Error GoTo HandleError
Dim strFindWhat As String: strFindWhat =
DoCmd.FindRecord FindWhat:=strFindWhat, Match:=acEntire, MatchCase:=False, _
                Search:=acSearchAll, SearchAsFormatted:=False, _
                OnlyCurrentField:=acCurrent, FindFirst:=True

HandleExit:
Exit Sub
HandleError:
MsgBox Err.Description
Resume HandleExit
End Sub
menu find record

Specify what to look for - Match

An important choice when doing a search is whether the match should be limited to the complete value of the field: Match:=acEntire. Alternatively, you can search for data located at the beginning of the field, acStart, or data in any part of the field, acAnywhere. If you don't specify this argument Access uses Match:=acEntire which is quite restrictive.

Search in current or all fields

With OnlyCurrentField:=acCurrent search will specifically be done on the field that currently has the focus. OnlyCurrentField:=acAllsearches in all fields in each record

Search start point and direction

Set argument FindFirst:=True to start the search at the first record. Use False to start the search at the record following the current record. If you leave this argument blank, the default (True) is assumed.

The argument Search specifies the direction to search. acDown searches all records below the current record, acUp all records above the current record. The default value is acSearchAll.

Determine if record was found

The FindRecord method does not return a value indicating its success or failure. However you can detect if a record was found by checking if the Bookmark property of the form changed


Dim varBookmark As Variant, strFindWhat As String
varBookmark = Me.Bookmark
DoCmd.FindRecord FindWhat:=strFindWhat
If varBookmark = Me.Bookmark Then
    'unchanged
Else
    'changed
End If

Start DoCmd.FindRecord from menu, ribbon or button

When you start DoCmd.FindRecord from a menu or ribbon the focus remains on the control on the form or datasheet. If this control is bound to a field of the datasource the FindRecord can be successfully executed.

Error 2162: A macro set to one of the current field's properties failed because of an error in a FindRecord action argument.

This error may occur when starting DoCmd.FindRecord from a button under one of the following conditions:

  1. The button is placed on the Header or Footer section of a form - or
  2. DoCmd.FindRecord has argument OnlyCurrentField:=acCurrent

A commonly used solution to the problem of the Find button having received focus is to set focus to the control that previously had the focus using Screen.PreviousControl.SetFocus. However this solution fails if the previous control was not a control bound to the recordsource, so it will not always work. The correct solution is to explicitly give a control on the detailsform the focus:


Dim strFindWhat As String: strFindWhat = "Ani"
Me![Name of a control].SetFocus
DoCmd.FindRecord FindWhat:=strFindWhat, Match:=acStart, MatchCase:=False,
OnlyCurrentField:=acCurrent

Note: If the button is placed on the Details section of a bound form using DoCmd.FindRecord with argument OnlyCurrentField:=acAll there is no need to correct the focus.

As is generally the case, you are advised add error handling code to your DoCmd.FindRecord call as in the code at the top of the page.

Alternatives to DoCmd.FindRecord

DoCmd.RunCommand acCmdFind

DoCmd.RunCommand acCmdFind opens the Find and Replace dialog. This has the advantage that the user may refine search criteria. However this approach gives a lower level of integration with your user interface.

DoCmd.RunCommand acCmdFind opens the Find and Replace dialog