Duplicate main form and subforms (1 Viewer)

[Mina Garas Daniel]

Hi everyone
I have a question
I have main form FormA
Contain suborm FormB
This subform FormC

I need to use command button
Allows duplicate records in FormA, FormB and FormC

I try to use Allen Brown code
But it's works on FormA and FormB only
How I can duplicate records in 3 forms at once

Can anybody help
Allen Brown code

Microsoft Access tips: Duplicate the record in form and subform

How to duplicate the current record on a form in a Microsoft Access database, along with the related records in the subform.

allenbrowne.com

Thanks

 

[pbaldy]

To go 3 levels deep, you'd have to modify the code a fair amount. Basically, instead of running SQL to append the second level data, you'd need to loop records meeting that criteria. Within that loop, add a record using the top level method so you can get the ID, then run SQL using that to append the third level records.

 

[Mina Garas Daniel]

Could you please explain that on Allen Brown example.

Thanks alot in advance

 

[Uncle Gizmo]

Unfortunately not a solution to your particular problem! I developed some code which is listed in this answer to a similar question:-

Solved - Not sure why my code Isnt working any help?

Hi! I have taken a standard code for duplicating a form with its subform, but it falls over when trying to append the subform to its table.... Private Sub OrderCopy_Click() 'On Error GoTo Err_Handler 'Purpose: Duplicate the main form record and related records in the subform. Dim...

www.access-programmers.co.uk

from a stack overflow solution Here:-

MS Access - Is there an easy way to duplicate field information of a form and its subforms?

