Recordset in a loop

[zadjali2000]

Hi everybody,

I would like to open a recordset inside a loop like this:

while not rs1.eof

sqlstmt = "select * from tbl_name where id = '" & rs1("needed_field") & "'

rs1.movenext
wend
------------------------------
Now the problem that it always says "Operation is not allowed when the object is open".

I remember that in Visual basic 6 i can open the recordset and add this code "rs1.ActiveConnection = Nothing" in order to do the same thing but when i tried it I got some new error "Operation is not allowed when the object is CLOSED"

So please can any one help me in this:confused:

[Roelf]

what are you trying to do, are you building a new query, based on information in the current recordset? This should work. If you try to re-open rs1with another sql statement while looping throug the current rs1, you should try creating another temporary recordsetobject which you use to get addidtional information from the database while looping through rs1,
like:
while not rs1.eof

sqlstmt = "select * from tbl_name where id = '" & rs1("needed_field") & "'
rstemp.open sqlstmt, connobject, etc

do the stuff you wanna doe with this info

rstemp.close

rs1.movenext
wend

perhaps, you can get all the info you want in the first recordset, using JOIN to connect the tables together, based on the criteria you provide

[whammy]

Roelf is totally correct, your SQL statement won't work because you are probably redefining the recordset object using the same variable, "rs1". When it tries to loop again, it can't because you have reassigned the rs1 object. To fix this you can create another temporary recordset within the loop using another variable as Roelf demonstrated above - however this is very inefficient and should be avoided if possible.

What does your first SQL statement look like? The latter suggestion by Roelf is definitely the best... I would use a JOIN if you can.

By looping and creating additional recordset objects, you are slowing down the server and your application.