HELP! - Run-time error '3034': Syntax error in insert into

[cathie]

Can anybody eyeball something that I can't see???

            MySql = "INSERT INTO All2 (LoanType, Street, Unit, City, State, Zip, Borrower, Vendor, DateRecd, Wholesaler, Processed, " & _
                                "UploadID, Result, Latitude, Longitude, ZillowID, Zestimate, ZRestimate, Zerror) VALUE " & _
                                "('" & LoanType & "','" & Street & "','" & Unit & "','" & City & "','" & State & "','" & Zip & _
                                "','" & Borrower & "'," & Vendor & ",#" & Format(DateRcvd, "mm/dd/yyyy") & "#," & Wholesaler & "," & Processed & _
                                "," & UploadID & "," & Result & ",'" & Latitude & "','" & Longitude & "','" & ZillowID & _
                                "'," & Zestimate & "," & ZRestimate & ",'" & ZError & "')"
            Debug.Print MySql
            CurrentDb.Execute MySql, dbFailOnError

Debug.Print Result = "INSERT INTO All2 (LoanType, Street, Unit, City, State, Zip, Borrower, Vendor, DateRecd, Wholesaler, Processed, UploadID, Result, Latitude, Longitude, ZillowID, Zestimate, ZRestimate, Zerror) VALUE ('2nd NPN','1 Main Street','','Ijamsville','MD','21754','John Smith',123,#10/01/2017#,,1,568,1,'39.31063','-77.330752','36881773',477935.00,2478.00,'')"

 

[pbaldy]

First thought is the empty value after the date (two commas together).

 

[arnelgp]

create a public function in a Standard Module to
generate the correct Delimiter for your VALUES.
in this case you would not need to worry if
it is date, integer, null is being feed in the
Query:

Public Function SQLFix(value As Variant) As String
    Select Case VarType(value)
    Case VbVarType.vbBoolean, VbVarType.vbByte, VbVarType.vbCurrency, _
        VbVarType.vbDouble, VbVarType.vbDecimal, VbVarType.vbInteger, _
        VbVarType.vbLong, VbVarType.vbLongLong, VbVarType.vbSingle
        SQLFix = value
    Case VbVarType.vbDate
        SQLFix = "#" & Format(value, "mm/dd/yyyy") & "#"
    Case VbVarType.vbString
        SQLFix = Chr(34) & value & Chr(34)
    Case VbVarType.vbNull
        SQLFix = "Null"
    End Select
End Function

then you need only to modify your SQL and remove the "'"
and "#":

MySql = "INSERT INTO All2 (LoanType, Street, Unit, City, State, Zip, Borrower, Vendor, DateRecd, Wholesaler, Processed, " & _
"UploadID, Result, Latitude, Longitude, ZillowID, Zestimate, ZRestimate, Zerror) VALUE " & _
"(" & SQLFix(LoanType) & "," & SQLFix(Street) & "," & SQLFix(Unit) & "," & SQLFix(City) & "," & SQLFix(State) & "," & SQLFix(Zip) & "," & _
"','" & Borrower & "'," & Vendor & ",#" & Format(DateRcvd, "mm/dd/yyyy") & "#," & SQLFix(Wholesaler) & "," & SQLFix(Processed) & "," & _
"," & SQLFix(UploadID) & "," & SQLFix(Result) & "," & SQLFix(Latitude) & "," & SQLFix(Longitude) & "," & SQLFix(ZillowID) & _
"," & SQLFix(Zestimate) & "," & SQLFix(ZRestimate) & "," & SQLFix(ZError) & ")"
Debug.Print MySql
CurrentDb.Execute MySql, dbFailOnError

 

[cathie]

Re: HELP! - Run-time error '3134': Syntax error in insert into

Thanks so much for the replies and assistance! I tried your solution with some minor tweaking and unfortunately, I still get the same result I tried with and without a closing semi-colon... the last 2 numeric figures are defined as currency fields in the database; I'm not sure if that makes a difference.

            MySql = "INSERT INTO All2 (LoanType, Street, Unit, City, State, Zip, Borrower, Vendor, DateRecd, Wholesaler, Processed, " & _
                    "UploadID, Result, Latitude, Longitude, ZillowID, Zestimate, ZRestimate, Zerror) VALUE " & _
                    "(" & SQLFix(LoanType) & "," & SQLFix(Street) & "," & SQLFix(Unit) & "," & SQLFix(City) & "," & SQLFix(State) & "," & SQLFix(Zip) & "," & _
                    SQLFix(Borrower) & "," & Vendor & "," & SQLFix(DateRcvd) & "," & SQLFix(Wholesaler) & "," & SQLFix(Processed) & _
                    "," & SQLFix(UploadID) & "," & SQLFix(Result) & "," & SQLFix(Latitude) & "," & SQLFix(Longitude) & "," & SQLFix(ZillowID) & _
                    "," & Zestimate & "," & ZRestimate & "," & SQLFix(ZError) & ");"
            Debug.Print MySql
            CurrentDb.Execute MySql, dbFailOnError

Debug.Print result = "INSERT INTO All2 (LoanType, Street, Unit, City, State, Zip, Borrower, Vendor, DateRecd, Wholesaler, Processed, UploadID, Result, Latitude, Longitude, ZillowID, Zestimate, ZRestimate, Zerror) VALUE ("2nd NPN","10009 Doctor Perry Road",Null,"Ijamsville","MD","21754","Lee Smith",123,#10/01/2017#,Null,1,568,1,"39.31063","-77.330752","36881773",477935.00,2480.00,Null);"

 

[arnelgp]

error 3034:
"You tried to commit or rollback a transaction without first beginning a transaction."

do you have your table "ALL2" open in the form/subform?
if so, are you in edit mode when?

if the form is Dirty (something has been changed and not
saved yet), you need to save it first:

Me.Dirty = False

also it is not wise that you have ALL2 table
open in subform/form and you are issuing an insert/update
query on it.

you MUST use Recordset insert/Update, eg:

Dim rs As DAO.Recordset
set rs = Me.RecordsetClone
rs.AddNew
''' set the fields here
...
rs.Update
rs.Close
set rs=Nothing

 

[cathie]

Hi arnelgp

I am a twit of major magnitude! I got it to work! I had an incorrect column name.

That SQLFix code is terrific though, and I appreciate you sharing it!

Thanks again for all your help!

 

[arnelgp]

Glad you figure it out

 

[Solo712]

Hi arnelgp

I am a twit of major magnitude! I got it to work! I had an incorrect column name.

That SQLFix code is terrific though, and I appreciate you sharing it!

Thanks again for all your help!

Let me guess: DateRcvd vs DateRecd ?

Best,
Jiri