Insert Into problem

[Johnny Bright]

Hi there!

I have a list box on my form and when I click it, I would like a record
inserted into a table. I am using the following code.

Private Sub lstSubCat_Click()
Dim db As DAO.Database
Dim strSQL As String
Dim varItem As Variant

Set db = CurrentDb

With Me.lstSubCat
For Each varItem In .ItemsSelected
strSQL = "Insert into tblSuppProdsSub (SuppID, ORCatID, SubCat " & _
"Values (me.SuppID, Me.ORCatID, Me.SubCat);"
db.Execute strSQL, dbFailOnError
Next varItem
End With
end sub

I am getting an syntax error message. Can anyone help? I've likely
misplaced a comma or something.

 

[Steve Schapel]

Johnny,

Yes, there is a problem with the commas. And also a missing )

Try it like this...

strSQL = "INSERT INTO tblSuppProdsSub (SuppID, ORCatID, SubCat)" & _
" VALUES ( " & Me.SuppID & ", " & Me.ORCatID & ", " & Me.SubCat
& " )"

(Assumes all 3 fields are number data type)