[RESOLVED] RESOLVED - Combo Box AutoComplete from access database
I have the following to auto complete a combo box from an access database, but i have problems
Code:
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
Code:
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.
Re: Combo Box AutoComplete from access database
you can use the sendmessage api to do the autocomplete with message cb_findstring
there will be many examples for this
Re: Combo Box AutoComplete from access database
Where do you call this Get_Records Function ?
BTW. try changing
Code:
Else
cbo.Text = Find
End If
to
Code:
Else
cbo.Text = Find
cbo.SelStart = Len(cbo.Text)
End If
:wave:
Re: Combo Box AutoComplete from access database
This is the function I use:
Code:
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:
Code:
'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.
Re: Combo Box AutoComplete from access database
zeezee, that little change you suggested did the trick. Thank you. and its called in the keyup
Re: RESOLVED - Combo Box AutoComplete from access database
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.
:wave: