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: