Combobox/Listbox change event populate values in it

[aman]

Hi All

I am trying to write a code that will execute at the change even of the combobox/Listbox and when a character is typed in it then all the data from "DocumentType" field whose first character matches with the first character typed in Combo/Listbox will be stored in it.

The following code doesn't work:

Private Sub ComboBox4_Change()
   Dim strText, strFind As String
       
        strText = Me.ComboBox4.Text

        If Len(Trim(strText)) > 0 Then
           
            strFind = "BarcodeRef like '" & strText & "*'"
        End If
        
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long
       Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=C:\Desktop\System1.mdb;"
    
     Set rs = CreateObject("ADODB.Recordset")
 
    
    strsql = "SELECT TblRef.Ref FROM TblRef WHERE ((Ref Like 'strText*'))" 'This is the SQL of what data you want to return"
   
    rs.Open strsql, cn
    Do While Not rs.EOF
    
    ComboBox4.AddItem rs.Fields("BarcodeRef")
    rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
         Me.ComboBox4.DropDown
        
End Sub

Any help would be much appreciated.

Thanks

 

[nathansav]

1st Off.

strsql = "SELECT TblRef.BarcodeRef FROM TblRef WHERE ((BarcodeRef Like '" & strText & "*'))"

think you need to clear the dropdown 1st and set to value list if not already.