[RESOLVED] RESOLVED - Combo Box AutoComplete from access database

[seanwpb]

I have the following to auto complete a combo box from an access database, but i have problems

Public Sub Get_Records(Find As String, cbo As ComboBox)
    
    Dim tRec As New ADODB.Recordset
    Dim SQL As String
    
    ' clear previous entries
    cbo.Clear
    
    If Find = "" Then Exit Sub
    
    ' build sql
    SQL = "SELECT * FROM tblName WHERE Name LIKE '" & Find & "%'" & " ORDER BY name"
    
    ' open recordset
    tRec.Open SQL, conn
    
    If Not tRec.BOF And Not tRec.EOF Then
        Do While Not tRec.BOF And Not tRec.EOF
            cbo.AddItem tRec.Fields("Name").Value
            tRec.MoveNext
        Loop
         
        ' put the first name in the cbo boxes text property
        ' and hightlight none user typed text
        cbo.Text = cbo.List(0)
        cbo.SelStart = Len(Find)
        cbo.SelLength = Len(cbo.Text) - Len(Find)
    Else
        cbo.Text = Find
    End If
    
    If tRec.State = adStateOpen Then tRec.Close
    Set tRec = Nothing
    
End Sub

Function Connect_to_Database(Path_To_Database As String) As ADODB.Connection
    
    On Error GoTo OpenError
    Dim tConn As New ADODB.Connection
    ' provide ODBC and open connection to database
    tConn.ConnectionString = "provider=Microsoft.JET.OLEDB.4.0;data source=" & Path_To_Database
    tConn.Open
    ' return new connection
    Set Connect_to_Database = tConn
    Set tConn = Nothing
    Exit Function
    
OpenError: ' be of error trapping

    MsgBox Err.Description
    Set tConn = Nothing
    
End Function

Dim conn As New ADODB.Connection

Set conn = Connect_to_Database(App.Path & "\work.mdb")



The problem is if your typing in the combo box and a match isn't found then it starts placing each new character you type at the beginning of the combo box. So it basically doesn't allow me be able to add new records because it jumbles up the text. I hope thats clear.