Access VBA Findfirst

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on July 9, 2022

In this tutorial, you will learn how to find the first record that meets a certain input criteria.

We have a table called ProductsT shown below:

Access Source Table

Using FindFirst with VBA

The syntax of the .FindFirst method is expression.FindFirst(criteria) where:

expression –  the Recordset of interest.

Criteria – a string that is used to identify a record. It is similar to the WHERE clause in SQL.

Note: We have to use the Recordset.FindFirst method in combination with an IF ELSE statement and the .NoMatch method. This tells VBA what to do if a match is found.

The following code will show you how to find the first product name whose price is greater than $15:

Sub UsingFindFirst()

Dim ourDatabase As Database
Dim ourRecordset As Recordset

Set ourDatabase = CurrentDb
Set ourRecordset = ourDatabase.OpenRecordset("ProductsT", Type:=RecordsetTypeEnum.dbOpenDynaset)

With ourRecordset

   .FindFirst "ProductPricePerUnit" & ">15"
If .NoMatch Then

MsgBox "No Match Found"
MsgBox "The product has been found and its name is: " & ourRecordset!ProductName

End If
End With

DoCmd.Close acTable, "ProductsT", acSaveNo
DoCmd.OpenTable "ProductsT"

End Sub

The result is:

Using FindFirst in Access VBA to Match a Record


VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples