VBA Access. Insert Into Error

Question

I have a listbox named listComponents1 and I want the items selected in this by the user to be populated in field Components ID of the table tblMasterData. In my table the field Components ID is of type number. I have written the code but I get an error saying syntax error in Insert Into statement. Can someone help with this issue?

For Each varItem In Me.listComponents1.ItemsSelected
    strSQL = "INSERT INTO tblMasterData" & _
                "(Components ID)" & _
                 "Values(" & Me.listComponents1.Column(0, varItem) & ")"
    CurrentDb.Execute strSQL, dbFailOnError
Next

June7 · Accepted Answer · 2018-03-28 08:40:21Z

1

Field Components ID has a space so enclose in [ ]. Use ItemData to read value from list item.

With Me.listComponents1
    For Each varItem In .ItemsSelected
        strSQL = "INSERT INTO tblMasterData" & _
                    "([Components ID])" & _
                     "Values(" & .ItemData(varItem) & ")"
        CurrentDb.Execute strSQL, dbFailOnError
    Next
End With

Advise not to uses spaces nor punctuation/special characters (underscore only exception) in naming convention.

edited Mar 28, 2018 at 8:40

answered Mar 28, 2018 at 8:34

June7

21.7k8 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Access. Insert Into Error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related