Transfer records from listbox 1 to listbox 2 gone wrong

[Pommetje77]

Good day everyone, hope you could help me with the following:

Attached you can find a small test database file with a form, with 2 listboxes.
listbox 1 can be filtered by a combobox and is multi-selectable.

Attachment: ListboxTransfer.zip

The purpose is;
You can select multiple items in listbox 1 and transfer them to listbox 2.

Problem:
When scrolling down to the end of listbox 1 and select multiple items here (for example 6 items) and transfer them to listbox 2,
the listbox 2 is not showing the correct transferred items from listbox 1.
They are totally different.


This only happens with the very first selection of items in the listbox.

I break my head about this, I don't know what is going wrong.

Does anybody have experienced the same?

Hope someone could help me with this.
Thanks in advance

[Missinglinq]

Listbox2 will have to be Requeried, after the 'transfer,' for the new data/records to appear in it:

Me.Listbox2.Requery

Linq ;0)>

[Pommetje77]

Hello Missinglinq,
Thanks for your reply!
The listbox2 is requeried after the ' transfer'.
The point is, it is showing data, however not the data I selected in listbox 1 to be transferred to listbox2.
for example if I select X00080, X00081 and X00082, then click on the arrow button to transfer it to listbox 2, listbox 2 will show X000001, X00002 etc.
If I remove them from listbox2 and repeat my previous command, it works correct!
Please help!

[Micron]

I think your problem is that you have bound the left box to a sql statement that excludes anything in the right box. When you think of it, after the first loop, you have removed that item from the left box. Now the row count and index (ItemData.varItem) has changed so the code selects from the start of the list. You cannot expect to maintain the list of selected items if you're going to change the index.

You'll have to design some other way. Easiest is probably to copy the items to the right box, but to do that, it doesn't have to be bound. Just use the .AddItem (varItem) approach. If you have to move them, maybe loop the items then build sql statements that add or delete as needed. You probably could also use an array, but why complicate things?

[alansidman]

Maybe something like this:

http://www.datapigtechnologies.com/f...tboxtrick.html

[Micron]

Nice solution, as long as OP doesn't need multi select feature.

[Pommetje77]

All,
Thanks for your posts!
I changed the table reference of listbox 2 into unbound, removed the table Selected with all the references to this table and changed the rowsource type to Value List and used the following code to copy the items from listbox1 to listbox2

Dim varItem As Variant
Dim varData As Variant
For Each varItem In Me.lstOne.ItemsSelected
varData = Me.lstOne.ItemData(varItem)
lstTwo.AddItem (varData)
Next varItem
me.listTwo.Requery

Everything is working like a charm, thank you very much for your help!
Ruud