List Box append to a table 1

[postmanplod]

Hello,

I have a form with a list box (lstSerial) and a button on it. I have set the list box to extended multi select. The list box brings in one column of data from a table (tblSerial.SerialNo).

When the user clicks on the button I would like it to append the selected data from the list box to another table (tblDisc.Serial)

The below is from the click event of the command button:

Private Sub Command13_Click()
Dim lst As Access.ListBox
Dim itm As Variant
Dim strInsert As String
Dim strValues As String
Dim strSql As String
Dim colOne As Long

Set lst = Me![lstSerial]
strInsert = "Insert into [tblDisc]![Serial] values "
For Each itm In lst.ItemsSelected
colOne = lst.Column(0, itm)

strValues = "(" & colOne & "')"
strSql = strInsert & strValues
CurrentDb.Execute strSql
Next itm
End Sub

However, when I click the button I receive the following:

Run time error 3134: Syntax error in INSERT INTO statement

It then highlights the following line of code:

CurrentDb.Execute strSql


Where am I going wrong?

Thanks,

Michael

 

[jrbarnett]

Change

strInsert = "Insert into [tblDisc]![Serial] values "

To:

strInsert = "Insert into [tblDisc] ([Serial]) values "

Note however you will need to run one insert statement for each selected value, you cannot string values clauses after each other.

John

 

[MajP]

Please do not start a new post for the same issue. I already told you in the original post that line was not correct.

 

[postmanplod]

jbbarnett - thank you this has worked!!