Microsoft Access 2007 tutorial: RecordSet find previous

RecordSet Find previous

IV.5.3. Recordset.FindPrevious

Recordset.FindPrevious 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 beginning of the Recordset.



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 uses 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 finding 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:

Microsoft Access 2007 vba code to find the previous record 

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 found record will display in the two text boxes and be selected in the list box. See the figure:

Microsoft Access 2007 vba code find previous result 

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.MoveLast  ' Move to last record

     If Not Rs.BOF Then

         Rs.FindPrevious"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




 If txtSearch.Value <> "" Then

     Rs.MoveLast  ' Move to last record

      Rs.FindPrevious "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.ListIndex = 0

  LstResult.ListIndex = i

End Sub


This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.