What's wrong with this INSERT syntax???

[puuts]

Hello,

I'm receiving an error "Syntax error in INSERT INTO statement" for the below code. I can't figure it out where/what is the error. Please help.

CurrentDb.Execute "INSERT INTO tblTrans(fdsname, project, so) " & _
                " VALUES (" & txt_fds & " ,'" & txt_name & "','" & txt_so & "')"

 

[pbaldy]

I'd use this method to see how the finished SQL is coming out:

http://www.baldyweb.com/ImmediateWindow.htm

Offhand, I'd take out the space after VALUES, but not sure that's causing the error.

 

[plog]

My guess is txt_fds doesn't contain numeric data, but you are inserting it like it does. Of course, pbaldy has the best solution--find out exactly what that SQL is, don't just guess by looking at your code.

 

[puuts]

Hello pbaldy,

Thanks for the reply. I've tried your suggestion but it seems it didn't save in the database? Or do I missed something??

Private Sub cmdSAVE_Click()
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb()
strSQL = "INSERT INTO tblTrans(fdsname, project, so) " & _
                " VALUES(" & txt_fds & ",'" & txt_name & "','" & txt_so & "')"
                Debug.Print strSQL
                
MsgBox ("Add Successfull!")
End Sub

 

[puuts]

Hello plog,

txt_fds contains names, and I've set the fdsname in the table to text. But it seems it didn't populate in the database

 

[pbaldy]

The SQL should display in the Immediate window, as shown in the link. You still need to execute the SQL; this is just a debugging technique.

 

[plog]

If txt_fds doesn't contain numeric data, you need to put single quotes around it like you have done for txt_name and txt_so

 

[puuts]

I've now added the execute SQL command, but everytime it runs, it always prompting "to append". I've just only want to insert the record not to append

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb()
strSQL = "INSERT INTO tblTrans(fdsname,project,so) " & _
                " VALUES(" & cb_fds & "," & txt_name & "," & txt_so & ")"
                Debug.Print strSQL
 
                DoCmd.RunSQL strSQL

 

[pbaldy]

What would you consider to be the difference?