Code To Duplicate a Record with Subform data

[sdel_nevo]

Hi Guys



i have a problem with some code on two forms

on one form "frmCustomersEditOrders" i have this code that runs on a the onclick event of a button

this code works as expected

Private Sub DuplicateOrderButton_Click()
    On Error GoTo DuplicateOrderButton_Click_Error
    Dim iResponse                                              As Integer
   
    iResponse = MsgBox("Are You Sure You Want To Duplicate This Order?", vbYesNo, "Duplicate This Order")
    If iResponse = vbYes Then
        'Duplicate the mainform records and the related records in the subform.
        Dim StrSql                                         As String    'SQL statement.
        Dim lNgId                                          As Long    'Primary key value of the new record.
        'Save any edits created first before then duplicate the order
        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."
        Else
            'Duplicate the main record: add to form's clone.
            With Me.RecordsetClone
                .AddNew
                'then we enter the ordernumber by looking up the highest current value and adding 1 to it
                !OrderNumber = Nz(DMax("OrderNumber", "tblorders"), 0) + 1
                !CustomerID = Me.CustomerID
                '!Notes = Me.Notes
                ![DateOfOrder] = Date
                ![VatRate] = Me.[VatRate]
                ![HasBeenInvoiced] = Me.Has_Been_Invoiced
                '![Notes] = Me.Notes
               
                .Update
                'Save the primary key value, to use as the foreign key for the related records.
                .Bookmark = .LastModified
                lNgId = !OrderID
            
                'Duplicate the related records: append query.
                If Me.frmCustomersEditOrdersDetailsSubform.Form.RecordsetClone.RecordCount > 0 Then
                    StrSql = "INSERT INTO [tblOrdersDetails] ( OrderID, ProductID, QTY, [ProductDescription],[SalePrice] ) " & _
                        "SELECT " & lNgId & " As NewID, ProductID, QTY, [ProductDescription],[SalePrice] " & _
                        "FROM [tblOrdersDetails] WHERE OrderID = " & Me.OrderID & ";"
                  
                    'Debug.Print StrSql
                    DBEngine(0)(0).Execute StrSql
                Else
                    MsgBox "Main Record Duplicated, But There Were No Related Records."
                End If
                'Then we open the new order ready to enter notes as required
                Me.Bookmark = .LastModified
            End With
        End If
    Else
    End If
    
    'now we disbale the pay now button, so we cant pay Straght away as this has been coursing errors
    Me.Make_Payment_Button.Enabled = False
  
    On Error GoTo 0
    Exit Sub
DuplicateOrderButton_Click_Error:
    MsgBox "Error Has Been Found " & Err.Number & " (" & Err.Description & ") In The Procedure (DuplicateOrderButton_Click) Of (Sub Form_frmCustomersEditOrders)"
End Sub

on my second form "FrmSuppliersEditPurchaseOrders" i have this code on the onclick event of a button

this code alwas fails where the text is highlighted with the folloing error code "Run-time error 3164, Field cannot be updated"

i cant seem to get my head around why one works and the other does not

Dim iResponse                                              As Integer
   
    iResponse = MsgBox("Are You Sure You Want To Duplicate This Order?", vbYesNo, "Duplicate This Order")
    If iResponse = vbYes Then
        'Duplicate the mainform records and the related records in the subform.
        Dim StrSql                                         As String    'SQL statement.
        Dim lNgId                                          As Long    'Primary key value of the new record.
        'Save any edits created first before when duplicate the order
        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."
        Else
            'Duplicate the main record: add to form's clone.
            With Me.RecordsetClone
                .AddNew
                'then we enter the Purchaseordernumber by looking up the highest current value and adding 1 to it
                !PurchaseOrderNumber = Nz(DMax("PurchaseOrderNumber", "tblPurchaseOrders"), 0) + 1
                !SupplierID = Me.SupplierID
                '!Notes = Me.Notes
                ![DateOfOrder] = Date
                ![VatRate] = Me.[VatRate]
                '![HasBeenInvoiced] = Me.Has_Been_Invoiced
                '![Notes] = Me.Notes
               
                .Update
                'Save the primary key value, to use as the foreign key for the related records.
                .Bookmark = .LastModified
                lNgId = !PurchaseOrderID
            
                'Duplicate the related records: append query.
                If Me.frmSuppliersPurchaseOrdersDetailsSubForm.Form.RecordsetClone.RecordCount > 0 Then
                    StrSql = "INSERT INTO [tblPurchaseOrdersDetails] ( PurchaseOrderID, ProductID, QTY, [ProductDescription],[StockCostPrice] ) " & _
                        "SELECT " & lNgId & " As NewID, ProductID, QTY, [ProductDescription],[StockCostPrice] " & _
                        "FROM [tblPurchaseOrdersDetails] WHERE PurchaseOrderID = " & Me.PurchaseOrderID & ";"
                  
                    'Debug.Print StrSql
                    DBEngine(0)(0).Execute StrSql
                Else
                    MsgBox "Main Record Duplicated, But There Were No Related Records."
                End If
                'Then we open the new order ready to enter notes as required
                Me.Bookmark = .LastModified
            End With
        End If
    Else
    End If
    
    'now we disbale the pay now button, so we cant pay Straght away as this has been coursing errors
    Me.PaymentButton.Enabled = False

any help would be fantastic

Steve

[Minty]

Is PurchaseOrderNumber an autonumber field ? If it is you can't specify a value for it, access will allocate the next number it decides is valid.

Or for some reason you end up trying to duplicate an existing PurchaseOrderNumber , and its indexed as no duplicate ?

Add a

debug.print Nz(DMax("PurchaseOrderNumber", "tblPurchaseOrders"), 0) + 1

to see what value you are trying to add.

[sdel_nevo]

Hi Minty

Many thanks for the reply

the feild is not an autonumber, i have checked the index and its set to "Duplicates OK"

the result of the debug returns 4, which is correct as this would be the 4th record, i have 3 records currently in the table

Steve

[Minty]

Okay in that case the data / field is probably a red herring.

Do you have that record open anywhere else , maybe on another form? The error code is indicative of a record lock.

[sdel_nevo]

Hi Minty

now thats a thought, i will double check everything and get back in touch
m in a meeting very shortly so i appologise in advance if it takes me a while to update you

Steve

[sdel_nevo]

Hi Minty

been looking over the code, it appears that everything is identical, on both forms and in the tables, i cant find an open record anywhere nor can i see from the code how any could be open, other than the one i want to duplicate

Steve

[Minty]

Try recreating the form in a basic fashion from scratch.
I've seen posts inferring that sometimes form corruption can cause strange errors.

Did you copy the original (working) form and adjust it to create the non working one ??
Wonder if you did, if anything odd got missed in "translation".

[davegri]

You may need to do this:

       'Make sure there is a record to duplicate.
        If Me.NewRecord Then
            MsgBox "Select The Record To Duplicate."
        Else
            'Duplicate the main record: add to form's clone.
        DIM rs as DAO.Recordset
	Set rs = Me.Recordsetclone            
        With rs
                .AddNew
                'then we enter the Purchaseordernumber by looking up the highest current value and adding 1 to it
                !PurchaseOrderNumber = Nz(DMax("PurchaseOrderNumber", "tblPurchaseOrders"), 0) + 1
                !SupplierID = Me.SupplierID

[sdel_nevo]

Hi Minty / Davegri

sorry for the late reply, i have been away for a while

Minty
i copied the form (the working one) and adjusted the tables, this has resulted in the non working one as you sugested

davegri
added the code you sugested, but still get the same error

i will try to recreate from scratch to see if this helps

Steve