"Auto Complete" on a combo box... 2

[mmilan]

Hi there,

I'm sure this can be done, but I don't seem to remember how... I have a combo box which I am populating with product codes. What I want is for the combo to suggest a complete code based upon the partial entry from the user...

I think it's some kind of property configuration, but failing that, I am going to have to write my own code to do it.

Cheers in advance for any assistance!

mmilan

 

[JustinEzequiel]

set the style property to 2 and see if this is what you want

 

[mmilan]

Not really bud...

Perhaps I should give a better description. Let's say I have a combo box with to items in it - "Martin" and "Milan".

I want the user to be able to type in the edit control part of the combo, and assuminging he types "M", then "artin" would appear as selected text after it. If he then presses "I", this would replace the selected text with "i", add the text "lan" to it and select that.

You with me?

Plan B as it were is to have a collection as a form variable, and populate it using the value itself as a key. Once this is done, I could check in event code if there was a suitable match.

Come to think about it, I have a products recordset I could use. I'd prefer not to though, because I want to avoid unnecessary network traffic. (I'm thinking that the recordset only stores a section of data, and often checks the data source. Thinking about it though, that can't be right...

I hate Mondays!

Cheers for the help!

mmilan

 

[CCLINT]

There are several ways to do this. One way is to loop through the entries in the combo box, comparing the elements with the entered characters; Another is to do this with a recordset object (clone the one that the combo is set to if this is a bound control or was used to fill the combo) and use the filter property, using the Like keyword (or just create a new recordset and use the Like keyword as the criteria in the WHERE portion of the SELECT statement - then take the returned results from the rs; Or use the Microsoft Forms 2.0 control (set a reference to it) - the combo box in this control package has an AutoWordSelect property.
If you use the latter, the check distribution issues under thread709-261789 and thread222-261786

 

[mmilan]

Cheers CClint...

I reckon I'm going to use a combo control, and then have event code check out the item's possible - in the same manner as you suggest...

It's an idea I had considered already, but I wanted to avoid all the event code if I could. Still, nevermind - I've got some serious code to do with this anyway - lots of lovely overlaying cells on a FlexGrid with text boxes and comboboxes... Nevermind...

Cheers!

mmilan

 

[devinci]

Dim Backspaced As Boolean

Private Sub cboNom_Change()

If Backspaced = True Or cboNom.Text = "" Then
Backspaced = False
Exit Sub
End If

Dim i As Long
Dim nSel As Long

For i = 0 To cboNom.ListCount - 1
If InStr(1, cboNom.List(i), cboNom.Text, _
vbTextCompare) = 1 Then
nSel = cboNom.SelStart
cboNom.Text = cboNom.List(i)
cboNom.SelStart = nSel
cboNom.SelLength = Len(cboNom.Text) - nSel
Exit For
End If
Next

End Sub

Private Sub cboNom_KeyDown(KeyCode As Integer, Shift As Integer)

If KeyCode = vbKeyBack Or KeyCode = vbKeyDelete Then
If cboNom.Text <> &quot;&quot; Then
Backspaced = True
End If
End If

End Sub

Try this it should help....

 

[CCLINT]

devianci, therefore I mentioned this as one of the ways and posted a Thread.....

 

[mmilan]

Thanks to both of you...

I'll give it a try when I get into the office tomorrow...

mmilan

 

[tonebone]

Try this code it is not mine but I cannot remember where I got it from. It uses and API call to find the text in the window. If you set the limit to list to True the user will only be able to select entrys from the combo box.

Put this in the declares section of your code:

Public Const CB_FINDSTRING = &H14C
Public Const CB_ERR = (-1)


Declare Function SendMessage Lib &quot;user32&quot; Alias _
&quot;SendMessageA&quot; _
(ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) As Long


On keypress function for your combo box:

Private Sub combo1(KeyAscii As Integer)
KeyAscii = AutoFind(combo1, KeyAscii, True)
End Sub

The AutoFind function:

Public Function AutoFind(ByRef cboCurrent As ComboBox, _
ByVal KeyAscii As Integer, _
Optional ByVal LimitToList As Boolean = False)

Dim lCB As Long
Dim sFindString As String

On Error GoTo errorhandler
If KeyAscii = 8 Then
If cboCurrent.SelStart <= 1 Then
cboCurrent = &quot;&quot;
AutoFind = 0
Exit Function
End If
If cboCurrent.SelLength = 0 Then
sFindString = UCase(Left(cboCurrent, Len(cboCurrent) - 1))
Else
sFindString = Left$(cboCurrent.Text, cboCurrent.SelStart - 1)
End If
ElseIf KeyAscii < 32 Or KeyAscii > 127 Then
Exit Function
Else
If cboCurrent.SelLength = 0 Then
sFindString = UCase(cboCurrent.Text & Chr$(KeyAscii))
Else
sFindString = Left$(cboCurrent.Text, cboCurrent.SelStart) & Chr$(KeyAscii)
End If
End If
lCB = SendMessage(cboCurrent.hwnd, CB_FINDSTRING, -1, ByVal sFindString)

If lCB <> CB_ERR Then
cboCurrent.ListIndex = lCB
cboCurrent.SelStart = Len(sFindString)
cboCurrent.SelLength = Len(cboCurrent.Text) - cboCurrent.SelStart
AutoFind = 0
Else
If LimitToList = True Then
AutoFind = 0
Else
AutoFind = KeyAscii
End If
End If

Exit Function
errorhandler:
MsgBox Error

End Function

I have used this code in W98 and W2000 not tested on any other platforms. Remember to set the LimittoList boolean as true if you want to limit to entries in the combo.

Cheers.

 

[strongm]

Here's another version that uses CB_FINDSTRING that I've knocked together. I think you'll find it closely matches the requirements that you stated (re: what text is selected after the match is made). It is also a little shorter.

Note that the actual search function is passed the handle to a combobox rather than the control itself, since this way the function can also be used against comboboxes in other applications (if you have their hWnd).
[tt]
Option Explicit
Private Declare Function SendMessage Lib &quot;user32&quot; Alias &quot;SendMessageA&quot; (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Private Const CB_ERR = (-1)
Private Const CB_FINDSTRING = &H14C
Private Const CB_SETCURSEL = &H14E

Private Sub CBSelectString(ByVal hwndCombo As Long, ByVal strSearch As String)
Dim smResult As Long

smResult = SendMessage(hwndCombo, CB_FINDSTRING, 0, ByVal strSearch)
If smResult <> CB_ERR Then ' Item is there
smResult = SendMessage(hwndCombo, CB_SETCURSEL, smResult, 0)
End If

End Sub

Private Sub Combo1_Change()
Dim OldSelStart As Long
Static InChangeEvent As Boolean

' Make sure we don't chain Change events
If Not InChangeEvent Then
InChangeEvent = True
OldSelStart = Combo1.SelStart
CBSelectString Combo1.hwnd, Left(Combo1.Text, OldSelStart)
' OK, get the highlighting correct
Combo1.SelStart = OldSelStart
Combo1.SelLength = Len(Combo1.Text) - Combo1.SelStart
InChangeEvent = False
End If
End Sub