VBA to Insert Values from Multi-Value Lisbox into Separate Table (Run-time error 3075)

Question

This is my first post and hoping to get some help. I have done plenty of research but can't determine where I'm going wrong. I simply want to loop through a multi-value listbox and add the values to a separate table.

I have code that I think should work but I keep getting Run-Time error 3075 Syntax error (missing operator) in query expression '1b'.

Here's my situation:

I have a table: qaTbl

I have two fields in this table. interactionID and defect (this is the multi-value listbox). Both fields are shortText.

For each record submitted, I want to place the interactionID and defect (regardless if one defect is selected or many) into a new table called callDefectsTbl.

The two fields in callDefectsTbl is interactionID and defect.

I have a form with a submit button (qaSubmit). The control for the listbox is 'defect'.

Here is the code tied to the Click() Event of the submit button:

Private Sub qaSubmit_Click()

Dim db As DAO.Database
Dim strSQL As String
Dim varItem As Variant

Set db = CurrentDb
' Add each defect selected in the list box to callDefectsTbl
With Me.defect
For Each varItem In .ItemsSelected
    strSQL = _
        "INSERT INTO callDefectsTbl " & _
            "(interactionID, defect) VALUES (" & _
            Me.interactionID & ", " & .ItemData(varItem) & ")"
    db.Execute strSQL
Next varItem
End With


DoCmd.GoToRecord , , acNewRec

End Sub

I get a Run-time error 3075. It seems to be stopping when grabbing the value of defect. I have studied other code that is doing the same thing but I don't understand what I'm doing wrong.

Any help and thorough explanation would be greatly appreciated!!

Thanks!!

Answer 1

If shorttext change sql with single quote

strSQL = _ "INSERT INTO callDefectsTbl " & _ "(interactionID, defect) VALUES ('" & _ Me.interactionID & "', '" & .ItemData(varItem) & "')"