Duplicate subtree in same tree hierarchy in Microsoft Access 2003

Posted on 2009-02-21
Last Modified: 2013-11-27
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.
Question by:StevenPMoffat
    LVL 84

    Accepted Solution

    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


    Author Closing Comment

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

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now