Searchable combo-box in user-form excel from access database

[PilarGiannine]

Hi, everyone.

I have a combobox in an excel 2016 user form. Combo box load data from an access table, if I write something it autocompletes itself but with the first letter in the table. There's a record "Compañia Molinera", if I write "comp" it autocompletes "Compañia Molinera" but if I write "molinera" it shows nothing as if the record doesn't exist. What I want is to look up what I write not just the first letter but the whole text and when I start writing shows a dropdown list with the word suggestions.

This is my code:

Public MiConexion As New ADODB.Connection
Public Rs As New ADODB.Recordset
Sub Conectar()

Set MiConexion = New ADODB.Connection
    With MiConexion
        .Provider = "Microsoft.ACE.OLEDB.16.0"
        .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\CONTAINTEGRAL.accdb"
        .Open
    End With
End Sub

Private Sub UserForm_Initialize()
    
    Call Conectar

        Rs.ActiveConnection = MiConexion
        Rs.CursorType = adOpenStatic
        Rs.LockType = adLockOptimistic
        Rs.CursorLocation = adUseClient
        Rs.Open "CLIENTES"
                'BoundColumn = 1
                'ColumnCount = 2
                'TextColumn = 2
                'ColumnWidth = 0
           Do While Not Rs.EOF()
              With Cmb_RSCliente
                .AddItem
                .List(.ListCount - 1, 0) = Rs("RUC")
                .List(.ListCount - 1, 1) = Rs("RAZÓN SOCIAL")
              End With
           Rs.MoveNext
           Loop
   Set Rs = Nothing
   MiConexion.Close
   Set MiConexion = Nothing
End Sub

Private Sub Cmb_RSCliente_Change()
 
    On Error GoTo Fin
 
    If Cmb_RSCliente.Text <> "" Then
        Txt_RUC.Text = Cmb_RSCliente.List(Cmb_RSCliente.ListIndex, 0)
    End If

Exit Sub
    
Fin:
    MsgBox "El dato '" & Me.Cmb_RSCliente.Text & "' no se encuentra en base de datos", vbInformation, "MV Containtegral"
    Cmb_RSCliente.Value = ""
    Txt_RUC.Value = ""
    Cmb_RSCliente.SetFocus
        
End Sub

 

[Marc L]

Hi,​

​

you can use for example the VBA text function Instr to check if an item contains a text …​

​

 

[PilarGiannine]

Thank you for the answer but could you give more details please?

 

[Marc L]

As written in the VBA help this function returns the position of the text searched in the original text​

so if it returns zero that means no match …​

​