Any ideas how to emulate googles instant search in access?

[Daniel Forstner]

Has anyone tried to emulate the google instant search in microsoft access using a little bit of vba?

I tried doing so using a form with a text box and a list box.

I used the text boxes on change event to run this code

Private Sub txtSearchName_Change()
    Dim strSQL As String
 
strSQL = "SELECT tblUsers.UserKey, tblUsers.DisplayName " & vbCrLf & _
        "FROM tblUsers " & vbCrLf & _
        "WHERE (((tblUsers.DisplayName) Like ""*""+ '" & Me.txtSearchName & "' +""*""));"
 
    Me.lstUserSearch.RowSource = strSQL
    'Debug.Print Me.lstUserSearch.RowSource
End Sub
 

the code does work but not very consistently.
There is a few second lag before the list box updates again. When i debug.print the sql string it doesnt change with every letter entered :-(

Anyone have any ideas?

Thanks in Advance for your help!

[Rabbit]

The change event doesn't trigger everytime you press a key. It usually triggers after the field loses focus. The closest you can get is the key up event. You would have to use the Text property of the field.

[Daniel Forstner]

Thanks for the quick reply!

I tried using the key up event and that works great but for some reason the queries where criteria still doesn't update with every key stroke.

What do you mean by I have to use the text product of the field?

Thanks

[Rabbit]

Fieldname.text instead of fieldname.value

[Daniel Forstner]

Thanks Rabbit!
That solved my problem. It searches everytime a new letter is entered and works great.

[TheSmileyCoder]

Just a short addon from me:
When using Me.txtSearchName you are in most cases actually refering to the default property of that control which is the .Value

[Daniel Forstner]

That makes sense!
I'll have to remember that for next time cause i have some accross a similar problem in the past!

Thanks again :-)