Recordset.FindLast is used to search for a record in the Recordset object that satisfies the specific criteria. The search process starts from the end of the Recordset to the beginning of the Recordset.
Criteria: It is like WHERE clause in an SQL statement, but in this syntax the word WHERE is not used. Criteria (must) has a data type as string.
Note: This method always use with NoMatch property to determine whether the process of finding is successful. If the search target is found, the value of NoMatch property is False. In contrast, the value of NoMatch property is True. However, you can use BOF property instead of Nomatch property in finding the record.
If the Recordset contains more than one record that satisfies the criteria, the first occurrence is returned.
For example, you have the frmModifyData form as figure:
This form allows you to find the existing record by CategoryID only. If you want to find any record(e.g. you would like to find Cate004. Does it exist in the table or not?), you have to type CategoryID(not case-sensitive) in the Search text box and click the Search command button. The matched record will display in the two text boxes and be selected in the list box. See the figure:
The VBA code below explicates the example. You can use either BOF or NoMatch property as in demonstrative VBA code below:
Option Compare Database
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim SqlText As String
Private Sub Form_Load()
' Display the first record on form and drop all records to list box with selection
Set Db = CurrentDb
SqlText = " Select * From TblCategory"
Set Rs = Db.OpenRecordset(SqlText, dbOpenDynaset)
Call Display ' Call display sub procedure
Selectlist (Rs.AbsolutePosition) ' Call selectlist sub procedure
Call PopListbox ' Call PopListbox sub procedure
cmdUpdate.Enabled = False
cmdDelete.Enabled = False
Private Sub cmdSearch_Click()
If txtSearch.Value <> " " Then
If Not Rs.BOF Then
Rs.FindLast "CategoryID='" & txtSearch.Value & "'"
If Trim(txtCategoryID.Value) <> txtSearch.Value Then
LstResult.Selected(Rs.AbsolutePosition) = False
MsgBox "The Record not found!"
Else: MsgBox "Please enter what you want to find."
If txtSearch.Value <> "" Then
Rs.FindLast "CategoryID='" & txtSearch & "'"
If Rs.NoMatch = True Then
MsgBox "The record not found!"
Else: MsgBox "Please enter what you want to find"
' Clear data on form
txtCategoryID.Value = ""
txtCategoryName.Value = ""
Sub PopListbox() ' Populate listbox
This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages: