Findfirst

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

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 MSAccess DoCmd.FindRecord command example

 

Microsoft Office:
MS Access 2000 Through 2016 and Office 365 & Sharepoint

 

Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016