Emptying a Recordset

[Valentine]

I am running a loop through a recordset and want to empty it before each loop to fill it with a different variable each time. How do I empty the recordset. I tried rsCNF = Empty but thats not right.

 

[MajP]

As written that question makes no sense. Can you explain better, what you are doing. There really is not such thing as emptying a recordset. If this is in a loop where you are done with the recordset you can close it and set the variable to nothing.

rsCNF.close
set rsCNF = nothing

 

[moke123]

Not quite sure what you mean by "empty it before each loop to fill it with a different variable each time"
Can you explain further and post what you have so far?

 

[Valentine]

Can't go too much into detail. I am setting up code to update a table by line based on a certain criteria. I have a recordset that has all the criteria and I want to run through each line 1 by 1 and update the table row by row and i want the RS to empty and fill up again with the next criteria row to fill. I know its kinda vague but cant say much without breaching security.

 

[MajP]

but cant say much without breaching security

That's hilarious. Using VBA on those top secret CIA projects again?

Still no idea what you are talking about. However if you reload an RS you do not "empty" it.

Set RS = currentDB.openrecordset ("Select * from sometable where SomeField = 'ABC')
loop here
rs.close  ' this is probably not necessary but to be safe.
Set RS = CurrentDb.openrecordset("Select * from sometable where SomeField = 'DEF')
next loop

 

[Gasman]

Can't go too much into detail. I am setting up code to update a table by line based on a certain criteria. I have a recordset that has all the criteria and I want to run through each line 1 by 1 and update the table row by row and i want the RS to empty and fill up again with the next criteria row to fill. I know its kinda vague but cant say much without breaching security.

Tip the recordset upside down and give it a shake?

 

[theDBguy]

Hi. Besides closing the recordset (rs.Close), you can also destroy it (Set rs = Nothing). However, I am also not sure I understand your request. Cheers!

 

[Valentine]

That's hilarious. Using VBA on those top secret CIA projects again?

NSA but thank you for repling, I just ask cuz i was getting an error but after reading what you wrote I think my close function should work since i have the rs set at the beginning of the loop and close at the end should work.

 

[Pat Hartman]

Just FYI, Action queries are far more efficient than VBA loops. You might want to run an update query instead of a code loop.