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.
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, _
An important choice when doing a search is whether the match should be limited to the complete value of the field:
Alternatively, you can search for data located at the beginning of the field,
acStart, or data in any part of the field,
If you don't specify this argument Access uses
Match:=acEntire which is quite restrictive.
OnlyCurrentField:=acCurrent search will specifically be done on the field that currently has the focus.
OnlyCurrentField:=acAllsearches in all fields in each record
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.
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.
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
If varBookmark = Me.Bookmark Then
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:
- The button is placed on the Header or Footer section of a form - or
- 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
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,
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 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.