Syntax error in Insert Into statement

[Ashnolan]

I am having trouble with the insert into statement. I have modified a previously error free code to look at some new field names in the table but now I am getting this problem. What I am trying to do is split the line quantity from multiple items to one in each line, copying the relevant data down.

Could someone please have a look at the below code and let me know what I am doing wrong. Thanks in advance

Sub ChangeandUpdate()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim q As Integer
    Dim i As Integer
    Dim SQL As String
    Dim qd As QueryDef
 
    Set db = CurrentDb
 
    Set rs = _
        db.OpenRecordset("SELECT DealerInvoices.* FROM DealerInvoices WHERE (((DealerInvoices.LineQuantity)>1))")
 
    Do While Not rs.EOF
 
        q = rs!LineQuantity
 
        SQL = _
            "Insert into DealerInvoices ( CustomerNumber, InvoiceNumber, InvoiceName, InvoiceDate, " & _
            "ProductCode, ProductDescription, LineQuantity, LineValue, LineCost, SerialNumber, DeliveryName,  " & _
            "DeliveryAddressLine1, DeliveryAddressLine2, DeliveryAddressLine3, DInvoice, ClaimType, " & _
            "MonthPaid, [CustomerName], Location, Street, Town, ExclDealer, RebateAmount ) " _
            & "values ('" & rs!CustomerNumber & "', '" & rs!InvoiceNumber & "', '" _
            & rs!InvoiceName & "', #" & rs!InvoiceDate & "#, '" & rs!ProductCode & _
            "', '" & rs!ProductDescription & "', 1, " & rs!LineValue / q & ", " & _
            rs!LineCost / q & ", '" & rs!SerialNumber & "', '" & rs!DeliveryName & _
            "', '" & rs!DeliveryAddressLine1 & "', '" & rs!DeliveryAddressLine2 & _
            "', '" & rs!DeliveryAddressLine3 & "', '" & rs!DInvoice & "', '" & _
            rs!ClaimType & "', #" & rs!MonthPaid & "#, '" & rs![CustomerName] & _
            "', '" & rs!Location & "', '" & rs!Street & "', '" & rs!Town & "', " & _
            rs!ExclDealer & ", " & rs!RebateAmount & "')"
 
        Set qd = db.CreateQueryDef("", SQL)
 
        For i = 0 To (q - 2)
            qd.Execute
        Next i
 
        Set qd = Nothing
 
        rs.MoveNext
 
    Loop
 
    SQL = _
        "UPDATE DealerInvoices SET DealerInvoices.LineValue = [LineValue] / [LineQuantity]"
 
    Set qd = db.CreateQueryDef("", SQL)
 
    qd.Execute
 
    Set qd = Nothing
 
    SQL = _
        "UPDATE DealerInvoices SET DealerInvoices.LineCost = [LineCost] / [LineQuantity]"
 
    Set qd = db.CreateQueryDef("", SQL)
 
    qd.Execute
 
    Set qd = Nothing
 
    SQL = "UPDATE DealerInvoices SET DealerInvoices.LineQuantity = 1"
 
    Set qd = db.CreateQueryDef("", SQL)
 
    qd.Execute
 
    Set qd = Nothing
 
    MsgBox "Done"
 
End Sub

 

[Galaxiom]

Set a break point before so you can check the SQL variable for correct syntax. You may need to Debug.Print it due to the length.

BTW The sequence in the loop is not going to work because only the SQL with the last value for q will make it through to the Execute.

Also you don't need to create the querydef.
Just use:

Currentdb.Execute SQL, dbFailOnError

The dbFailOnError is required or the error will pass quietly by.

 

[Ashnolan]

Thanks Galaxiom I have now got it to work