Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    2

    Unhappy Unanswered: Search and Filter ListBox

    hye All,

    I'm new in access and I created a form with a text box (txtSearch) and a listbox to store student ID using table/query for row source. I want to filter list(studentID) in listBox as i enter txt in txtSearch. This is my vba coding.

    **************
    Private Sub txtSearch_Change()
    On Error Resume Next

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset


    rs.Open "SELECT * FROM tblStudent WHERE (StudentID LIKE """ & txtSearch & """ )", _
    CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    If rs.RecordCount = 0 Then
    MsgBox "No ID Found!!!", vbExclamation, "Oops.."
    txtSearch = ""
    txtSearch.SetFocus

    Else
    rs.MoveFirst
    Do While Not rs.EOF
    lstID.AddItem rs!StudentID
    rs.MoveNext
    Loop
    End If
    End Sub

    **************

    What is worng with that code...

    Thanks..

  2. #2
    Join Date
    May 2003
    Posts
    144
    You are missing the * on your select statement.

    You are adding the items to the list box based on a recordset that is generated according to the text box. I am not sure if there is a reason behind this approach, but there is an easier way to make this work. You can reset the rowsource of the listbox when a new value is keyed on the text field.

    Have the following script under the AfterUpdate of the text field

    me.lstbox.rowsource = "SELECT * FROM STUDENT WHERE STUDENTID lIKE '*" & me.txtSearch & "*'"

    Hope this Helps

    SHK

  3. #3
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    On a quick glance: single-quotes for criteria.

  4. #4
    Join Date
    Feb 2005
    Posts
    2

    Talking

    Thanks a lot for reply. Wow...It work.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •