• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

Duplicate subtree in same tree hierarchy in Microsoft Access 2003

I have a database with three tables in a hierarchical one to many relationsihp.  Project, SubProject, Functional Plan.

I want to be able to copy a particular SubProject and its related entries in the Functional Plan to another Project.  How can I select a particular Subproject and reproduce the subproject under a different project?   Also please consider that I am using replication.  I tried to effectively copy a record, but it didn't bring along the related records.
1 Solution
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Without knowing the specifics of your table structure it's hard to fully advise, but basically:

1. Capture/create the Project ID:

   i  If you're adding a new Subproject to an existing Project, then open a Recordset and retrieve the Primary Key value of that Project
   ii If you're creating a New Project, then create that Project, and capture the Primary Key of that Project after it's created

2. Open a recordset containing the Subproject that you wish to clone. Use code to Insert a NEW record into the table, and again capture the value of the Primary Key. When inserting this new record, you would include the value captured in Step1 to relate your new SubProject to your new Project.

3. Now open a Recordset against your Function Plan table that is related to the SubProject you're cloning. Loop through this recordset, adding a NEW record to the Functional Plan table BUT relating each of those records to the Primary Key value of the SubProject you created in Step 2

StevenPMoffatAuthor Commented:
That is what I was expecting, but a little more complicated than my current skills to implement.  Thanks anyway.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now