How to search a record from database in Visual Basic 6

Welcome to the next tutorial of our Visual Basic programming.

Let us first enumerate the previous tutorials.

1. How to connect Visual Basic into MS Access.
2. How to insert a record in MS Access.
3. How to update the record in the database.
4. How to delete a record in the database.
5. How to populate Listview control from our database.

This is a part 6 of our Visual Basic 6 database programming using MS Access. Our next tutorial will be on searching record from database in Visual Basic 6.

Since this tutorial is the continuation of the previous tutorial which is populating ListView control from database, it is recommended to download the previous file.

Let’s start the tutorial:

1. After you have downloaded the file from the part 5 of our tutorial (How to populate Listview control from our database). Extract the file on your desktop or which folder you wanted to.

2. Open the extracted file and open the Project1.vbp.

3. On the project, there are three forms; InsertRecordFrm, SplashFrm and ListviewFrm. Open or select ListviewFrm.

4. Add a TextBox control in the form and name it as txtSearch.

5. We also need a command button that will trigger the search function. Kindly add a command button and name it as searchCmd with a caption Search.

This is the look now of our ListviewFrm after we have added the textbox and command button.

Still you are free to design or what will be the look of your form.

6. We will now create a function or procedure for searching a record from database. Kindly paste the code below. To paste the code just right click the ListviewFrm and click View Code and paste the code.

Sub SearchStudent()
Dim lstItem As ListItem 
If rs.State = adStateOpen Then rs.Close
    sql = " SELECT tblStudent.ID, tblStudent.studentName, tblStudent.studentAge, tblStudent.studentContact, tblStudent.studentAddress" & _
" From tblStudent" & _
" Where tblStudent.studentName='" & txtSearch.Text & "'" & _
" GROUP BY tblStudent.ID, tblStudent.studentName, tblStudent.studentAge, tblStudent.studentContact, tblStudent.studentAddress" & _
" ORDER BY tblStudent.ID;"
   
  rs.Open sql, conn
  ListView1.ListItems.Clear
  Do While Not rs.EOF
        Set lstItem = ListView1.ListItems.Add(, , rs(0).Value)
            lstItem.SubItems(1) = rs(1).Value
            lstItem.SubItems(2) = rs(2).Value
            lstItem.SubItems(3) = rs(3).Value
            lstItem.SubItems(4) = rs(4).Value
            rs.MoveNext
            Loop
MsgBox rs.RecordCount & " record(s) found for " & "" & txtSearch.Text
End Sub

Code explanation: The code above will retrieved all the fields in the database and populates it in our listview control if the user enters a student name that matches the student name stored in our database. The code will then prompt the user if how many record(s) matches the search.

7. Double click the search button and call the function that we have created.

The code should look like this:

Private Sub searchCmd_Click()
SearchStudent
End Sub

8. To test the project, kindly Press F5.

Note: Search option in the any database application is very necessary for the reason that it provides an easy access to data needed by the user. You can also create a complex search option based on your need.

If you have encountered errors in the program kindly post that on this page or you can directly email us. We will try our best to answer your queries.

The source code for this tutorial is available for download. Thank you very much for visiting the site.

The next part is on how to print records from database.