Access VBA Findfirst

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:

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"
Else
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: