Results 1 to 5 of 5

Thread: Cascade copying

  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm wanting to copy records & change one of the primary key fields to create a new unique set of records.

    This is based on the copying of the 'main' record, & related records in 2 other tables.

    The problem I'm having is that the related records are not updating, so the relationship is lost.

    I have the 'main' table related to an intermediate table & from there to a 3rd table.

    Realtionships are;
    • TblMain ► one-to-many ◄ TblCondition
      TblSub ► one-to-many ◄ TblCondition


    Each with cascading enabled.

    Any ideas where I'm going wrong?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    How are you copying the records - with a query? If so, I think we will need to see the query SQL strings in order to see how you are doing this. If you are simply copying the whole table and then pasting it, cascading won't be effective on the new tables.
    Wendell

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is being done in a user form (with relevant sub-forms).

    User would select the record, use Ctrl + C, then create new record & paste. The idea is to then change the value in one field (version), in the TblMain & this would cascade to the other tables, but does not.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I'm afraid that won't work - I assume the user has to copy the records in each sub-form as well, since copying the main record wouldn't copy the sub-records. Cascading only works when you change a record and you already have the sub-records existing. In addition, cascading changes are not viewed favorably by most Access Developers. I would need to know a fair bit more about your application to make detailed suggestions as to how to proceed, but I think you are probably going to need to use some VBA with DAO or ADO to create the new records you need.
    Wendell

  5. #5
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Cheers Wendell.

    I was afraid it would be something like that.

    We're using 2007 & all the records paste (into a new record), but those in the related tables disappear when any PK field is changed. So as things stand, each related record would have to be copied over individually (or maybe by a query???).

    I now understand this may only be acheivable with code (probably from a button).

    I don't have the knowledge in VBA on how to do this, any help would be greatly appreciated. What would you need to know?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •