Move all items in listbox to a table RRS feed

  • Question

  • I have two listboxes . Listbox1 is bound to a query whilst the other is not bound to anything.  I can move items from listbox1 to litbox 2. Now what I want to do is to  move the items from listbox 2 into a table, but I want each item to be a separate entry in the table.  Thanks for your help in advance.

    olu Solaru

    Wednesday, July 3, 2019 10:03 PM

All replies

  • Hi

    Maybe  this  function will help.

    but first change the list name to name without gap like listbox_2.

    then use this

     Function insrtLstToTable()
        Dim lngRow As Long
       On Error GoTo insrtLstToTable_Err
      For lngRow = 0 To Me.listbox_2.ListCount - 1
             If Not IsNull(Me.listbox_2.ItemData(lngRow)) Then
         '   use first row if the field is string  ' if numeric use second row
              ' CurrentDb.Execute "INSERT INTO tmpTbl ( someFieldName ) SELECT  '" & Me.listbox_2.ItemData(lngRow) & "'", dbFailOnError
                CurrentDb.Execute "INSERT INTO tmpTbl ( someFieldName ) SELECT  " & Me.listbox_2.ItemData(lngRow), dbFailOnError
            ' if more then one column then use this 
        	 CurrentDb.Execute "INSERT INTO tmpTbl ( someFieldName , secondFieldName  ) SELECT  " & Me.listbox_2.Column(1, lngRow) & "," & Me.listbox_2.Column(2, lngRow)  , dbFailOnError
            End If
    On Error GoTo 0
     Exit Function
    ' do your error trap
    End Function

    Thursday, July 4, 2019 9:44 AM
  • Thanks for replying. Will this be on the click event of the command button?

    olu Solaru

    Monday, July 8, 2019 2:37 PM