Move values from a listbox to a table

Question

I am trying to move items from a listbox into a table.  So if I have 5 items in the listbox, those five items will  make 5 entries in a particular table. I have a function that I call on the On click event of a button, but it doesn't seem to work.

Public Function InsrtLstTOTable()
    Dim lngRow As Long
    On Error GoTo insrtLstToTable_Err
For lngRow = 0 To Me.lstSavedIndTasks.ListCount - 1
 If Not IsNull(Me.lstSavedIndTasks.ItemData(lngRow)) Then
 CurrentDb.Execute "INSERT INTO Related_Tasks(Task_Name, Task_Type) SELECT " & Me.lstSavedIndTasks.Column(1, lngRow) & Me.lstSavedIndTasks.Column(2, lngRow), dbFailOnError
 
 End If
Next

insrtLstToTable_Exit:
On Error GoTo 0
    Exit Function
insrtLstToTable_Err:

End Function

---

olu Solaru

Answer 1

Assuming that Task_Name and Task_Type are text fields:

CurrentDb.Execute "INSERT INTO Related_Tasks (Task_Name, Task_Type) VALUES ('" & Me.lstSavedIndTasks.Column(1, lngRow) & "', '" & Me.lstSavedIndTasks.Column(2, lngRow) & "')", dbFailOnError

Keep in mind that the columns of a list box start counting at 0, so Column(1, lngRow) is the 2nd column.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

Thanks. Can I add this to the On click event of the Command button or should I call the Function

CAll - <Function Name>?

---

olu Solaru

Answer 3

SO this is what I modified my code to , and it still does not populate the table.  

Public Function InsrtLstTOTable()
    Dim lngRow As Long
    On Error GoTo insrtLstToTable_Err
For lngRow = 0 To Me.lstSavedIndTasks.ListCount - 1
 If Not IsNull(Me.lstSavedIndTasks.ItemData(lngRow)) Then
 CurrentDb.Execute "INSERT INTO Related_Tasks(Task_Name, Task_Type) VALUES('" & Me.lstSavedIndTasks.Column(0, lngRow) & "', '" & Me.lstSavedIndTasks.Column(1, lngRow) & "')", dbFailOnError
 End If
Next

insrtLstToTable_Exit:
On Error GoTo 0
    Exit Function
insrtLstToTable_Err:

End Function

---

olu Solaru

Answer 4

You can call the function from the On Click event procedure of the command button, or copy the code into the On Click event procedure.

The way you have written the code, you won't know whether an error occurred, and if so, what it was. Try this version. You should see the error message if something goes wrong:

Public Function InsrtLstTOTable()
    Dim lngRow As Long
    On Error GoTo insrtLstToTable_Err
    For lngRow = 0 To Me.lstSavedIndTasks.ListCount - 1
        If Not IsNull(Me.lstSavedIndTasks.ItemData(lngRow)) Then
            CurrentDb.Execute "INSERT INTO Related_Tasks(Task_Name, Task_Type) VALUES('" & _
                Me.lstSavedIndTasks.Column(0, lngRow) & "', '" & _
                Me.lstSavedIndTasks.Column(1, lngRow) & "')", dbFailOnError
         End If
    Next lngRow
    Exit Function

insrtLstToTable_Err:
    MsgBox Err.Description, vbExclamation
End Function

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)