limiting listbox to listbox

Question

Hello. I have a form with 2 listboxes. The user selects rows from listbox A and transfer them to listbox B. Listbox A is populated by a query. When the items in A are transferred, they are actually being inserted into a temp table. This temp table then is what populates listbox B. I have all of this working but what I do not have working is allowing the user to move a list item from A only once. They can select all the items in listbox A they want and transfer them to listbox B but only once. While the item(s) are in listbox B the user can not move any of those same items again from A to B until those in B are gone. How would I code something like that? Thanks....John

Answer 1

Hi,

when you say "transfer", do you really want to leave original values in the listbox A? What I mean is when we use transferring between listboxes (in Access Wizards, for example), items are often being moved (read "cut and pasted") from list A to list B. If it is applicable for your case, you can bind both listboxes to temp tables and just move records between them.

Otherwise, I think you should add an additional check for existing record before inserting.

If you're interested in w/o temp tables solution, you can check it here http://enblog.biztoolbox.ru/post/8882528924/htl

---

Andrey V Artemyev | Saint-Petersburg, Russia
Russian blog artemyev.biztoolbox.ru
English blog enblog.biztoolbox.ru

Answer 2

Hi Johnboy0276
You could create a unique index on the temp table that doesn't allow to insert a record twice. Alternatively you can insert only the records that are not in the temp table yet. If you insert these records by VBA then you can use

If DCount("*"; "tempTable"; "Key=" & KeyOfListBox) > 0 Then
 MsgBox "already in Listbox B"
End If

HTH
Henry
johnboy0276 wrote:

Hello. I have a form with 2 listboxes. The user selects rows from listbox
A and transfer them to listbox B. Listbox A is populated by a query. When
the items in A are transferred, they are actually being inserted into a
temp table. This temp table then is what populates listbox B. I have all
of this working but what I do not have working is allowing the user to
move a list item from A only once. They can select all the items in
listbox A they want and transfer them to listbox B but only once. While
the item(s) are in listbox B the user can not move any of those same
items again from A to B until those in B are gone. How would I code
something like that? Thanks....John

Answer 3

Gentlemen, thank you for your suggestions.  My immediate need I will be using the 2 temp table solution from Andrey.  But there is another project in which Henry's solution will be applicable.  Makes one less posting needed for the other project.