Solved Not sure why my code Isnt working any help?

[Sodslaw]

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 strSQL As String    'SQL statement.
    Dim lngID As Long       'Primary key value of the new record.
  
    '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."
    Else
        'Duplicate the main record: add to form's clone.
        With Me.RecordsetClone
            .AddNew
               '!OwnerID = Me.OwnerID
                !TBLCustomerID = Me.TBLCustomerID
                !OrderDate = Me.OrderDate
                !OrderType = Me.OrderType
                !EmployeeID = Me.EmployeeID
                !ProjectRef = Me.ProjectRef
              ' !PONo = Me.PONo
                !SalesRegion = Me.SalesRegion
                !Commision = Me.Commision
              ' !SpecNo = Me.SpecNo
                !Notes = Me.Notes
                !OrderMainText = Me.OrderMainText
                !DocumentsInc = Me.DocumentsInc
                !DrawInc = Me.DrawInc
               '!SupplyType = Me.SupplyType
            .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.[FRmQ_OrderItemSUB].Form.RecordsetClone.RecordCount > 0 Then
                strSQL = "INSERT INTO TblOrderitem ( OrderMainLink, PartNo, Description, Qty, UnitPrice, SubPrice, OrderItem_Spec, Location, Supply, DelTime ) " & _
                "SELECT " & lngID & " As NewID, PartNo, Description, Qty, UnitPrice, SubPrice, OrderItem_Spec, Location, Supply, DelTime " & _
                "FROM TblOrderitem  WHERE OrderLinkID = " & Me.OrderID & ";"
                Debug.Print strSQL
                DBEngine(0)(0).Execute strSQL, dbFailOnError
            Else
                MsgBox "Main record duplicated, but there were no Order Items to Copy."
            End If
          
            'Display the new duplicate.
            Me.Bookmark = .LastModified
        End With
    End If

Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
    Resume Exit_Handler

End Sub

Reports an RT error 1034 "Too Few Parameters. Expected 1"
Error is @ Line

 DBEngine(0)(0).Execute strSQL, dbFailOnError

(dbFailOnError=128)
Debug.Print strSQL ....
INSERT INTO TblOrderitem ( OrderMainLink, PartNo, Description, Qty, UnitPrice, SubPrice, OrderItem_Spec, Location, Supply, DelTime ) SELECT 113 As NewID, PartNo, Description, QTY, UnitPrice, SubPrice, OrderItem_Spec, Location, Supply, DelTime FROM TblOrderitem WHERE OrderLinkID = 84;

Im pretty sure the rest of the code is fine, but cant figure it out the error, please help.

 

[Gasman]

Does the sql work if you paste it into a sql window and run it?

 

[Sodslaw]

@Gasman thanks for your speedy reply
The SQL i have tried...

INSERT INTO TblOrderitem ( OrderMainLink, PartNo, Description, Qty, UnitPrice, SubPrice, OrderItem_Spec, Location, Supply, DelTime )
SELECT 113 As NewID, PartNo, Description, Qty, UnitPrice, SubPrice, OrderItem_Spec, Location, Supply, DelTime
FROM TblOrderitem  WHERE OrderLinkID = 24;

It asks for the OrderLinkID once again in a little pop up (is this the problem?) if I enter 24 result is, "You are about to paste 39 rows"
if i enter 113 as the OrderLinkID then it shows the result "You are about to paste 0 rows"

so im not sure if its working properly or not. 39 or 0 rows is not correct it should be 9 rows (39 is all records)

Hope this helps

 

[Gasman]

Sounds like it does not recognise orderlinkid?
Is that the correct name? Try prefixing with table name.
However that is your initial problem. How many records get updated is down to your data.

 

[Sodslaw]

@gasmanAll working now thank you... I needed your fresh pair of eyes and experience. issue was naming of the feild OrderLinkID