Results 1 to 8 of 8
  1. #1
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194

    How Do I Duplicate a main form record and subform record?

    I've seen some articles related to this subject, but I haven't seen one that really encompasses what I am trying to do.

    I have a tabbed form with 10 miscellaneous subforms. Each table has one field that I use to link it to the main form. That field is called "CO". If you type "C05525" in the CO field of a pre-form and open the tabbed form, all subforms will be populated with the associated records.

    What I would like is to add a button that says "Save As" but really it duplicates all records on the main form and subforms while asking for a new CO. When you enter the new CO, that value is added to the CO field of every duplicated record.

    How do I go about doing this?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    use vba to append a copy of the main form record to the table (with a new PK), retrieve that PK and follow on by looping through all the subforms and their records appending those records, but with the new PK for the FK.

    Code would be something like

    dim db as dao.database
    dim rst as dao.recordset
    dim newPK as long

    set db=curentdb
    'insert new main record
    db.execute "INSERT INTO maintable (Field1, Field2....) VALUES (" & ctrl1 & "," & ctrl2 &.....&")"
    'retrieve the PK
    set rst=db.openrecordset("SELECT @@Indentity")
    newPK=rst.fields(0)

    for each subform recordsouce (i.e. child records)
    ' this depends on how your tables and relationships are set up, assumption is they are table based

    db.execute "INSERT INTO subtable1 (FK,Field1, Field2...) SELECT " & newPK & ", Field1, Field2... FROM subTable1 WHERE FK=" & me.PK
    db.execute "INSERT INTO subtable2....
    db.execute "INSERT INTO subtable3....
    etc

    Each table has one field that I use to link it to the main form
    this is your FK field, the PK is your primary key of the main table - usually autonumber

  3. #3
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    Thanks for the response Ajax,

    Can you break this down a little further for me as I'm still a beginner at coding.

    ("SELECT @@Indentity")
    Do I type this exactly as you have?

    ' this depends on how your tables and relationships are set up, assumption is they are table based
    My tables are related based on this field "CO".

    Also, I cannot get past the SQL statement since I have so many fields in my main table. I keep getting Error Expected: End Of Statement on the line that starts "VALUES"... Could you tell me how to write this?
    Code:
    Private Sub Command775_Click()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim NewPK As Long
    
    
        Set db = CurrentDb
        'insert new main record
        db.Execute "INSERT INTO tbl_MBR_Gummy ([ItemNumber],[Description],[Remarks],[Theoretical Premix Brix],[Theoretical Post-Cook Brix],[TC1],[TC2],[SKID 3 Overage],[SKID 5 Overage],[SKID 6 Overage],[SKID 7 Overage],[We01],[We02],[We03],[We04],[We05],[S101],[S102],[S103],[S104],[S105],[S106],[S401],[UKSC01],[UKSC02],[UKS01],[UKS02],[UKP01],[UKP02],[UKP03],[UKP04],[UKP05],[UKP06],[UKP07],[UKP08],[UKP09],[UKP10],[UKP11],[MOP01],[MOP02],[MOP03],[MOP04],[MOP05],[MOP06],[MOP07],[MOP08],[CRP01],[CRP02],[CRP03],[CRP04],[MF01],[MF02],[MF03],[MF04],[MF05],[Flavor 1],[Flavor 2],[Flavor 3],[MF06],[MF07])&_"
        VALUES ("&[ItemNumber]&","&[Description]&","&[Remarks]&","&[Theoretical Premix Brix]&","&[Theoretical Post-Cook Brix]&","&[TC1]&","&[TC2]&","&[SKID 3 Overage]&","&[SKID 5 Overage]&","&[SKID 6 Overage]&","&[SKID 7 Overage]&","&[We01]&","&[We02]&","&[We03]&","&[We04]&","&[We05]&","&[S101]&","&[S102]&","&[S103]&","&[S104]&","&[S105]&","&[S106]&","&[S401]&","&[UKSC01]&","&[UKSC02]&","&[UKS01]&","&[UKS02]&","&[UKP01]&","&[UKP02]&","&[UKP03]&","&[UKP04]&","&[UKP05]&","&[UKP06]&","&[UKP07]&","&[UKP08]&","&[UKP09]&","&[UKP10]&","&[UKP11]&","&[MOP01]&","&[MOP02]&","&[MOP03]&","&[MOP04]&","&[MOP05]&","&[MOP06]&","&[MOP07]&","&[MOP08]&","&[CRP01]&","&[CRP02]&","&[CRP03]&","&[CRP04]&","&[MF01]&","&[MF02]&","&[MF03]&","&[MF04]&","&[MF05]&","&[Flavor 1]&","&[Flavor 2]&","&[Flavor 3]&","&[MF06]&","&[MF07]&")"
        'Retrieve the PK
        Set rst = db.OpenRecordset("SELECT @@Identity")
        NewPK = rst.Fields(0)
    Sorry if these sound like basic questions, but they're escaping my comprehension.
    Last edited by lccrews; 02-20-2018 at 12:10 PM. Reason: Got stuck on SQL statement

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Do I type this exactly as you have?
    No typo on my part - should be identity

    Also, I cannot get past the SQL statement since I have so many fields in my main table
    looking at your sql, looks like you have a bad case of poor table design. Looks like the line above is not correct


    ],[MF06],[MF07])&_"


    should be

    ],[MF06],[MF07]) " &_

    But you will also have a problem passing the text values, they need to be surrounded with single quotes

    ....", '" & [Description] & "',".....

    and if any of them are dates, surround with # and format as mm/dd/yyyy e.g

    ....", #" & format(datefield,"mm/dd/yyyy") & "#, "....

    Also note that description (and desc) are reserved words, using them for field names is likely to cause problems down the line. And better not to use spaces in field names.

    Another tip - create your sql as a string then on the following line put

    debug.print sqlstr

    and your execute would become

    db.execute sqlstr

    this will appear in the immediate window and you can copy and paste to a new query and try to run it, you will get more informative error information (if there are any)

  5. #5
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    should be

    ],[MF06],[MF07]) " &_
    I get Error: Invalid Character and the underscore is highlighted.

    like you have a bad case of poor table design
    Can you point me in the right direction of fixing this problem? I need this code to work, but I want to do it the right way. I'm still in the development phase of this project so no large amounts of data have been dumped in yet. There's still time to fix my design!

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    there should be a space between & _

    Can you point me in the right direction of fixing this problem?
    google/bing 'normalisation'. Also look on this forum for help and advice, but do start a new thread (and include in that thread details of tables and relationships) since it is a different subject.

    From the look of your code, it looks like you have an 'excel' mindset. Excel typically stores and presents data in one horizontal view - so the 'table' is 'short and wide' with many duplicate values going through the rows. Access (or any database) stores data in tables and presents via queries through forms and reports. It stores data 'tall and narrow' and vertically, with no duplicates and no calculations (which are done in queries). Any sign of numbered field names such as your UKP, TC, MOP etc suggests these should be in their own tables, hence my comment. But there are a few occasions where this is perfectly valid, but without knowing your business, what the db is supposed to do and the processes involved.....

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    lccrews_Insert SQL.zip
    Take a look at this. It does what you want with 5 identical subform tables on the tab control.
    Easy to expand to more tables.

  8. #8
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    Thanks Dave, this should work quite well. I'm in the process of normalizing my data right now (correcting field names, correcting relationships, etc) but I will apply this when I'm finished. Thanks again!

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

Similar Threads

  1. Replies: 10
    Last Post: 07-05-2017, 11:31 AM
  2. Replies: 6
    Last Post: 05-09-2017, 09:13 AM
  3. Replies: 10
    Last Post: 12-17-2014, 03:31 PM
  4. Replies: 1
    Last Post: 11-21-2013, 12:17 PM
  5. Replies: 6
    Last Post: 08-22-2012, 03:24 AM

Tags for this Thread

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