Wednesday, 15 May 2013

ms access - End of statement error -



ms access - End of statement error -

i trying assemble insert statement multiple line breaks. insertion includes both numerical textual data. maintain getting error, , cannot find syntactically wrong. can see me error?

currentdb.execute "insert 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 & ")"

i not sure quotes , ampersands for, causing problem. occur @ start , end of each value line. best set string in separate variable, , execute that, easier spot problems. might consider parameters, create life easier in case.

ssql = "insert 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 database set db = currentdb db.execute ssql dbfailonerror

last record inserted id

set rs = db.openrecordset("select @@identity id") lastid = rs("id")

parameter example:

ssql = "insert table1 (atext,anumber) values (@atext,@anumber)" dim qdf 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 id" set rs = qdf.openrecordset lastid = rs("id")

ms-access access-vba ms-access-2010

No comments:

Post a Comment