Move all items in listbox to a table

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

Answer 1

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
   Next

insrtLstToTable_Exit:
On Error GoTo 0
 Exit Function
insrtLstToTable_Err:
' do your error trap
End Function

Asaf

Answer 2

Thanks for replying. Will this be on the click event of the command button?

---

olu Solaru