Microsoft Access 2007 tutorial: Recordset find next

Recordset find next

IV.5.3. Recordset.FindNext

Recordset.FindNext is used to search for a record in the Recordset object that satisfies the specific criteria. The search process starts from the current record of the Recordset to the end of the Recordset.

Syntax:

           RecordsetObject.FindNext(Criteria)

Criteria: It is like WHERE clause in the 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 has succeeded. If the search is found, the value of NoMatch property is False. In contrast, the value of NoMatch property is True (when the search is fail). However, you can use BOF property instead of Nomatch property in find the data.

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 through 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 record will display in the two text boxes. 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 listbox 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

End Sub

    Private Sub cmdSearch_Click()

If txtSearch.Value <> " " Then

   Rs.MoveFirst  ' Move to first record

    If Not Rs.BOF Then

        Rs.FindNext "CategoryID='" & txtSearch.Value & "'"

        Call Display

        Selectlist (Rs.AbsolutePosition)

        If Trim(txtCategoryID.Value) <> txtSearch.Value Then

           Call Cleardata

           LstResult.Selected(Rs.AbsolutePosition) = False

           MsgBox "The Record not found!"

        End If

    End If

Else: MsgBox "Please enter what you want to find."

End If

                OR

If txtSearch.Value <> "" Then

   Rs.MoveFirst  ' Move to first record

    Rs.FindNext "CategoryID='" & txtSearch & "'"

    Call Display

    Selectlist (Rs.AbsolutePosition)

    If Rs.NoMatch = True Then

       Call Cleardata

       LstResult.Selected(Rs.AbsolutePosition) = False

       MsgBox "The record not found!"

    End If

Else: MsgBox "Please enter what you want to find"

End If

   End Sub

Sub Cleardata()

' Clear data on form

txtCategoryID.Value = ""

txtCategoryName.Value = ""

End Sub

Sub PopListbox()

' Populate listbox
LstResult.ColumnCount = 2

LstResult.ColumnHeads = True

LstResult.RowSourceType = "Table/Query"

LstResult.RowSource = SqlText

End Sub

Sub Display()

' Display data on form
txtCategoryID.Value = Rs(0)

txtCategoryName.Value = Rs(1)

selectlist (Rs.AbsolutePosition)

End Sub

Sub Selectlist(i As Integer)

' Scrollable list
LstResult.SetFocus

LstResult.ListIndex = 0

LstResult.ListIndex = i

End Sub