add listbox items to table

[spinkung]

hi

i am trying to add selected list box items to a temp table (insert into)

so on a button click i have...

Dim tmpTbl As String
Dim lbx_Sel As Long

tmpTbl = Me.txt_tmpTbl.Value

     'loops through ListBox to test if it is selected
    For lbx_Sel = 0 To Me.lst_select.ListCount - 1
        If lst_select.Selected(lbx_Sel) = True Then
            SQL = "insert into " & tmpTbl & " (sql, sql_desc, aggregate) values (" & lst_select.Selected(lbx_Sel) & ")"
            Debug.Print SQL
            CurrentDb.Execute SQL
        End If
    Next

..but the result is...

insert into tbl_proteus_fields_280312102957 (sql, sql_desc, aggregate) values (-1)

..i need to insert column 0, 1 and 2 into my temp table. can anyone point me in the right direction.

thanks

 

[Beetle]

Here's an example (air code). You'll have to add appropriate delimiters, if necessary, depending on the data types of the table fields.

Dim tmpTbl As String
Dim SQL As String
Dim varItem As Variant

tmpTbl = Me.txt_tmpTbl.Value

With Me.lst_Select
    For Each varItem In .ItemsSelected
        SQL = "Insert Into " & tmpTbl & " (sql, sql_desc, aggregate)" _
            & " Values (" & .Column(0, varItem) & ", " & .Column(1, varItem) & ", " & .Column(2, varItem) & ");"
            Debug.Print SQL
            CurrentDb.Execute SQL, dbFailOnError
    Next
End With