Access VBA Findfirst
Written by
Reviewed by
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:
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:
