locked
Move values from a listbox to a table RRS feed

  • 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

    Wednesday, July 10, 2019 2:26 PM

All replies

  • 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)

    Wednesday, July 10, 2019 3:21 PM
  • 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

    Wednesday, July 10, 2019 4:37 PM
  • 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

    Wednesday, July 10, 2019 4:47 PM
  • 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)

    Wednesday, July 10, 2019 6:09 PM