VBA example - Microsoft Access:Recordset method to find record

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




Comments

Nik comment

 Nik

Thanks for you advise, I created the table with sample data per the example and then tried the form.

05-14-2014
Dara comment

 Dara

You need to create a table called TblProduct that contains the following fields: ProductID, ProductName, Quantity, and UnitPrice with some sample data.

05-14-2014
Nik, email:nkarpak@gmail.com comment

 Nik, email:nkarpak@gmail.com

I tried the above form but it didn't work. When I opened the form it got the run time error '424': object required and highlighted following code:
lstData.RowSourceType = "Table/Query"

I'm using Access 2007 and just started VBA. I appreciate if you can help me to fix this error.

05-13-2014
Name:
Comment:
Enter the text:
CAPTCHA image

.........................................................................................................................Home | Forum | About | Contact
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.
Copyright @ 2011-2013 worldbestlearningcenter. All Rights Reserved.
Home Programming Web DB & App Questions Exercises Tips Programs FAQs Download About
Computer-Wbest
Popular Pages
You might like