UserForm Textbox - "Instant Search"

[ldoodle]

Hi,

I have a UserForm that has 3 controls - textbox and 2 buttons (Clear and Close). I am trying to replicate search engines "instant search" so results (from a SQL database) are updated as you type.

The problem is that the Change event causes the text input to lag while it is off doing it's SQL query, so the characters don't show in the textbox until the query is done. Can this be changed so typing is fluid.

One thing I've literallty just thought of is having a delay between keystrokes so if the time is longer than say 2 seconds, then do the search. Is that possible?

Also, is it possible to do what I want without a UserForm? Can I replicate this in an "in-sheet" control?

Thanks

 

[ldoodle]

Private Sub cmdSearch(value As String)
    Dim Connection, Command, Recordset As Object
    
    Application.ScreenUpdating = False
    
    Set Connection = New ADODB.Connection
    Connection.Open SqlString
    
    Set Command = New ADODB.Command
    Set Command.ActiveConnection = Connection
    
    With Command
    
        .CommandType = adCmdStoredProc
        .CommandText = "dbo.ef_sp_InproSearchExcel"
        .Parameters.Append .CreateParameter("@search", adVarWChar, adParamInput, 100, value)
        
    End With
    
    Sheet1.Range("A4", "A65536").EntireRow.ClearContents
    Application.Goto Range("A1"), True
    Sheet1.Range("A1").Select
    
    Set Recordset = Command.Execute
    If Not Recordset.EOF Then
        Sheet1.Range("A4", "G4").CopyFromRecordset Recordset
        For Each Cell In Range("A4:A65536")
            If Cell.value <> "" Then
                Cell.Hyperlinks.Add ANCHOR:=Cell, Address:="Y:\RootFolder\" & Left(Cell.value, 1) & "\" & Left(Cell.value, 2) & "\" & Cell.value, TextToDisplay:=Cell.value
            End If
        Next
    End If
    
    Recordset.Close
    Set Recordset = Nothing
    
    Set Command.ActiveConnection = Nothing
    Set Command = Nothing
    
    Connection.Close
    Set Connection = Nothing
    
    Application.ScreenUpdating = True
    
End Sub

 

[ldoodle]

Ok so I've been reading on adAsyncExecute but when I add it, the recordset never opens:

Set Recordset = Command.Execute(, , adAsyncExecute)