Access VBA Insert INTo Error

Question

I have a listbox named lstAvailable and i am trying to send the values selected from it into a table. But i get an error saying syntax error in Insert Into statement. Machine Subsystem ID,NewComponents are of type integer and MO_TAG and Components_Detail is of text type

      Dim varItem As Variant
      Dim sSQL As String
      Dim nRid As Long 
     Dim nEid As Long
     Dim nEid1 As String
     Dim nEid2 As String 
   Dim varRow As Variant
     nRid = listMachineSubSystem.Column(1)
  For Each varRow In lstAvailable.ItemsSelected
    nEid = lstAvailable.Column(0, varRow)
    nEid1 = lstAvailable.Column(2, varRow)
    nEid2 = lstAvailable.Column(3, varRow)
    sSQL = "INSERT INTO tblComponents ([Machine Subsystem ID], [NewComponents], [MO_TAG], [Components_Detail])" _
    & " VALUES (" & nRid & ", " & nEid & ", " & nEid1 & ", " & nEid2 & ");"
    'Debug.Print "Insert " & sSQL
    CurrentDb.Execute sSQL, dbFailOnError

Answer 1

Maybe this will help?:

sSQL = "INSERT INTO tblComponents ([Machine Subsystem ID], [NewComponents], [MO_TAG], [Components_Detail])" _
& " VALUES (" & nRid & ", " & nEid & ", '" & nEid1 & "', '" & nEid2 & "');"

I added some ' ' where the Strings are in the value brackets.

And:

[Machine Subsystem ID]

Shouldn't this be only one word like Machine_Subsystem_ID?