Changing Recordsets

[Spyle]

I have a form which lets you type in criteria to search a table by.

On that form I have textboxes that I bind to that recordset after they input their criteria and hit a search button.

Private Sub cmdSearch_Click()
 
If rsSearch.State = 1 Then rsSearch.Close
rsSearch.Source = "SELECT * FROM MyTable WHERE MyField = '" & txtSearch.Text & "'"
rsSearch.Open , connDB, adOpenStatus, adLockOptimistic
Set textbox1.Datasource = rsSearch
 
End Sub

Now, I have about 30 controls with the datafields already set that I set the datasource to that recordset. It works fine..

My problem is, when they search a second time it goes REALLY slow. The only way I found to fix this was to set the controls datasource to Nothing before setting it to the RS.

Seemed to work great until I watched the memory usage go UP UP UP every time you searched. It does not do that if dont set the controls datasource to Nothing first though.. What can I do?? HELP!

[ralph]

How's it declared?
Dim rsSearch As New Recordset 'bad implicit declaration might lead to memory leak
or
Dim rsSearch as recordset
set rsSearch = new recordset

just guessing if a new instance of RS gets created with implicit declaration each time datasource references rsSearch.

[Spyle]

In the declarations I have:

Private WithEvents rsSearch As ADODB.Recordset

and in the form load I have

Set rsSearch = New ADODB.Recordset

The only time I do "Set rsSearch = New ADODB.Recordset" again is if the previous results returned 0 records. If I don't reset it the recordcount is always -1.

[Spyle]

Am I the only one who experiences this problem? I just created another small app that uses Set MyControl.DataSource = Nothing and memory usage on the app goes up and up each time it does that.