Syntax Error in insert statement (1 Viewer)

[Happyz]

Hi everyone, i am encountering syntax error in my insert statement. How do i solve it?

Private Sub cmdAdd_Click()
 'add data to table
 CurrentDb.Execute "INSERT INTO InvestmentRecord(ID,[FSI/NonFSI],CompanyStock,Country,Branch,Currency,NumberOfShares,ActualCost,ImpairmentAllowance,GrossFairValueAdjustmentToEquity)" & _
                   " VALUES (" & Me.txtID & ",'" & Me.txtFSInonFSI & "','" & Me.txtSecurities & "','" & Me.txtCountry & "','" & Me.txtBranch & "','" & Me.txtCurrency & "','" & _
                     Me.txtNumberOfShare & "," & Me.txtOriginalCost & "," & Me.txtImpairmentAllowance & "," & Me.txtGrossfair & ")"

 

[arnelgp]

error on this line you have extra single quote:


Me.txtCurrency & "','" & _
Me.txtNumberOfShare


should be:


Me.txtCurrency & "'," & _
Me.txtNumberOfShare

 

[Happyz]

error on this line you have extra single quote:


Me.txtCurrency & "','" & _
Me.txtNumberOfShare


should be:


Me.txtCurrency & "'," & _
Me.txtNumberOfShare

I tried to change and it still prompt me the same error.

CurrentDb.Execute "INSERT INTO InvestmentRecord(ID,[FSI/NonFSI],CompanyStock,Country,Branch,Currency,NumberOfShares,ActualCost,ImpairmentAllowance,GrossFairValueAdjustmentToEquity)" & _
                   " VALUE (" & Me.txtID & ",'" & Me.txtFSInonFSI & "','" & Me.txtSecurities & "','" & Me.txtCountry & "','" & Me.txtBranch & "','" & Me.txtCurrency & "'," & _
                     Me.txtNumberOfShare & "'," & Me.txtOriginalCost & "'," & Me.txtImpairmentAllowance & "'," & Me.txtGrossfair & "')"

 

[arnelgp]

better put this in a Standard Module

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

the on your insert query:

Currentdb.Execute "Insert Into InvestmentRecord(ID, [FSI/NonFSI]," & _
"CompanyStock,Country,Branch,Currency,NumberOfShares," & _
"ActualCost,ImpairmentAllowance,GrowwFairVauleAdjustmentToEquity) " & _
"SELECT " & _
FixSQL(Me.txtID) & "," & _
FixSQL(Me.txtFSInoFSI) & "," & _
FixSQL(Me.txtSecurities) & "," & _
FixSQL(Me.txtCountry) & "," & _
FixSQL(Me.txtBranch) & "," & _
FixSQL(Me.txtCurrency) & "," & _
FixSQL(Me.txtNumberOfShare) & "," & _
FixSQL(Me.txtOriginalCost) & "," & _
FixSQL(Me.txtImpairmentAllowance) & "," & _
FixSQL(Me.txtGrossfair) & ")"


also, if ID is Autonumber field in your table,
you should not include it in the query.

 

[Happyz]

better put this in a Standard Module

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

the on your insert query:

Currentdb.Execute "Insert Into InvestmentRecord(ID, [FSI/NonFSI]," & _
"CompanyStock,Country,Branch,Currency,NumberOfShares," & _
"ActualCost,ImpairmentAllowance,GrowwFairVauleAdjustmentToEquity) " & _
"SELECT " & _
FixSQL(Me.txtID) & "," & _
FixSQL(Me.txtFSInoFSI) & "," & _
FixSQL(Me.txtSecurities) & "," & _
FixSQL(Me.txtCountry) & "," & _
FixSQL(Me.txtBranch) & "," & _
FixSQL(Me.txtCurrency) & "," & _
FixSQL(Me.txtNumberOfShare) & "," & _
FixSQL(Me.txtOriginalCost) & "," & _
FixSQL(Me.txtImpairmentAllowance) & "," & _
FixSQL(Me.txtGrossfair) & ")"


also, if ID is Autonumber field in your table,
you should not include it in the query.

Hi,

I am still facing the run-time error '3134':
Syntax error in INSERT INTO statement.
The below is the coding:

Private Sub cmdAdd_Click()

CurrentDb.Execute "Insert Into InvestmentRecord(ID, [FSI/NonFSI]," & _
"CompanyStock,Country,Branch,Currency,NumberOfShar es," & _
"ActualCost,ImpairmentAllowance,GrossFairVauleAdjustmentToEquity) " & _
"SELECT " & _
FixSQL(Me.txtID) & "," & _
FixSQL(Me.txtFSInonFSI) & "," & _
FixSQL(Me.txtSecurities) & "," & _
FixSQL(Me.txtCountry) & "," & _
FixSQL(Me.txtBranch) & "," & _
FixSQL(Me.txtCurrency) & "," & _
FixSQL(Me.txtNumberOfShare) & "," & _
FixSQL(Me.txtOriginalCost) & "," & _
FixSQL(Me.txtImpairmentAllowance) & "," & _
FixSQL(Me.txtGrossfair) & ","

End Sub

The yellow arrow is pointed at the last line of coding:
FixSQL(Me.txtGrossfair) & ","

 

[arnelgp]

i don't have a comma there at the end.
sorry i have wrong spelling for
GrossFairValueAdjustmentToEquity

CurrentDb.Execute "INSERT INTO InvestmentRecord(ID,[FSI/NonFSI],CompanyStock,Country,Branch,Currency,NumberOfShares,ActualCost,ImpairmentAllowance,GrossFairValueAdjustmentToEquity) " & _
" SELECT " & _
FixSQL(Me.txtID) & "," & _
FixSQL(Me.txtFSInonFSI) & "," & _
FixSQL(Me.txtSecurities) & "," & _
FixSQL(Me.txtCountry & "," & _
FixSQL(Me.txtBranch & "," & _
FixSQL(Me.txtCurrency) & "," & _
FixSQL(Me.txtNumberOfShare) & "," & _
FixSQL(Me.txtOriginalCost) & "," & _
FixSQL(Me.txtImpairmentAllowance) & "," & _
FixSQL(Me.txtGrossfair) & ")"

 

[Happyz]

i don't have a comma there at the end.
sorry i have wrong spelling for
GrossFairValueAdjustmentToEquity

Hi, i am unable to add the record from subform into my table. Hence, i have attached my table above, hope that you are able to help me out on this! Thank you. Have a great day!

 

[Minty]

Use a qdf and parameters to avoid the need for this. This thread https://www.access-programmers.co.uk/forums/showthread.php?t=297150 post #10 explains it very eloquently.

 

[arnelgp]

try this one.

 

[Happyz]

try this one.

Hi, there is still an error. I have screenshot the error message and the line of codes with errors. Hope you guys out there can help me out.

 

[arnelgp]

from which button is that, Edit?

 

[Happyz]

from which button is that, Edit?

the add button

 

[Happyz]

from which button is that, Edit?

Hi Arnelgp, the codes you've send me can be used now. Thank you so much for your help. Really appreciate it.

 

[arnelgp]

here again