Transfering Data from a listbox to a table

[slushpuppie]

Hello, i need help. What im trying to do is select items from a master list and place the item number into a different table with the use of a listbox. Then the user can select a button to view the table of selected items or return to the form with the lisbox if they need to select other choices. Simple or extended on the listbox would be nice as well as double clicking on the item to add or remove it. Sorry if this isnt the best explanation, ill be happy to make it clearer. I have been working on this for days and cant figure it out....PLEASE HELP!

 

[jebry]

Hi!

Here is the general set up for what you want to do:

Dim varRow As Variant
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("yourtable", dnOpenDynaset)

For Each varRow In Me!YourListBox.ItemsSelected
With rst
.AddNew
!YourField = Me!YourListBox.Column(0, varRow)
add other fields as necessary
.Update
End With
Next varRow

Set rst = Nothing

That was for a multiselect listbox. If you go with the doubleclick notion then the listbox should not be multiselect and you can use the following code:

Dim intRow As Integer
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("yourtable", dbOpenDynaset)

intRow = Me!YourListBox.ListIndex
With rst
.AddNew
!YourField = Me!YourListBox(0, intRow)
add other fields as needed
.Update
End With

You may want to remove the items selected or doubleclicked from the list box at this point. How you do that will depend on the row source type of the list box.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[slushpuppie]

Thank you, But where would i place this code.....?
thank you...

 

[jebry]

Hi again!

For the multiselect procedure you would put in the click event of a Save button. For the doubleclick procedure you would put it in the doubleclick event of the list box.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[slushpuppie]

I get a "Compile Error: User-Defined Type Not Defined"

Does that mean i dont have a certain library referenced?