VBA Access Find Record: FindFirst, FindRecord, and More

To find and move to a matching record in an Access form, use its RecordsetClone with FindFirst, check NoMatch, and copy the found record’s bookmark to the form. For example, rs.FindFirst "[CustomerID] = 42" followed by Me.Bookmark = rs.Bookmark navigates to customer 42; use DoCmd.FindRecord instead when you want Access’s interactive Find behavior.

Two ways to find a record in Access VBA

The right method depends on whether you need to navigate a form reliably or reproduce the Find dialog’s user-interface behavior. Both methods search records, but they work at different levels.

RecordsetClone.FindFirst: Searches the records in a form’s recordset. When a match exists, assigning the result’s bookmark to the form moves the form to that row. This is generally the clearest option for a search button or other form logic.
DoCmd.FindRecord: Runs Access’s Find Record command. Its result depends on the active form, focus, current field, and search settings. Use it when you want behavior similar to the Find interface, not when your code needs a straightforward success-or-failure result.

A search does not automatically guarantee a unique result. If several rows match, the recordset method finds the first match in the recordset’s current order. For a precise lookup, search a primary key or another field that is actually unique.

Use FindFirst to navigate a bound form

For a form bound to a table or query, Me.RecordsetClone gives the code a separate recordset representing the form’s records. Calling FindFirst searches that recordset without changing the form’s current row. If a match is found, the clone and form can be synchronized using their bookmarks. The form then displays the matching record.

This example assumes the form has a text box named txtFind and a field named CompanyName in its record source. It searches for an exact company-name match, including names with apostrophes:

Private Sub cmdFind_Click()
    ' Search the form's records and move to an exact company-name match.
    Dim rs As DAO.Recordset
    Dim searchText As String
    Dim criteria As String

    searchText = Nz(Me.txtFind.Value, "")
    If Len(searchText) = 0 Then
        MsgBox "Enter a company name to find."
        Exit Sub
    End If

    criteria = "[CompanyName] = '" & Replace(searchText, "'", "''") & "'"
    Set rs = Me.RecordsetClone
    rs.FindFirst criteria

    If rs.NoMatch Then
        MsgBox "No matching company was found."
    Else
        Me.Bookmark = rs.Bookmark
    End If

    rs.Close
    Set rs = Nothing
End Sub

The brackets mark the field name, while doubled apostrophes keep a search value such as O'Brien inside a valid text criterion. The code uses Nz so a Null text-box value becomes an empty string rather than causing a type mismatch. Replace the control and field names with the names in your own form. The field must be included in the form’s record source.

Build valid FindFirst criteria

FindFirst takes a criteria expression, similar to the condition after a SQL WHERE clause. The field’s data type determines how the value should be written. A string criterion needs single quotes around the value; a number does not. Bracketing field names is a useful habit, especially when a name contains spaces or overlaps with a reserved word.

Text: [LastName] = 'Nguyen'. If the value comes from a user, double any apostrophes before joining it into the criterion.
Number: [CustomerID] = 42. Do not surround numeric values with text quotes.
Date: Use Access date delimiters, #, around a date value. Date interpretation can depend on regional settings, so format or construct the criterion carefully rather than assuming every computer parses a typed date the same way.
Null: Use [MiddleName] Is Null. An equals comparison with Null does not test for a missing value.

For partial text matches, use a Like expression, for example [CompanyName] Like 'North*' in a database using Access’s ANSI-89 wildcard convention. Access databases can use ANSI-92 conventions instead, where the wildcard characters differ. Check the database’s query and wildcard settings before relying on * or ?; exact equality is simpler when a partial match is not required.

Use DoCmd.FindRecord for interactive searches

DoCmd.FindRecord invokes Access’s Find Record action, which is useful for a quick search of the active form. Its optional arguments control whether the match is anywhere in a value, at its start, or the whole value; whether to search up, down, or all records; and whether Access searches the current field or all fields. It can also search formatted display text rather than the underlying value.

Here is a minimal example that searches all fields from the first record for a term:

Private Sub cmdFindInteractive_Click()
    ' Use Access's Find Record command on the active form.
    Dim term As String

    term = Nz(Me.txtFind.Value, "")
    If Len(term) = 0 Then Exit Sub

    DoCmd.FindRecord term, acAnywhere, False, acSearchAll, _
                     False, acAll, True
End Sub

In this call, acAnywhere allows the term to occur within a value, acSearchAll searches all records, acAll searches all fields, and the final True begins at the first record. With SearchAsFormatted set to False, Access searches the underlying value rather than its displayed formatting. These options do not make the command return a dependable Boolean result that your code can use to distinguish found from not found. For controlled navigation and explicit handling of a miss, prefer FindFirst and NoMatch.

FindFirst versus FindRecord

