FindFirst Recordset Example

The FindFirst recordset function is often used in programming Access VB to locate a record based on criteria entered by the user.

The Findfirst command is also useful for coordinating forms that are not part of a master/detail relationship. In the Access recordset findfirst example below we have a form with all the personal details of prospective clients.  We have a VB combo box which lists all the prospects in the database and allows the user to select a particular prospect and then have the form automatically go to that person's data record.

Here's the VBA FindFirst code:

Private Sub Find_Combo_AfterUpdate()
Dim rst As DAO.Recordset
Dim strCriteria As String

strCriteria = "[Prospect_ID] =" & Me.Find_Combo
Set rst = Form_F_Prospects.RecordsetClone

rst.FindFirst (strCriteria)
    If rst.NoMatch Then
        MsgBox "No entry found"
    Else
        Form_F_Prospects.Bookmark = rst.Bookmark
    End If

Set rst = Nothing
DoCmd.GoToControl "Find_Combo"

End Sub

Below is a short cut method which accomplishes the same Visual Basic findfirst function:

Private Sub Find_Combo_AfterUpdate()
Dim strCriteria As String

strCriteria = "[Prospect_ID] =" & Me.Find_Combo

Me.recordsetclone.FindFirst (strCriteria)
If me.recordsetclone.NoMatch Then
    MsgBox "No entry found"
Else
    Form_F_Prospects.Bookmark = me.recordsetclone.Bookmark
End If

End Sub

Note: A common mistake is to forget the quote marks when using the findfirst command with text data.  For example, if the Prospect_ID was a text field rather than a numeric field then the assignment statement for setting the strCriteria variable would look like this:

strCriteria = "[Prospect_ID] ='" & Me.Find_Combo & "'"

Note the single quote marks (in red color) wrapped around the Me.Find_Combo field value.

Also see the MS Access DoCmd.FindRecord command example