[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.

[westconn1]

you can use the sendmessage api to do the autocomplete with message cb_findstring

there will be many examples for this

[zeezee]

Where do you call this Get_Records Function ?

BTW. try changing

 Else
        cbo.Text = Find
        
    End If

to

   Else
        cbo.Text = Find
        cbo.SelStart = Len(cbo.Text) 
    End If

[Comintern]

This is the function I use:

Public Function AutoCompleteCombo(oCombo As ComboBox) As Boolean

    Dim sPart As String, lIndex As Long, lStart As Long, sTemp As String

    With oCombo
        If Len(.Text) <> 0 Then                                         'Don't execute if null string.
            lStart = .SelStart                                          'Cache cursor position.
            sPart = Left$(.Text, lStart)                                'Select the user typed text.
            For lIndex = 0 To .ListCount - 1                            'Loop through Combo items.
                sTemp = UCase$(.List(lIndex))                           'Make non-case sensitive.
                If sTemp Like UCase$(sPart & "*") And sTemp <> UCase$(.Text) Then   'Test item.
                    .SelText = Mid$(.List(lIndex), lStart + 1)          'Add on the new ending.
                    .SelStart = lStart                                  'Reset the selection.
                    .SelLength = Len(.Text) - lStart
                    AutoCompleteCombo = True                            'Return true for automatch.
                    Exit For                                            'First match is good enough.
                End If
            Next lIndex
        End If
    End With

End Function

You really shouldn't be updating the record set every time the user types in the Combo. This is a TON of overhead you don't need. Fill the combo at the start, and then use the return value of the above function (or some other method to determine if it is a new entry. It's called like this:

'In declarations
Private bReEntry As Boolean

Private Sub ComboBox1_Change()

    If Not bReEntry Then                                                'Check for re-entry.
        bReEntry = True                                                 'May make changes, so set the flag.
        If Not AutoCompleteCombo(ComboBox1) Then                        'Test for autocomplete.
            'Do whatever.
        End If
        bReEntry = False                                               'Changes done, can toggle the flag.
    End If

End Sub

Note: The re-entry flag prevents it from recursively running the event when the AutoComplete function changes the control's value.

[seanwpb]

zeezee, that little change you suggested did the trick. Thank you. and its called in the keyup

[zeezee]

Glad I could Help
One thing I want to tell is that the method you are using could degrad App's performance.
In every key press, it would query the DB and get values. What would happen when the DB grows up?
Try what Comintern has given also.