VBA to copy form recordsetclone to a table

Patrick McDevitt 1 Reputation point

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.

0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Tom van Stiphout 1,621 Reputation points MVP

    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
    while not .eof
    rsTo.ID = .ID
    end with

    0 comments No comments

  2. Patrick McDevitt 1 Reputation point

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

    0 comments No comments

  3. Patrick McDevitt 1 Reputation point

    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.
    I am using MS Access for OFFICE 365, 32 bit

  4. Patrick McDevitt 1 Reputation point

    That worked! Thank you very, very much!

    0 comments No comments