Extending DBCombo Box...

[chernandez2000]

Hi all VB phreaks out there.

I am trying to extend the normal way the DBCombo box works as follows:

The DBCombo is currently attached to a field in a database, so it is populated with, for example, last names. I am using dblExtendedMatching under the Match Entry property. So the names automatically complete. Thats cool. But I want to highlight the part of the name that is autocompleted, and also if the user enters a letter that isnt there, then no longer highlight anything.

So if "Smith" is a last name in the database... as the user types "S" then "mith" would appear and be highlighted... then if the next letter the user enters is "a", then since there is no "Sa..." names in the database, the user would see "Sa" without any highlights.

Excuse the long post, but I wanted to explain it. I have a feeling Windows API calls will be involved.

Anyone point me in the right direction?

Thanks!

Conrad

 

[RobS23]

Conrad I'm posting more to keep track of the thread than help specifically. To be honest I've given up on the DBcombobox(I don't like binding anyway) and gone back to it's precursor and written my own matching procedure. It can populate, match entries and create new entries in a SQL db - although it doesn't provide the visual feedback you require - you're welcome to have a look.

Rob

 

[chernandez2000]

Yeah Rob,

Not sure if its easier to email it to me or post it here.

Whatcha think?

 

[Mrmeola]

how about if you create a cbo box named cboFind and copy and paste this code....

when finished run the program and type in "S" and "mith" will be highlighted in the box Loaded some other names so you can play with it..

E

Option Explicit

Dim back As Boolean


Private Sub Form_Load()
    
    With cboFind
    .AddItem "Regal"
    .AddItem "Jones"
    .AddItem "Wilson"
    .AddItem "Johnson"
    .AddItem "Meola"
    .AddItem "Smith"
    End With
End Sub

Private Sub cboFind_KeyDown(KeyCode As Integer, Shift As Integer)
'shouldn't respond to this change.
    If KeyCode = 8 Or KeyCode = 46 Then 'KeyCode 8 is backspace...46 is delete
        If cboFind.Text <> &quot;&quot; Then
            back = True
        End If
    End If

End Sub

Private Sub cboFind_Change()

    'keeps backspace from messing up program
    If back = True Or cboFind.Text = &quot;&quot; Then
        back = False
        Exit Sub
    End If

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

End Sub

[\Code]

 

[RobS23]

Conrad
This has been hacked about with - my naming conventions don't need some of the variables - any mistakes will be easy to repair though. Matches record (use this in conjunction with Mrmeola's code above) if unmatched will optionally, save record with stored procedure.

'*****Match(or not) Text entered to listitem in Combo box *****

Public Sub MatchCreateComboData(objCombo As ComboBox, Optional spAddRecord As String, Optional ADOConnstr As String)

Dim adoconn As New ADODB.Connection
Dim ADOCmd As New ADODB.Command
Dim Param As New ADODB.Parameter

Dim i As Integer

If objCombo = "" Then Exit Sub

' Itterate through Combobox items to match entry to list item
For i = 0 To objCombo.ListCount - 1

If LCase(objCombo.List(i)) = LCase(objCombo.Text) Then
objCombo.ListIndex = i
Exit Sub

End If

Next i


'Add new record to table if Stored Procedure name has been passed _
else Exit
If spAddRecord = "" Then Exit Sub

adoconn.Open Connstr

Set ADOCmd.ActiveConnection = adoconn

ADOCmd.CommandText = spAddRecord
ADOCmd.CommandType = adCmdStoredProc

Set Param = ADOCmd.CreateParameter("@Param", adVarChar, adParamInput, 50, 0)
Param.Value = objCombo.Text
ADOCmd.Parameters.Append Param

ADOCmd.Execute


'Add new item and index to Combobox
objCombo.AddItem objCombo.Text
objCombo.ItemData(objCombo.NewIndex) = (objCombo.ListCount + 1)

objCombo.Refresh

'Recursion - to Match new data
GetCboData objCombo




End Sub

 

[chernandez2000]

Mrmeola and Rob - you dudes rock!

I'm gonna start playing with the code in a few minutes.

Thanks!

Conrad

 

[RobS23]

No problem darn things have always irritated me! Drop an email address and I'll send you a working example - with a populate routine added for free!

 

[chernandez2000]

chernandez2000@hotmail.com

Thanks again!