VBA to copy form recordsetclone to a table

Question

I'd like to filter a form and then click a button to copy the form's resultant recordsetclone to a table. I'm am doing this so one can thereafter use the table for queries. I am looking for the vba code to place behind my button.

Answer 1

At the most you should copy the PK column(s) to a table, maybe tblSelections, in the FE.
The other fields you can pick up with a new query that joins the form's query with this table.
Off the cuff and of course guessing at your object names:
dim rsTo as dao.recordset
set rsTo=currentdb.openrecordset("tblSelections", dbOpenDynaset)
currentdb.execute "delete * from tblSelections;", dbfailonerror
with me.recordsetclone
.movefirst
while not .eof
rsTo.AddNew
rsTo.ID = .ID
rsTo.Update
.MoveNext
wend
end with
rsTo.Close

Answer 2

Thank you very much. I will give this a shot and let you know how I make out.

Answer 3

Hi @Tom van Stiphout , I ran the code and for the line of code "rsTo.ID = .ID" I get the message "Compile error: Method or Data member not found."
Do you know what I can do to activate the ".ID" or an alternate value to use?

I ran the code without this line and it does do the delete, of course, and does "write" whatever number of records I have in my filtered form recordsetclone, so it is doing the proper loop-through. It is just not pulling any data.

Any further help you might provide would be greatly appreciated.
Patrick
I am using MS Access for OFFICE 365, 32 bit

Answer 4

That worked! Thank you very, very much!