Sub records? (1 Viewer)

[Pat Hartman]

You copy the PO using DAO so you can capture the generated autonumber. Then you run an append query to copy the details from the "old" ID and use the captured autonumber as the FK in the append query.

 

[mounty76]

 

[Pat Hartman]

Here's some sample code from one of my applications. The concept is the same as what you want. instead of the top level being PO, it is Quote and the quote has items that need to be copied same as the PO does.

Private Sub cmdCopyQuote_Click()
On Error GoTo Err_Handler

'Purpose:   Duplicate the main form record and related records in the subform.
    
    Dim NewQuoteID  As Long       'Primary key value of the new record.
    Dim OldQuoteID    As Long
    Dim db                      As DAO.Database
    Dim qd                      As DAO.QueryDef

    '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
        
        ''  copy details
        Set qd = db.QueryDefs!qCopyAppendQuoteAcc
            qd.Parameters!EnterOldQuoteID = OldQuoteID
            qd.Parameters!EnterNewQuoteID = NewQuoteID
            qd.Execute dbSeeChanges
      
    
        Me.SfrmQuoteDetails.Requery
        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

 

[mounty76]

Hi Pat, thank you so much, will try this out over the next couple of days...most likely with some more questions! lol. Thanks again and I'll keep you posted how it goes

 

[Pat Hartman]

Good luck

 

[mounty76]

Hi Pat,

Having some issues.......I don't get any error messages, but it also doesn't clone the record...here is the modified code:

FYI:

tblPO - The primary key is POID (autonumber)
tblDetail - The FK is called DetailID

So it should copy the mainform details into tblPO and give a new POID, then remember this new POID and paste it into DetailID for each line item in tblDetail thats in the subform....Think that's how it should work anyway


Private Sub Command48_Click()

On Error GoTo Err_Handler

'Purpose: Duplicate the main form record and related records in the subform.

Dim NewPOID As Long 'Primary key value of the new record.
Dim OldQuoteID As Long
Dim db As DAO.Database
Dim qd As DAO.QueryDef

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

'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.POID
With Me.RecordsetClone
.AddNew
!Quote = Null
!EDD = Null
!BudgetCode = Me.BudgetCode
!Currency = Me.Currency
!POCreatedBy = Me.POCreatedBy
!POTitle = Me.POTitle
!Supplier = Me.Supplier
!PODate = Date
!POSent = Null
!POApproved = Null
!POOrdered = Null
!POPartReceived = Null
!POReceived = Null
!POPaid = Null
!POClose = Null

.Update

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

'' copy details
Set qd = db.QueryDefs!qryPODetail
qd.Parameters!DetailID = OldQuoteID
qd.Parameters!DetailID = NewQuoteID
qd.Execute dbSeeChanges


Me.tblPODetail.Requery
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 If
End Sub

 

[Pat Hartman]

Don't be sloppy about your naming convention.
1. ALWAYS give controls proper names when you build them. That way you don't end up with controls named Command48 and events named Command48_Click. Changing the name now will orphan the code. Do it anyway and rename the orphaned procedures
2. I gave you existing code so you need to change the names to suit your situation. Be CONSISTANT. Don't change just one but not the others. Use the find/replace dialog change all OldQuoteID to OldPOID and all NewQuoteID to NewPOID, etc.
3. you are using the same parameter and populating it with different values:
qd.Parameters!DetailID = OldQuoteID
qd.Parameters!DetailID = NewQuoteID

 

[mounty76]

Thanks Pat, have made the changes as above and renamed the control to Clone_Click()

It clones the PO now and gives it a new POID but then I get an error code 91 and it doesn't copy the subform data, I have this code below but have tried a few different options but can't seem to figure it out

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

'' copy details
Set qd = db.QueryDefs!qryPODetail
qd.Parameters!EnterOldPOID = OldPOID
qd.Parameters!EnterNewPOID = NewPOID
qd.Execute dbSeeChanges


I think the parameters should only be one which should be qd.Parameters!DetailID = NewPOID

But this doesn't work either. FYI I've no primary key on the PODetail table, only the FK

 

[Pat Hartman]

You need BOTH the old and the new PO in the query. The old PO is used in the Where clause and the new PO is used in the select clause . Post your qryPODetail so we can see the SQL String please.

 

[mounty76]

Ah OK, thanks for this, here is the SQL from qryPODetail

SELECT tblPODetail.DetailID, tblPODetail.ItemDescription, tblPODetail.Quanity, tblPODetail.BudgetCode, tblPODetail.Currency, tblPODetail.Price, tblPODetail.VAT, tblPODetail.TotalPrice, tblPODetail.EDD, tblPODetail.Ordered, tblPODetail.Received
FROM tblPODetail;

 

[Pat Hartman]

Sorry, I thought my example was clear due to the name of the query being run. It must be an append query. It copies rows using a where clause that references the OldPO and appends rows using the NewPO as the value for the FK.

 

[mounty76]

Sorry for delay....busy with the day job!! Thanks Pat, all working now, very much appreciated!!

 

[Pat Hartman]

You're welcome