Narrow down a ListView when typing

[Zirak]

Hi,

I have a listView control. I populate the control by looping through a recordset that I retrive from a table.
I also have a Search text box that I let the user type what they are looking for and it will narrow down the result as they type.
On the "OnChange" property of the text box I build a dynamic sql statment that will return a recordset containing all the records that one of their fields matched the search criteria like :
SELECT * from tblMyTable Where Myfield LIKE '*' & criteria & '*'.

Then I clear the listBox, loop through the recordset and add them to the list.

PROBLEM:
each time the user types a letter the whole recordset is sent from the server to the client. This is unecasary network traffic since its already sitting in the list.
I know you can use FindItem method to look for an item in the list. But can you use wildcards with the FindItem?

If not is there any way to narrow down a ListBox results?

Thanks

 

[scking]

You are using the wrong control type. The OnChange event is triggered immediately when the user enters a value. So, if they entered an 'e' and you then used your SQL criteria of "*" & Criteria & "*" it would return a recordset for every value that had an 'e' anywhere in the data. You could try using 'AfterUpdate' or 'OnLostFocus' events or use a combobox which has an 'AfterUpdate' event that works marvelously for this. It also has the TypeAhead feature which will narrow down your search as you type BUT you couldn't have the *Criteria* because it has no way of eliminating values as you type unless you use Criteria*. I always use a combobox for this capability and then, in the 'AfterUpdate' event set a filter or the RowSource property of the form or control.




---------------------
scking@arinc.com
---------------------

 

[Zirak]

Thanks for your reply.
I know that the on change event fires when user types and thats what i want. This way as they type they can imedietly see the result list and ofcourse it at least three letters need to be typed.