searching databases

[milner]

How can I search through a database in a specific field like is done when you go to windows help-index window. I want to see possible matches as I type in the string.

 

[hug]

One way to do it is with the text_change() event, put a code to look like:<br>
<br>
private sub text1_change()<br>
dim s as string<br>
s = "fields = " & text1.text<br>
with dat.recordset<br>
.findfirst s<br>
if .nomatch then .movefirst<br>
end with<br>
end sub<br>

 

[chiph]

hug & milner -<br>
<br>
I saw this being done in Bell South's "Yellow Pages on CDROM". God, what a dog. It hit the disk every time you would enter a keystroke. Even on a 24x CDROM I'd fall asleep.<br>
<br>
It's probably an OK technique as long as the database is on a local hard drive, and the query is very focused, and the table is small, and you're going against the primary key.<br>
<br>
Chip H.<br>

 

[calahans]

Guys,<br>
<br>
I have seen this being done via a combo/list box. Populate the combo, then use the windows api to search, its a simple piece of code and very fast. I'll try to dig it up.<br>
<br>
Calahans.

 

[MikeLacey]

I think that I would agree with Chip - this could be rather slow unless you're searching through a small amount of data.<br>
<br>
You could try speeding it up by reading the data into an array and searching through that on the change event... Depends upon how much data you have to deal with and on the speed of the PC doing the search.<br>
<br>
Regards<br>
<br>
Mike<br>

 

[johnk]

Banging on the database in a multi-user network is a big problem here. Like Mike, we load an array during form activate. Only with the search field and corresponding record key.<br>
<br>
Users expect the good stuff they see with shrink wrapped single user packages. It's a genuie challange to include some of it in multi-user apps without wrecking response times.<br>
<br>
JohnK

 

[trebort]

I would have to agree with the comments re the api. Look at a windows help file, some of these may have a couple of thousand entries, and as you type it actively searches the list for a match. You could look at setting up a static recordset set for users, write it to the the harddisk and reload it into an a recordset and populate an object (list combo..) and use it to search. <br>
<br>
Get any decent reference on ADO and you can see the code to write a recordset to disk. I have used the api and a combo to search through 2000 records and its a fast as a windows help file. However, there is an upper limit to the recordset size for perf!!!