Results 1 to 10 of 10
  1. #1
    Euler271 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    63

    Copy form then subform of a subform

    I found Allen Browne's website where he describes how to copy a form's records and any records for related subforms. Now, I need to copy not only the form's and related subform's records but also the records of the subform of a subform. For instance, I have a form for a particular person. That form has a subform for the person's addresses. Then each of the addresses has another set of data associated with it in a sub-subform.



    I need to find a clever append query that will copy the sub-subform's records.

    Thanks for the help.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Provide code or link to Allen's.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Euler271 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    63
    Here's the link to Allen Browne's site:

    http://allenbrowne.com/ser-57.html

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I have had to do something like this and code won't be simple. I don't have a db can test code with and don't want to build. If you want to provide db, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Euler271 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    63
    Sample.zip
    Quote Originally Posted by June7 View Post
    I have had to do something like this and code won't be simple. I don't have a db can test code with and don't want to build. If you want to provide db, follow instructions at bottom of my post.

    I'll try to build something while withholding sensitive information.

    I couldn't find any instructions about providing the db.

    I was able to zip the database to this post.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Please clarify. Why would you even think to duplicate record in tblPeople? Allen's code in this context makes no sense. It is intended for use in situation where returning customer is placing a new order for product previously ordered. It would not create new record in tblCustomers.

    I should have recognized this flaw in your concept when I first read your OP.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Euler271 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    63
    Quote Originally Posted by June7 View Post
    Please clarify. Why would you even think to duplicate record in tblPeople? Allen's code in this context makes no sense. It is intended for use in situation where returning customer is placing a new order for product previously ordered. It would not create new record in tblCustomers.

    I should have recognized this flaw in your concept when I first read your OP.

    The reason for it is not important. It has to do with fields that weren't included in my sample database.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    So you want to copy Humphrey Bogart in tblPeople (multiple Humphrey Bogart records?) and all of Bogie's address records and then all records related to each address?

    Duplicating Humphrey Bogart and address info makes no sense. However, regardless of tables and nature of data, the code logic will be the same and complex.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Euler271 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    63
    Quote Originally Posted by June7 View Post
    So you want to copy Humphrey Bogart in tblPeople (multiple Humphrey Bogart records?) and all of Bogie's address records and then all records related to each address?

    Duplicating Humphrey Bogart and address info makes no sense. However, regardless of tables and nature of data, the code logic will be the same and complex.

    The user will change the name and keep the other information.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Okay, really a new name in tblPeople. But why duplicate addresses? Save AddressID into a junction table that associates people with addresses. But it is unlikely multiple people will have identical addresses.

    And when/where is the new name entered/available? Record in tblPeople must be committed before the associated records can be created.

    I have had to code a procedure that uses data of existing records to create a new main record and its associated dependents. User selects the original record identifier from unbound combobox and also enters details that will apply to new record. Code then saves records with the new and replicated data. However, did not have to incorporate 3rd level.

    Suggest the Copy button opens another form for entry of new record for tblPeople and code behind that form creates associated records. Or once the new tblPeople record is committed pass the new ID back to frmMain for use in code to create associated records. Or have unbound controls on frmMain for user input of new tblPeople info.

    Figure out how to make the new person data available before proceeding.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 10-18-2017, 07:10 PM
  2. Copy Data from One Subform to another Subform.
    By Kaloyanides in forum Access
    Replies: 4
    Last Post: 05-12-2017, 05:04 PM
  3. Replies: 1
    Last Post: 02-25-2016, 01:39 PM
  4. Replies: 2
    Last Post: 03-04-2010, 02:04 PM
  5. copy info in subform as well as main form
    By Coolpapabell in forum Forms
    Replies: 0
    Last Post: 09-30-2009, 10:02 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums