changing a recordset

[John Watson]

I have a connection and recordset declared in a standard module. I want to be able to temporarily alter the recordset in a different form to narrow down the amount of data available. I tried this:

[strSource is variable storing the original SQL statement for the recordset in the standard mod.]

rst.Open strSource & " " & "WHERE State = 'MD'"

but I always get an error; if I close the original recordset first I get "cannot perform if object is closed" and if I leave it open I get "cannot perform if object is open".

I tried using the filter method for this but as I understand it recordset.filter cannot perform statements such as ("State = 'MD'" OR "State = 'DC'") AND "Last_Name = 'Jones'" --- which I need to do.

Does anyone know the proper way to alter a recordset, or if I can use the filter method somehow to do this? I'm sure the answer is easy, I just don't know it.

Thanks
John

[Rufus]

Are you using ADO or DAO ? I don't know the error your getting but try this first:

rst.Open strSource & " " & " WHERE State = 'MD'"

add a space befor the W in WHERE because in your SQL string your saying SELECT ### FROM ###WHERE.

If this don't work, and your using ADO Dim a New Record set and open it with the new SQL string, on the same connection obj. After you get what you want, make the new recordset = Nothing. As for filters use the OR & AND in the SQL string.

"### ### ### ### WHERE TABLE.FIELD = 'Something' or TABLE.FIELD = 'Something Else'" .
Please don't take this to the bank, it's just an idea. I hope it's helps.

[John Watson]

I forgot to mention the most important part. Duh. I'm using ADO to connect to an Access database (from a VB6 project). I will try your idea and repost if it doesn't work.

Thanks,
John