VBA to copy form recordsetclone to a table

Patrick McDevitt 1 Reputation point
2021-06-20T08:17:01.127+00:00

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
    2021-06-20T19:05:49.517+00:00

    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

    0 comments No comments

  2. Patrick McDevitt 1 Reputation point
    2021-06-21T20:34:35.53+00:00

    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
    2021-06-22T00:27:35.63+00:00

    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


  4. Patrick McDevitt 1 Reputation point
    2021-06-24T13:28:01.003+00:00

    That worked! Thank you very, very much!

    0 comments No comments