End of statement error

Question

I am trying to assemble an insert statement with multiple line breaks. The insertion includes both numerical as well as textual data. I keep getting an error, and I cannot find the where I am syntactically wrong. Can anyone see me error?

CurrentDb.Execute "INSERT INTO tblCustParts (CustPartNum,CustomerID,Alloy,Temper,Finish,FormType,Gauge,GaugeAimPlus,GaugeAimMinus," & _
        "GaugeGuarPlus,GaugeGuarMinus,Width,WidthAimPlus,WidthAimMinus,WidthGuarPlus,WidthGuarMinus,Length,LengthAimPlus,LengthAimMinus," & _
        "LengthGuarPlus,LengthGuarMinus,Diameter,DiameterAimPlus,DiameterAimMinus,DiameterGuarPlus,DiameterGuarMinus,CircleShear,IDMin," & _
        "ODMin,ODMax,LabelAs,ProduceAs,ShopPaperNotes,EyeOrientation,CoreType,PackingNotes,AQ,PaperInterleave,HeatTreatedSkids,HasRecipeFlag," & _
        "QRRExists,TensionLevel,MSKCoreSpec,ChemCertsReq,PhysCertsReq,PhysAndChemCertsReq,AAStandard,ASTM_B209_10)" & _
        "VALUES('" & Part & "', " & Me.Customer & ", '" & Alloy & "', '" & Temper & "', '" & Finish & "', '" & FormType & "', " & Me.Gauge & ", "" & _
        " & Me.Gauge & ", " & Me.GaugeAimPlus & ", " & Me.GaugeAimMinus & ", " & Me.GaugeGuarPlus & ", " & Me.GaugeGuarMinus & ", "" & _
        " & Me.WidthEntry & ", " & Me.WidthAimPlus & ", " & Me.WidthAimMinus & ", " & Me.WidthGuarPlus & ", " & Me.WidthGuarMinus & ", "" & _
        " & Me.LengthEntry & ", " & Me.LengthAimPlus & ", " & Me.LengthAimMinus & ", " & Me.LengthGuarPlus & ", " & Me.LengthGuarMinus & ", "" & _
        " & Me.Diameter & ", " & Me.DiameterAimPlus & ", " & Me.DiameterAimMinus & ", " & Me.DiameterGuarPlus & ", "" & _
        " & Me.DiameterGuarMinus & ", " & Me.CS & ", " & Me.IDMin & ", " & Me.ODMin & ", " & Me.ODMax & ", '" & Me.LabelAs & "', '"" & _
        " & Me.ProduceAs & "', '" & Me.ShopPaperNotes & "', '" & Me.EyeOrientation & "', '" & Me.CoreType & "', '" & Me.PackingNotes & "', "" & _
        " & Me.AQ & ", " & Me.PaperInterleave & ", " & Me.HeatTreatedSkids & ", " & Me.HasRecipeFlag & ", " & Me.QRRExists & ", "" & _
        " & Me.TensionLevel & ", " & Me.MSKCoreSpec & ", " & Me.ChemCertsReq & ", " & Me.PhysCertsReq & ", " & Me.PhysAndChemCertsReq & ", "" & _
        " & Me.AAStandard & ", " & Me.ASTM_B209_10 & ")"

Fionnuala · Accepted Answer · 2013-03-01T10:54:05.243

I am not sure what the extra quotes and ampersands are for, but they are causing the problem. They occur at the start and end of each Value line. It is best to put your string in a separate variable, and then execute that, it is easier to spot problems. You might also like to consider parameters, they will make your life easier in this case.

ssql = "INSERT INTO tblCustParts (CustPartNum,CustomerID,Alloy,Temper,Finish,FormType,Gauge,GaugeAimPlus,GaugeAimMinus," & _
        "GaugeGuarPlus,GaugeGuarMinus,Width,WidthAimPlus,WidthAimMinus,WidthGuarPlus,WidthGuarMinus,Length,LengthAimPlus,LengthAimMinus," & _
        "LengthGuarPlus,LengthGuarMinus,Diameter,DiameterAimPlus,DiameterAimMinus,DiameterGuarPlus,DiameterGuarMinus,CircleShear,IDMin," & _
        "ODMin,ODMax,LabelAs,ProduceAs,ShopPaperNotes,EyeOrientation,CoreType,PackingNotes,AQ,PaperInterleave,HeatTreatedSkids,HasRecipeFlag," & _
        "QRRExists,TensionLevel,MSKCoreSpec,ChemCertsReq,PhysCertsReq,PhysAndChemCertsReq,AAStandard,ASTM_B209_10)" & _
        "VALUES('" & Part & "', " & Me.Customer & ", '" & Alloy & "', '" & Temper & "', '" & Finish & "', '" & FormType & "', " & Me.Gauge & ", " & _
        Me.Gauge & ", " & Me.GaugeAimPlus & ", " & Me.GaugeAimMinus & ", " & Me.GaugeGuarPlus & ", " & Me.GaugeGuarMinus & ", " & _
        Me.WidthEntry & ", " & Me.WidthAimPlus & ", " & Me.WidthAimMinus & ", " & Me.WidthGuarPlus & ", " & Me.WidthGuarMinus & ", " & _
        Me.LengthEntry & ", " & Me.LengthAimPlus & ", " & Me.LengthAimMinus & ", " & Me.LengthGuarPlus & ", " & Me.LengthGuarMinus & ", " & _
        Me.Diameter & ", " & Me.DiameterAimPlus & ", " & Me.DiameterAimMinus & ", " & Me.DiameterGuarPlus & ", " & _
        Me.DiameterGuarMinus & ", " & Me.CS & ", " & Me.IDMin & ", " & Me.ODMin & ", " & Me.ODMax & ", '" & Me.LabelAs & "', '" & _
        Me.ProduceAs & "', '" & Me.ShopPaperNotes & "', '" & Me.EyeOrientation & "', '" & Me.CoreType & "', '" & Me.PackingNotes & "', " & _
        Me.AQ & ", " & Me.PaperInterleave & ", " & Me.HeatTreatedSkids & ", " & Me.HasRecipeFlag & ", " & Me.QRRExists & ", " & _
        Me.TensionLevel & ", " & Me.MSKCoreSpec & ", " & Me.ChemCertsReq & ", " & Me.PhysCertsReq & ", " & Me.PhysAndChemCertsReq & ", " & _
        Me.AAStandard & ", " & Me.ASTM_B209_10 & ")"


Dim db As database
Set db = CurrentDB
db.Execute ssql dbFailOnError

Last record inserted ID

Set rs = db.OpenRecordset("select @@identity as id")
LastID = rs("id")

Parameter example:

ssql = "INSERT INTO Table1 (Atext,Anumber) Values (@AText,@Anumber)"
Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("", ssql)
qdf.Parameters("@atext") = "abc"
qdf.Parameters("@Anumber") = 1
qdf.Execute dbFailOnError

Last record inserted ID

qdf.SQL = "select @@identity as id"
Set rs = qdf.OpenRecordset
LastID = rs("id")

Thank you Remou. Perfect solution and great advice, as usual! — prayingmantes, Feb 18 '13 at 17:16
Another question for you @Remou. How do I return the primary key of that inserted record and store it in a form control please? — prayingmantes, Feb 26 '13 at 15:59
I added a note. If you are working with a recordset, you can also check out [lastmodified](http://msdn.microsoft.com/en-us/library/office/bb208485(v=office.12).aspx) — Fionnuala, Mar 01 '13 at 10:55

End of statement error

1 Answers1

Linked