I want to duplicate a form with 3 subforms - to explain it simple: imagine a recipe (main form: some general data; sub form 1: list of ingredients, sub form 2: instructions; sub form 3: prices; som...

stackoverflow.com

I adapted the stackoverflow solution into a generic solution where you didn't need to add the subform names in to the code manually, the code finds the subform information automatically and duplicates the records of any subform Tagged with an X.

I posted a link to my solution because it might be possible to use the same idea on the next level of subforms, the subforms contained within the subforms. Mind you, it might be a bit of a head scratcher! They do get a bit like that when you try and go deep, unless you can come up with some way of iterating through the levels. Let's put it this way, I don't fancy doing it myself!

 

[pbaldy]

Could you please explain that on Allen Brown example.

Thanks alot in advance

I'll let UG take over.

 

[Uncle Gizmo]

Let's put it this way, I don't fancy doing it myself!

I'll let UG take over

I'm not willing or able to modify the code to do another subform level. I'm not even sure it's a good idea ....

 

[pbaldy]

Could you please explain that on Allen Brown example.

Thanks alot in advance

If UG's links were not a solution, attach a sample db here and I'll play with it.

 

[Pat Hartman]

Please at least try the Stack overflow solution. You have the code to do the top level. The middle level as others have pointed out, needs to be a VBA loop that inserts a single record at one time. Within that loop, you would run an append query to append the third level. The query takes two parameters. One to select the source using the original PK of the copied record and the second to provide a value for the FK taken from the newly generated PK.

No one is going to be able to give you the code you need. If you post the db, someone might code it for you since it isn't actually difficult once you understand the concept. I have posted a sample showing how to do this but I don't have a link handy.

I've never copied a structure more than three levels deep but the concept is the same. The top level is done as you have, all the middle levels are done with nested VBA .AddNew loops and the lowest level is done with an append query.

 

[Mina Garas Daniel]

Please at least try the Stack overflow solution. You have the code to do the top level. The middle level as others have pointed out, needs to be a VBA loop that inserts a single record at one time. Within that loop, you would run an append query to append the third level. The query takes two parameters. One to select the source using the original PK of the copied record and the second to provide a value for the FK taken from the newly generated PK.

No one is going to be able to give you the code you need. If you post the db, someone might code it for you since it isn't actually difficult once you understand the concept. I have posted a sample showing how to do this but I don't have a link handy.

I've never copied a structure more than three levels deep but the concept is the same. The top level is done as you have, all the middle levels are done with nested VBA .AddNew loops and the lowest level is done with an append query.

Dear Pat
I try your ideas but I failed because my skills in VBA are limited

I need to divide this case

Firstly I try duplicate form a and b successfully

But I need your help to duplicate certain record if Main form PK = 1 ,FK in form B contain many lines and form B has its PK

I try to put Form B PK as an identifier in vba code but it doesn't work any ideas to how fullfil this issue

Thanks

 

[pbaldy]

Should I ask for a sample db again, or just get out of the way?

 

[Mina Garas Daniel]

Here you are
Thanks in advance

 

[Pat Hartman]

Here is working code from one of my apps. Hopefully, you can follow the logic.

Private Sub cmdCopyQuote_Click()
On Error GoTo Err_Handler
'Purpose:   Duplicate the main form record and related records in the subform.
    Dim str2Sql             As String
    Dim strSQL              As String    'SQL statement.
    Dim NewQuoteID  As Long       'Primary key value of the new record.
    Dim OldQuoteID    As Long
    Dim NewQuoteDetailID As Long
    Dim db                      As DAO.Database
    Dim qd                      As DAO.QueryDef
    Dim FromQD          As DAO.QueryDef
    Dim FromRS           As DAO.Recordset
    Dim ToTD                As DAO.TableDef
    Dim ToRS                As DAO.Recordset

    If SecurityAdd = True Then
    Else
        Exit Sub
    End If

    'Save any edits first
    If Me.Dirty Then
        Me.Dirty = False
    End If

    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
        Exit Sub
    End If
    

    'Duplicate the main record: add to form's clone.
    OldQuoteID = Me.QuoteID
    With Me.RecordsetClone
        .AddNew
        !CustID = Me.cboCustID
        !JobName = Me.JobName
        !Notes = Me.Notes
        !TermsID = Me.TermsID
        !ShippingID = Me.ShippingID
        !CustConID = Me.cboCustConID
        !CustLocID = Me.cboCustLocID
        !QuoteNumber = Nz(DMax("QuoteNumber", "tblQuotes"), 0) + 1
        !QuoteDate = Date
        !Expires = Date + 30
        .Update

        'Save the primary key value, to use as the foreign key for the related records.
        .Bookmark = .LastModified
        NewQuoteID = !QuoteID
        'Display the new duplicate.
        Me.Bookmark = .LastModified
    End With

        'Duplicate the related Item records using DAO recordset.  Append Accessories with  append query inside loop
        Set db = CurrentDb()
        Set ToTD = db!tblquoteDetails
        Set ToRS = ToTD.OpenRecordset
        Set FromQD = db.QueryDefs!qQuoteCopyDetails
                FromQD.Parameters!EnterOldQuoteID = OldQuoteID
        Set FromRS = FromQD.OpenRecordset(dbOpenDynaset, dbSeeChanges)
        With FromRS
            .MoveFirst
            Do Until .EOF = True
                ToRS.AddNew
                ToRS!ItemNo = !ItemNo
                ToRS!EstID = !EstID
                ToRS!ModelNo = !ModelNo
                ToRS!Description = !Description
                ToRS!Qty = !Qty
                ToRS!Price = !Price
                ToRS!AccessPrice = !AccessPrice
                ToRS!QuoteID = NewQuoteID
                NewQuoteDetailID = ToRS!QuoteDetailID
                ToRS.Update
                
                
                ''  copy accessories
                Set qd = db.QueryDefs!qCopyAppendQuoteAcc
                    qd.Parameters!EnterOldQuoteDetailID = !QuoteDetailID
                    qd.Parameters!EnterNewQuoteDetailID = NewQuoteDetailID
                    qd.Execute dbSeeChanges
            
                .MoveNext
            Loop
        End With
        Me.SfrmQuoteDetails.Requery
        ToRS.Close
        FromRS.Close
        Set ToRS = Nothing
        Set FromRS = Nothing
        Set db = Nothing

Exit_Handler:
    Exit Sub

Err_Handler:
     Select Case Err.Number
        Case 3021, 2501   ' update cancelled
            Resume Exit_Handler
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume Exit_Handler
    End Select

End Sub

Here's the Append query for the third level. Pay attention to the arguments == [EnterNewQuoteDetailID] and [EnterOldQuoteDetailID]

Make sure you understand what the arguments are doing

INSERT INTO tblquoteacc ( QuoteDetailID, ItemNo, EstID, ModelNo, Description, Qty, Price )
SELECT [EnterNewQuoteDetailID] AS Expr1, tblQuoteAcc.ItemNo, tblQuoteAcc.EstID, tblQuoteAcc.ModelNo, tblQuoteAcc.Description, tblQuoteAcc.Qty, tblQuoteAcc.Price
FROM tblQuoteAcc
WHERE (((tblQuoteAcc.QuoteDetailID)=[EnterOldQuoteDetailID]));

 

[Mina Garas Daniel]

Thanks Pat
I will try it and inform you the result
Thanks again

 

[Mina Garas Daniel]

Dear @Pat Hartman

I read your code many times to understand the concept
i wrote my code following your steps as i understood
but when i click on command button nothing happen

as below the code i wrote
please guide me where i do mistakes


On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the subform.
Dim str2Sql As String
Dim strSQL As String 'SQL statement.
Dim NewQuoteID As Long 'Primary key value of the new record.
Dim OldQuoteID As Long
Dim NewQuoteDetailID As Long
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim FromQD As DAO.QueryDef
Dim FromRS As DAO.Recordset
Dim ToTD As DAO.TableDef
Dim ToRS As DAO.Recordset
If SecurityAdd = True Then
Else
Exit Sub
End If

'Save any edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Exit Sub
End If


'Duplicate the main record: add to form's clone.
OldQuoteID = Me.TRPK
With Me.RecordsetClone
.AddNew
!TRPK = Me.TRPK
!TrialDate = Me.TrialDate
!TrialBy = Me.TrialBy
!QC = Me.QC
.Update

'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
NewQuoteID = !TRPK
'Display the new duplicate.
Me.Bookmark = .LastModified
End With

'Duplicate the related Item records using DAO recordset. Append Accessories with append query inside loop
Set db = CurrentDb()
Set ToTD = db!TRD_RDLog
Set ToRS = ToTD.OpenRecordset
Set FromQD = db.QueryDefs!QRD_TrialLog
FromQD.Parameters!EnterOldQuoteID = OldQuoteID
Set FromRS = FromQD.OpenRecordset(dbOpenDynaset, dbSeeChanges)
With FromRS
.MoveFirst
Do Until .EOF = True
ToRS.AddNew
ToRS!TRPK = !TRPK
ToRS!PHIPPK = !PHIPPK
ToRS!RPFPType = !RPFPType
ToRS!ERP_Code = !ERP_Code
ToRS!Item_Eng = !Item_Eng
ToRS!Brand = !Brand
ToRS!Item_Arb = !Item_Arb
ToRS!RDCode = !RDCode
ToRS!Unit = !Unit
ToRS!Unit_Arb = !Unit_Arb
ToRS!TrialPurpose = !TrialPurpose
ToRS!Kitchen = !Kitchen
ToRS!PHIPNetWt = !PHIPNetWt
ToRS!Shelflife = !Shelflife
ToRS!Storageconditions = !Storageconditions
ToRS!SampleApproval = !SampleApproval
ToRS!SampleApprovalDate = !SampleApprovalDate
ToRS!Notes = !Notes
ToRS!RecipeDate = !RecipeDate
ToRS!QuoteID = NewQuoteID
NewQuoteDetailID = ToRS!QuoteDetailID
ToRS.Update


'' copy accessories
Set qd = db.QueryDefs!qCopyAppendQuoteAcc
qd.Parameters!EnterOldQuoteDetailID = !QuoteDetailID
qd.Parameters!EnterNewQuoteDetailID = NewQuoteDetailID
qd.Execute dbSeeChanges

strSQL = "INSERT INTO TFP_PHIPDTL ( TDPK, RawCode, Unit, PQty)" & _
"SELECT [EnterNewQuoteDetailID] AS Expr1, TFP_PHIPDTL.RawCode, TFP_PHIPDTL.Unit, TFP_PHIPDTL.PQty" & _
"FROM tblQuoteAcc WHERE (((tblQuoteAcc.QuoteDetailID)=[TDPK]))"

.MoveNext

Loop
End With
Me.FFP_PHIPLog.Requery
ToRS.Close
FromRS.Close
Set ToRS = Nothing
Set FromRS = Nothing
Set db = Nothing

Exit_Handler:
Exit Sub

Err_Handler:
Select Case Err.Number
Case 3021, 2501 ' update cancelled
Resume Exit_Handler
Case Else
MsgBox Err.Number & "--" & Err.Description
Resume Exit_Handler
End Select
End Sub

thanks in advnce

 

[Pat Hartman]

Remove the If that checks to see if the user is authorized to add a record. it is the first If.

You need to create your own append query modeled after the one I posted. The SQL does not belong in the VBA. My code references a querydef. Then replace "qCopyAppendQuoteAcc" with the name of YOUR query. The .Parameters statements are providing the OLD and NEW IDs to the query and then the .execute runs it. If you want to use embedded SQL, you have to change that section of code.

You should also change the names of the variables. In my case, I was copying a quote and so the variables reference OldQuoteID and NewQuoteID. Name the variables to whatever makes sense in your situation.