Findfirst Recordset Function

Findfirst Recordset Function

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

Visual Basic Tutorials:

Access-Google Earth Age Calculation Change To Proper Case Email via Gmail #1
Send Gmail Email #2 Inactivity Logout Code Outlook Email Read Email Access
Email Attachment Send Outlook Email Running Sum Denormalize Records
Stock Quotes CreateQueryDef Find Database Path Detail-Master Update
Data Field Validation Field Value New-Old FindFirst Access Version
Global Variable Parameter Global Variables Active Labels Files List Box
Mail Merge Quick Sort Select Case Access Transactions