Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cloning Deep Levels (2003 SP2)

    I've set up a sequence of tables for a company which looks after several sites. Each site can have any number of contracts, each contract can have any number of departments, each department can any number of cleaning activities, and each cleaning activity has its own 'thing' eg shelving, floors, windows, to be cleaned.

    I need to set up various levels of cloning, specifically at site level, and contract level, and department level. Cloning a department means I would need to duplicate all cleaning activities and related things, however many there are and then dump them back into the appropriate tables. The autonumbering at this point is taken care of by Access but I am stuck on establishing and recording each foreign key in the new tables.

    Cloning a contract multiples the aforementioned problem. To clone a site, makes the whole scenario even more scary.

    Just a bit of background to help with understanding the setup and structure, I have created only one data entry form for the entire database - the main form being for the site and there are four sub-forms for the related records down the 'pyramid' of contracts, departments, etc. That all works perfectly.

    My question is, "How do I make the cloning happen at each of those three levels?". I realise this problem might be too broad in scope but I am hoping someone has had to tackle this situation before and can help out.

    Many thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cloning Deep Levels (2003 SP2)

    I don't have enough time right now, but if nobody else replies, I'll get back to you later today.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Cloning Deep Levels (2003 SP2)

    Are you familiar with using DAO Recordsets?

    That is how I would approach this task.

    To clone something, open a recordset containing just the record I want to clone. Put all it field values into variables, add a new record, then write the variable values into the new record. Then write the value of the new autonumber back to a variable so you can use it later.

    To clone all the 'children' of a record, open a recordset containing each of the children, then write a loop to work through them and clone each of them in turn. Use the new autonumber you have kept in a variable, to link the clones back to their new parent.
    Regards
    John



  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cloning Deep Levels (2003 SP2)

    I have attached part of the code. You must, of course, substitute the correct table names and field names.
    I have assumed that each table has an AutoNumber field as primary key, and that this is the first field.

    To clone the site with ID = 37, you'd call

    CloneOneSite 37

    CloneOneSite calls CloneContracts, and this in turn calls CloneOneContract repeatedly.
    CloneOneContract calls CloneDepartments, and this in turn calls CloneOneDepartment repeatedly.
    CloneOneDepartment calls CloneActivities, and this in turn calls CloneOneActivity repeatedly.
    CloneOneActivity calls CloneItems, and this in turn calls CloneOneItem repeatedly.

    The CloneOne... procedures can be called, e.g.

    CloneOneDepartment 98

    The names indicated in Italic haven't been implemented in the attached code, they can be created by duplicating CloneContracts and CloneOneContract and modifying the duplicates.

    Note: I haven't actually tested the complete code, so try it out on a copy of the database first!
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cloning Deep Levels (2003 SP2)

    Thank you for taking the time to reply. Unfortunately I am not familiar enough with DAO recordsets although I understand what you are saying but not enough to implement it.

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cloning Deep Levels (2003 SP2)

    Hans, Thank you immensely for the code. I copied and pasted the code making a couple of minor changes for naming but when I compiled it I got an error: "Member identifier already exists in object module from which this object module derives"

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cloning Deep Levels (2003 SP2)

    You should put the code in a standard module (the kind you create by clicking New in the Modules section of the database window), not in a form module.

  8. #8
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cloning Deep Levels (2003 SP2)

    Thanks Hans, it's worked beautifully!! I really appreciate your help! I'll be able to keep chipping away at the remaining tables with a great deal more knowledge and confidence.

  9. #9
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cloning Deep Levels (2003 SP2)

    Hans, I have just one question about this process: How to make the newly cloned form display as the current record? I have tried adding a bookmark to the end of the procedure "Clone One Site" as follows but it doesn't work.
    ==================================================
    ' Clone documents
    CloneDocuments lngOldSiteIDp, lngNewSiteID
    DoCmd.ShowAllRecords
    .Bookmark = .LastModified
    End With

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    ==================================================

    Many thanks

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cloning Deep Levels (2003 SP2)

    You are setting the bookmark of the recordset rst, not of the form. Perhaps you can use

    RunCommand acCmdRecordsGoToLast

  11. #11
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cloning Deep Levels (2003 SP2)

    The RunCommand etc displays the very last record. The form is sorted alphabetically by Site title rather than SiteID so the command displays the site name at the end of the alphabet rather than the highest record ID.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cloning Deep Levels (2003 SP2)

    OK, then try this:

    With Me.RecordsetClone
    .FindFirst "SiteID = " & lngNewSiteID
    If .NoMatch = False Then
    Me.Bookmark = .Bookmark
    End If
    End With

  13. #13
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cloning Deep Levels (2003 SP2)

    Perfect! Thank you so much!

Posting Permissions

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