• Version
• Download 277
• File Size 40.00 KB
• File Count 1
• Create Date April 16, 2016
• Last Updated April 16, 2016

Filter Record from database Using Combo Box in Visual Basic

Filter Record from database Using Combo Box

Most of the searching or filtering of records is by textbox or search box, this time we will use combo box to filter records from database.

Features:
1. Populate combo box from records in the database (course, school year)
2. You can now select which course and school year you want to show or display the records.
3. After you have selected the course and school year the records will be displayed in the listview and count the number of items found.

Source code (filter function)

Public Sub DisplayStudentList(lstStudent As ListView, SearchSY As String, SearchCourse As String)
Dim lstItem As ListItem, a As Integer
If rs.State = adStateOpen Then rs.Close
    msql = " SELECT StudentsInfo.ID, StudentsInfo.Lname, StudentsInfo.Fname, StudentsInfo.Mname, StudentsInfo.mSex, StudentsInfo.Address, StudentsInfo.Contact, StudentsInfo.Course, StudentsInfo.SchoolYear" & _
           " From StudentsInfo" & _
           " Where StudentsInfo.Course= '" & SearchCourse & "' and StudentsInfo.SchoolYear='" & SearchSY & "'" & _
           " GROUP BY StudentsInfo.ID, StudentsInfo.Lname, StudentsInfo.Fname, StudentsInfo.Mname, StudentsInfo.mSex, StudentsInfo.Address, StudentsInfo.Contact, StudentsInfo.Course, StudentsInfo.SchoolYear" & _
           " ORDER BY StudentsInfo.ID, StudentsInfo.Lname;"
rs.Open msql, conn
   lstStudent.ListItems.Clear
   Do While Not rs.EOF
    a = a + 1
        Set lstItem = lstStudent.ListItems.Add(, , a)
            lstItem.SubItems(1) = rs(0).Value
            lstItem.SubItems(2) = rs(1).Value
            lstItem.SubItems(3) = rs(2).Value
            lstItem.SubItems(4) = rs(3).Value
            lstItem.SubItems(5) = rs(4).Value
            lstItem.SubItems(6) = rs(5).Value
            lstItem.SubItems(7) = rs(6).Value
            lstItem.SubItems(8) = rs(7).Value
            lstItem.SubItems(9) = rs(8).Value
            rs.MoveNext
            Loop
End Sub