Insert Into Query help

[teambond]

Hi,

I'm trying to duplicate the records in a subform to a new form but keep getting a too few parameters error. Can anyone spot my issue? I've been staring at it all day.

strSql = "INSERT INTO [OrderDetailT] ( OrderID, ProductID, Quantity, DiscountPercentage ) " & _
"SELECT " & lngID & " As NewOrderID, ProductID, Quantity, DiscountPercentage " & _
"FROM [OrderDetailT] WHERE OrderNumber = " & Me.OrderNumber & ";"

The debug.print comes out as below
INSERT INTO [OrderDetailT] ( OrderID, ProductID, Quantity, DiscountPercentage ) SELECT 49 As NewOrderID, ProductID, Quantity, DiscountPercentage FROM [OrderDetailT] WHERE OrderT!OrderNumber = 11;

Thanks.

 

[Galaxiom]

Your story is not accurate.

This:
"WHERE OrderNumber = " & Me.OrderNumber

Will not produce this:
WHERE OrderT!OrderNumber = 11

 

[teambond]

Okay, but can you see any issue with the query?

 

[Galaxiom]

Most common problem is misspelled names.

It would appear you need to pay more attention to detail, literally.
[OrderDetailT] WHERE OrderT!OrderNumber = 11

BTW To trouble shoot such problems, paste the SQL into the SQL view of a new query in the query designer. When the query is run the designer will tell you the name of the parameter it is expecting.

 

[teambond]

Hi,

I'm still struggling with this one. Any help would be appreciated. I probably should have added more of the code where I am trying to duplicate the order details from a previous order. I am using Allen Browne's code ser-57.

Attached is the whole code -

Dim strSql As String    'SQL statement.
    Dim lngID As Long       'Primary key value of the new record.
 
    'Save any edits first
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
    Else
        'Duplicate the main record: add to form's clone.
        With Me.RecordsetClone
            .AddNew
                !CustomerNumber = Me.CustomerNumber
                !OrderStatus = 1
            .Update
 
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            lngID = !OrderNumber
 
 
            'Duplicate the related records: append query.
            If Me.[OrderDetailSubF].Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO [OrderDetailT] ( OrderID, ProductID, Quantity, DiscountPercentage ) " & _
                    "SELECT " & lngID & " As OrderID, ProductID, Quantity, DiscountPercentage " & _
                    "FROM [OrderDetailT] WHERE OrderNumber = " & Me.OrderNumber & ";"
                Debug.Print strSql
                CurrentDb.Execute strSql, dbFailOnError
            End If
 
            'Display the new duplicate.
            Me.Bookmark = .LastModified
        End With
    End If
End Sub