copy from one listbox to another

[spacerocket]

Ok, I have read a lot of post and tried a lot of different things, but can not get it to work the way I want. Here is a description of what I have and what I need. First of all, I have a listbox that show 5 columns of data updated from a query with cascading combo boxes. (note the combo box only show 5 rows of data, but each row has 15 columns of data).

I have another listbox that I want to essentially create a list in. So I want to be able to use my cascading combo boxes to filter the data in my firstlist1 box, then be able to double click on a line in listbox and have it copy it to listbox2, although I want it to show different data in listbox2 then the original listbox1.

So for example if I click on a row in listbox1 that show data from columns 1,3,5,7,8 in my query, Then I want it to show up in listbox2 showing columns 1,2,6,8,10. (note: I do not want the row to be removed from listbox1). Also, I am not sure if I have the properties set correctly in my listboxes. Listbox1 is unbound, row source is data/query, and mutli select is simple. Listboxs is unbound, row source set to value list and mutli select is simple.

I would also like to be able to double click on a line in listbox2 and it gets deleted and have a button that deletes all data in listbox2 (essential clearing it to create a new list).

Thank you in advance

 

[Mr. B]

You could make your senerio work if you add a field to your table that is providing the data to your listboxes. Make the field an boolean type field (Yes/No) and perhaps name the field "Selected". Then in listbox1 show all records regardless of the value of the "Selected" field. Have your listbox2 show only records where the "Selected" field is equal to "Yes". When you double click in listbox1 you can use VBA to read the Id of the record that was double clicked and then use DAO to update the "Selected" field in that record to "Yes". Requery listbox2 and you should see the new value in listbox2.

You could then add code to the Double click event of listbox2 that would again read the ID of the selected record and update the selected record to "No" and requery listbox2 and the record would no longer be in the list.

Just my thoughts on a rather simple way to do what you want.

 

[GaryPanic]

an alternative would be to have the list 2 on a temp table

double click list1 to append to the temp table -requery - and have a double click on the list2 to delete (requery)
list2 is based on a simple qry based on the temp table so you can view whatever you want
you will probably need a delete table when you close the form (or close d/b)

i have this set up and it looks ok - works pretty well (I also update on my option - with a date stamp on the push of a button

mine is invoice - then cash in and then banked
so the cash gets entered in I use the append qry to put into my temp table what i am banking - give it a date stamp and a banking refernce number
run qry to update the main table and when i close the d/b it deletes the data in the temp table

quite easy to do

 

[DCrake]

Take a look at this Link

 

[spacerocket]

Mr. B,
Thank you that worked perfectly. Way easier then what I was trying to do.

 

[spacerocket]

Spoke to soon, had everything working perfect on a small testing database, but when I uppdated listbox1 from a query and not a table it did not work. For some reason when I call my sub that changes the boolean expression it erros if reading from the query. I get a type mismatch run time error 13. any ideas? below is the call function that errors.

Private Sub list_box1_DblClick(Cancel As Integer)

Call ChangeStatus(CLng(Me.list_box1), 1)

End Sub

 

[spacerocket]

ah stupid me I did not add my ID Column to my query

​