INSERT INTO question

[accessguy52]

Hi all - I'm trying to insert 3 flds from a form into another table when a Save Record button is clicked. However, I need to fill up the ENTIRE recordset with those 3 flds. Access keeps giving me a "INSERT INTO syntax error" message when I run this code. A debug step shows that the program drops out at the Execute statement as shown below. So the Add and Update statements passed over. Can it be done soemhow? Code is below. Thanks for any interest.

'Dim rs As Recordset
'Dim rst As Recordset
'Dim db As Database

'Set db = CurrentDb
'Set rst = db.OpenRecordset("tblRisk_Severity_Levels")
'Set rs = db.OpenRecordset("tblProject2")

'Dim strSQL As String
'Dim str1, str2, str3 As String

'str1 = Me!Title
'str2 = Me!FP_ID
'str3 = Me!PM_Name

'strSQL = "INSERT INTO tblRisk_Severity_Levels (Project_Name, Project_Number, Project_Mgr) " _
'& "VALUES " & str1 & str2 & str3 & ";"


'Do While Not rst.EOF
' db.Execute strSQL
' rst.Update
' rst.AddNew
'Loop

'rst.Close
'rs.Close

accessguy52

 

[Golom]

You need to enclose the list of values in parentheses and each value (if they are strings) in quotes separated by commas.
[blue][tt]
strSQL = "INSERT INTO tblRisk_Severity_Levels (Project_Name, Project_Number, Project_Mgr) " & _
"VALUES ('" & str1 & "','" & str2 "','" & str3 & "');"
[/tt][/blue]

 

[jksmi]

Hi, I think it maybe because of you values statement... try adding the highlighted bits
& "VALUES [COLOR=red yellow]('[/color]" & str1 [COLOR=red yellow]& "', "[/color] & str2 [COLOR=red yellow]& ", '" [/color] & str3 & [COLOR=red yellow]"')[/color] ;