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.
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.
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
Thank you very much. I will give this a shot and let you know how I make out.
Hi @Tvanstiphout, 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
My off-the-cuff mistake. Should have been:
rsTo!ID = !ID
Of course "ID" should be replaced by your PK field name.
2 people are following this question.