reuse recordset?

Hi all,

I have a textfield that on the textchanged event runs a query to a postgresql database and returns results to a recordset. You know, the old:
dim rs as recordSet
rs = databasename.sqlSelect( sqlquery )

I then loop through to see if the text typed in matched a record in the database (through the recordset)
if rs <> nil then
While Not rs.EOF
if rs.IdxField( 1 ).StringValue = me.Text then
\\do something
end if
rs.MoveNext
wend
end if

It is obviously going to be doing a lot of querying since it runs each time a letter is pressed.

What I wanted to do was query into the recordset when the window opens, then in the textchanged event in the textfield, loop through to check for match, then go to the top of the recordset ready for use next time a textchange event fires.

Is this possible?

I looked at recordset.movefirst but it does not work with postgresql (as noted in the docs and confirmed by testing).

Couldn’t you write the whole rs into an array which can be used as often, as you want?

Not at my desk right now.

Put everything in a Tmer with mode 0 and Period 250.
Start the timer in the KeyUp event may help.

@Robert Blazek Yes, i could and might have to. Was hoping there was a more direct route like recordset.movefirst

@Sascha S I don’t think I follow? this would still cause the query to run each time and generate a new recordset?
I just want to move to the top of the recordset.

Another thought i had was to create the recordset with
dim masterrs as recordSet
masterrs = databasename.sqlSelect( sqlquery )

then copy it with
dim rs as recordset
rs = masterrs

But rs goes to the bottom once you loop through masterrs

[quote=164284:@Andrew Willyan]Hi all,

I have a textfield that on the textchanged event runs a query to a postgresql database and returns results to a recordset. You know, the old:
dim rs as recordSet
rs = databasename.sqlSelect( sqlquery )

I then loop through to see if the text typed in matched a record in the database (through the recordset)
if rs <> nil then
While Not rs.EOF
if rs.IdxField( 1 ).StringValue = me.Text then
\\do something
end if
rs.MoveNext
wend
end if
[/quote]
Dont do this
The database can do all this work for you and return only the things that match
That will be much faster

Dont do it on the keypress
In the keypress start a short period timer that runs in about 1/2 a second IF there are no more keypresses
If you get another keypress reset the timer so it will run 1/2 second after the last keypress

Then you do the query a lot less