question

PatrickMcDevitt-8684 avatar image
0 Votes"
PatrickMcDevitt-8684 asked PatrickMcDevitt-8684 answered

VBA to copy form recordsetclone to a table

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.

office-vba-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TvanStiphout avatar image
0 Votes"
TvanStiphout answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PatrickMcDevitt-8684 avatar image
0 Votes"
PatrickMcDevitt-8684 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PatrickMcDevitt-8684 avatar image
0 Votes"
PatrickMcDevitt-8684 answered TvanStiphout commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

My off-the-cuff mistake. Should have been:
rsTo!ID = !ID
Of course "ID" should be replaced by your PK field name.

0 Votes 0 ·
PatrickMcDevitt-8684 avatar image
0 Votes"
PatrickMcDevitt-8684 answered

That worked! Thank you very, very much!

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.