Emptying a Recordset (1 Viewer)

Valentine

Member
Local time
Today, 02:01
Joined
Oct 1, 2021
Messages
261
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

You've got your good things, and you've got mine.
Local time
Today, 02:01
Joined
May 21, 2018
Messages
8,463
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

AWF VIP
Local time
Today, 02:01
Joined
Jan 11, 2013
Messages
3,852
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

Member
Local time
Today, 02:01
Joined
Oct 1, 2021
Messages
261
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

You've got your good things, and you've got mine.
Local time
Today, 02:01
Joined
May 21, 2018
Messages
8,463
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.

Code:
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

Enthusiastic Amateur
Local time
Today, 06:01
Joined
Sep 21, 2011
Messages
14,048
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

I’m here to help
Staff member
Local time
Yesterday, 23:01
Joined
Oct 29, 2018
Messages
21,358
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

Member
Local time
Today, 02:01
Joined
Oct 1, 2021
Messages
261
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

Super Moderator
Staff member
Local time
Today, 02:01
Joined
Feb 19, 2002
Messages
42,981
Just FYI, Action queries are far more efficient than VBA loops. You might want to run an update query instead of a code loop.
 

Users who are viewing this thread

Top Bottom