MS Access Form - VBA Error - Run Time Error '3134' : Syntax Error in INSERT INTO statemt

Hi,

What is the value of?

"INSERT INTO tblOriginalValue_HGen([Link_ID],[OriginalValues],[OriginalUnit]) Values (" & Me.ID & "," & Me.OriginalValue & ", '" & Me.OriginalUnit & "')"

Select allOpen in new window

Regards

[Link_ID],[OriginalValues],[OriginalUnit] are three fields in tblOriginalValue_HGen and (" & Me.ID & "," & Me.OriginalValue & ", '" & Me.OriginalUnit & "') are textboxes in which Me.ID is Autonumber , Me.OriginalValue are numbers and Me.OriginalUnit is text.

make sure Me.OriginalValue do not contains comma, if yes, try to remove it.

Just want to see what is the value of the sql string when the error occurs

@Ryan, Me.OriginalValue do not contain comma at all.

@Rgonzo1971, how to see the value of SQL String? Should i enter something ?

before currentdb.execute...

try do this:

debug.print "INSERT INTO tblOriginalValue_HGen([Link_ID],[OriginalValues],[OriginalUnit]) Values (" & Me.ID & "," & Me.OriginalValue & ", '" & Me.OriginalUnit & "')"

Select allOpen in new window

see what's the output you get.

Don't see anything right off, so a suggestion:

Do:

strSQL =

Then :

CurrentDb.Execute strSQL

This lets you put a breakpoint on the strSQL line and view the statement as Access sees it before it executes.

Jim.

@Ryan Chong, it still gives error '3134' , Syntax Error in Insert Into.

it still gives error '3134' , Syntax Error in Insert Into.

yes, hence we try to suggest you to post the generated insert SQL script here, so we can verify your issue.

@Jim Dettman (Microsoft MVP/ EE MVE), it still highligh the DoCmd.Execute statement.

@Ryan Chong, how to generate SQL Script, kindly guide, sorry i am a beginner.

@Gustav , Have a look at image.

@Gustav , i removed long and it's working but can you please add one more condition in this mention below:

Private Sub btnEditRec_Click()
 

    blnSave = MsgBox("Are you sure you want to edit this record?", vbQuestion + vbYesNo, "Save Confirmation")
    If blnSave = vbYes Then
        Call Form_frmSWH_Single.CurrentZero
[i] If IsEmpty(Me.OriginalValue) Then[/i]
        MsgBox ("No Original to Add")
        Else
       CurrentDb.Execute ("INSERT INTO tblOriginalValue_HGen([Link_ID],[OriginalValues],[OriginalUnit]) Values (" & Me.ID & "," & Me.OriginalValue & ", '" & Me.OriginalUnit & "')")
[i]End If [/i]
        DoCmd.Close ObjectType:=acForm, ObjectName:=Me.Name, Save:=True

    Else
        Cancel = True
        Me.Undo
        DoCmd.Close ObjectType:=acForm, ObjectName:=Me.Name, Save:=False
    End If
        
    
End Sub

Select allOpen in new window

@Gustav, If there is some Original Value then do insert else display mesg and then execute the rest
....
if (not  empty)
Sql = "INSERT INTO tblOriginalValue_HGen ([Link_ID],[OriginalValues],[OriginalUnit]) Values (" & CSql(Me.ID) & "," & CSql(Me.OriginalValue) & ", " & CSql(Me.OriginalUnit) & ")"
else
MsgBox

I'm not sure I understand, what you wish, sorry.

/gustav

I wish to apply IF Condition before this statement : "Sql = "INSERT INTO tblOriginalValue_HGen ([Link_ID],[OriginalValues],[OriginalUnit]) Values (" & CSql(Me.ID) & "," & CSql(Me.OriginalValue) & ", " & CSql(Me.OriginalUnit) & ")" " ... i tried applying but it's not working if you could guide me in this.

@Gustav, thanks again , it worked :)

Great!

/gustav