Recordset: find record

In this example, you will learn Recordset Method to find record in a table. When the record is found, that record will be selected in a list box. The example illustrates a Search form that has a text box(Search), a list box(lstData), a command buttons(Search). See the figure below:

 VBA example - Microsoft Access VBA find record form  

-When the form loads, the cursor sets focus in Search text box and all records are populated in the list box .The first record be selected in the list box automatically(See Search form above).

-When you enter ProductID(P003) and click Search command button, that record will be selected in the list box. See the figure below:

VBA example - Microsoft Access VBA Search Form1 

-If you click Search command button without entering ProductID in the text box, one message box will appear:

VBA example - Microsoft Access VBA Message Box product id confirm 

-When the form cannot find the ProductID you entered, another message box will appear:

VBA example - Microsoft Access VBA Message product id can't be found 

After you clicked Ok command button of message box, the data in the text box is selected and  the list box is unselected. See the figure below:

VBA example - Microsoft Access VBA select found record 

To have a form as the figure above, you have to create the form in Form Design. See the figure below:

VBA example - Microsoft Access VBA Search Form Design 

Note: Unselect Use Control Wizards before you drag these controls to the form.

- Drag and drop a text box, a list box and a command buttons.

- Set Name properties of three controls:

Name: txtSearch

Name: lstData

Name: CmdSearch

Caption: Search

 

Then apply the VBA Code below:

 

Option Compare Database

Dim Rs As dao.Recordset

 ...............................................................................................

Private Sub Form_Load()

    Set Rs = CurrentDb.OpenRecordset("TblProduct", dbOpenDynaset)

    lstData.RowSourceType = "Table/Query"

    lstData.ColumnHeads = True

    lstData.ColumnCount = 4

    lstData.RowSource = "Select * from TblProduct"

    SelectList (Rs.AbsolutePosition)

    txtSearch = ""

    txtSearch.SetFocus

End Sub

 ...............................................................................................

Private Sub cmdSearch_Click()

    If txtSearch <> "" Then

        Rs.FindFirst "ProductID='" & txtSearch & "'"

        SelectList (Rs.AbsolutePosition)

        If Rs.NoMatch = True Then

            MsgBox "The record not found.", vbInformation

            lstData.ListIndex = -1

            txtSearch.SetFocus

            txtSearch.SelLength = Len(txtSearch)

        End If

    Else

    MsgBox "Enter what you want to find.", vbInformation

    txtSearch.SetFocus

    End If

End Sub

 ...............................................................................................

Sub SelectList(i As Integer)

    lstData.SetFocus

    lstData.ListIndex = 0

    lstData.ListIndex = i

End Sub


HTML Comment Box is loading comments...



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.