The key difference is control. The recordset approach gives the code an explicit test for a match and works naturally with a form’s record source. FindRecord behaves more like a user command: the active object and focus can affect what gets searched. If a text box has focus, a current-field search may search that field rather than every column. If a different form is active, the command may act on that form.

Choose FindFirst when a button must locate a specific customer, validate whether a record exists, or move to a match after a user enters a value. Choose DoCmd.FindRecord when the desired behavior is an interactive find through the active form, including moving to subsequent matches. Neither method changes the underlying data just by searching. To edit a found record, assign values to bound controls or fields only after checking that the intended row is selected.

Search limits, ordering, and performance

A recordset search only sees the records available in that recordset. A form filter, its record source, or a query condition may exclude the row you expect. Clear or adjust the filter, or change the form’s record source, if the target is outside the current set. Also check whether a control is bound to the field you intended to search; a label or an unbound display control is not itself a searchable field in the recordset.

When duplicates exist, FindFirst returns the first matching row according to the recordset’s current ordering. That ordering may not be the order users expect unless the form or query sorts it explicitly. Searching a primary key avoids ambiguity. For a large table or frequent lookups, a query restricted by an indexed key is often more efficient than repeatedly scanning a broad form recordset. An index can improve searches on that field, but it does not make a non-unique field unique.

Searching formatted values also needs care. A number displayed as currency or a date shown in a custom format may not look like its stored value. With DoCmd.FindRecord, the formatted-search option changes which representation is compared. With recordset criteria, use the actual field value and its data type. A value displayed as $1,200.00, for instance, is still a numeric value, not necessarily text containing a dollar sign and commas.

Common problems and fixes

“Too few parameters” or a criteria error: Confirm the field name is spelled correctly and is present in the form’s record source. Bracket names containing spaces, and make sure text values use quotes while numbers do not.
The search finds nothing unexpectedly: Check for leading or trailing spaces, the exact field value, active filters, and the field’s data type. If searching a formatted value, compare the displayed representation with the stored one.
The form does not move: Confirm the code assigns Me.Bookmark = rs.Bookmark after checking rs.NoMatch. A form without a usable recordset, or a record excluded by its filter, cannot navigate to that row through this clone.
Apostrophes break a text criterion: Double embedded apostrophes with Replace(value, "'", "''") before assembling a quoted text criterion.
The wrong duplicate is selected: Search a unique key, or deliberately define the recordset’s sort order before searching. A non-unique match does not identify one particular row.

Do not treat text typed into a criterion as harmless simply because it comes from a search box. Quote and escape text values correctly, and avoid building complex SQL from unchecked input. For more involved searches, parameterized queries provide a cleaner way to separate values from SQL syntax.

When to use a query or another lookup

Finding a row in the form’s existing recordset is convenient when the form already contains the records users may browse. If the form must open directly on a particular row, DoCmd.OpenForm can open it with a WhereCondition that limits the displayed records. This is different from searching through every row in an already-open form. Build the condition with the correct data type and escape text input.

A query with a WHERE condition is a better fit when you need a reusable result set, several criteria, or a large-data lookup. DLookup can retrieve one field’s value from a table or query, but it does not navigate a form to a record. It also returns a single value, so it is not a substitute for a recordset when the code needs several fields or must edit the selected row. Choose based on the outcome: navigate, filter, retrieve a value, or produce a result set.

Frequently Asked Questions

How do I find a record by ID in an Access form?
Use the form’s recordset clone and search a numeric key with a criterion such as [CustomerID] = 42. If NoMatch is False, assign the clone’s bookmark to the form’s bookmark.

What does NoMatch mean in Access VBA?
After a recordset’s FindFirst call, NoMatch is True when the search found no record. Test it before using the clone’s bookmark; otherwise, your code may try to navigate without a valid match.

Can DoCmd.FindRecord search every field?
Yes. Set its OnlyCurrentField argument to acAll to search all fields. The active form and its current search context still matter, so use a recordset search when you need predictable field-specific logic.

Does FindFirst return every matching record?
No. FindFirst positions its recordset at the first match. To find later matches, use FindNext with the same criteria, or use a query that returns all rows meeting the condition.

Can a search macro be undone with Ctrl+Z?
No. Access does not provide Ctrl+Z as a general undo for actions performed by a VBA macro. The examples above only navigate, but save a copy of the database before running a macro that may change or delete data.

Safe testing and a practical rule

Test the search on a copy of the database and include cases for a match, no match, an empty search box, a duplicate, and text containing an apostrophe. Navigation-only code should not edit a row, but macros that perform updates or deletes can have lasting effects and cannot be rolled back with Ctrl+Z. The practical rule is simple: use RecordsetClone.FindFirst plus NoMatch and bookmark assignment for dependable form navigation; reserve DoCmd.FindRecord for interactive Find-style behavior.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.