issue with insert into sql

[sdel_nevo]

Hi Guys

im really sorry if this has been answered before, when running this code

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 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
            !OrderNumber = Nz(DMax("ordernumber", "tblorders"), 0) + 1
            !CustomerID = Me.CustomerID
            !Notes = Me.Notes
            ![Date Of Order] = Date
            ![VAT Rate] = Me.[VAT Rate]
               
            'etc for other fields.
            .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, Product Description ) " & _
                  "SELECT " & lngID & " As NewID, ProductID, QTY, Product Description " & _
                  "FROM [tblOrdersDetails] WHERE OrderID = " & Me.OrderID & ";"
                  
               Debug.Print strSql

               DBEngine(0)(0).Execute strSql, dbFailOnError
            Else
               MsgBox "Main record duplicated, but there were no related records."
            End If
            'Display the new Order.
            Me.Bookmark = .LastModified
         End With
      End If
   Else
   End If

I get Run-Time error '3134' Syntax error in INSERT INTO Statement

the code fails at this line

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

when viewing the output in the intermediate windows i get this result

INSERT INTO [tblOrdersDetails] ( OrderID, ProductID, QTY, Product Description ) SELECT 17 As NewID, ProductID, QTY, Product Description FROM [tblOrdersDetails] WHERE OrderID = 1;

this looks correct to me,

any ideas

Many thanks



Steve

[ranman256]

Build this SQL as a query. You may get better error messages that way.

[sdel_nevo]

Hi ranman256

many thanks for the quick reply, will try that will let you know how it goes

steve

[John_G]

Product Description has a space in it, so whenever you reference it you muse enclose it in square brackets : [Product Description]

Try this:

strSql = "INSERT INTO [tblOrdersDetails] ( OrderID, ProductID, QTY, [Product Description] ) " & _
      "SELECT " & lngID & " As NewID, ProductID, QTY, [Product Description] " & _
       "FROM [tblOrdersDetails] WHERE OrderID = " & Me.OrderID & ";"

[sdel_nevo]

Hi John_G

Oh my god, i had been looking at this for hours, never saw that, i feel such an idiot now

that works brilliantly

many many thanks
nearly pulled out all my hair lol

Steve

[John_G]

Glad to be of help. That is just one of many pitfalls of using spaces if field names. I'm as guilty as anyone of that, and have experienced the same issues.

[sdel_nevo]

Hi John_G

I think I need to go through this application and start to remove the spaces from at the fields with spaces in, could be a big job I think

once again many thanks for your help

steve