Automatically copying main and related sub records

Discussion in 'Microsoft Access Macros' started by Stephen Jame Pattenden, Jan 22, 2009.

  1. I am trying to create a macro/vba that will automatically copy a master
    record and its related sub records into a new master record with the same
    related sub records.

    Automatically copying the main record is simple, but being able to copy all
    the related sub records and pasting them into a new main record - to which
    they would then be related is proving to be quite difficult.

    Any suggestions would be greatfully received.
     
    Stephen Jame Pattenden, Jan 22, 2009
    #1
    1. Advertisements

  2. To copy the subrecords, you'll need to know the original master record's
    primary key value, and the newly copied master record's primary key value.
    Then you run an append query that uses the original master record's primary
    key value in the WHERE clause for selecting the subrecords, and that uses
    the newly copied master record's primary key value in the VALUES (or SELECT)
    clause for the new subrecords' foreign key field.

    --

    Ken Snell
    <MS ACCESS MVP>
    http://www.accessmvp.com/KDSnell/


    "Stephen Jame Pattenden" <>
    wrote in message news:...
    >I am trying to create a macro/vba that will automatically copy a master
    > record and its related sub records into a new master record with the same
    > related sub records.
    >
    > Automatically copying the main record is simple, but being able to copy
    > all
    > the related sub records and pasting them into a new main record - to which
    > they would then be related is proving to be quite difficult.
    >
    > Any suggestions would be greatfully received.
     
    Ken Snell \(MVP\), Jan 23, 2009
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Joe
    Replies:
    0
    Views:
    336
  2. Pele

    Macros - to Delete Records or Add New Records

    Pele, Apr 6, 2005, in forum: Microsoft Access Macros
    Replies:
    4
    Views:
    1,087
  3. DD

    copying records

    DD, Aug 2, 2005, in forum: Microsoft Access Macros
    Replies:
    2
    Views:
    203
    Michael Shannon
    Aug 2, 2005
  4. Guest

    run macro on all records automatically

    Guest, Feb 20, 2007, in forum: Microsoft Access Macros
    Replies:
    1
    Views:
    186
    Guest
    Feb 22, 2007
  5. arnezmando

    Access VB create new records and replicate related records

    arnezmando, Apr 10, 2007, in forum: Microsoft Access Macros
    Replies:
    0
    Views:
    767
    arnezmando
    Apr 10, 2007
  6. Hediru

    Copying fields from one table to another automatically

    Hediru, Apr 17, 2008, in forum: Microsoft Access Macros
    Replies:
    1
    Views:
    242
    Jeanette Cunningham
    May 1, 2008
  7. Yousoft

    Delete sub-form records

    Yousoft, May 19, 2008, in forum: Microsoft Access Macros
    Replies:
    1
    Views:
    228
    Steve Schapel
    May 22, 2008
  8. TRose

    Syntax for sub sub form in macro

    TRose, Jun 8, 2009, in forum: Microsoft Access Macros
    Replies:
    4
    Views:
    640
    TRose
    Jun 9, 2009
Loading...