locked
limiting listbox to listbox RRS feed

  • 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
    Wednesday, December 7, 2011 3:59 AM

Answers

  • 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
    • Marked as answer by johnboy0276 Wednesday, December 7, 2011 8:14 PM
    Wednesday, December 7, 2011 6:18 AM
  • 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

    • Marked as answer by johnboy0276 Wednesday, December 7, 2011 8:14 PM
    Wednesday, December 7, 2011 7:02 AM

All replies

  • 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
    • Marked as answer by johnboy0276 Wednesday, December 7, 2011 8:14 PM
    Wednesday, December 7, 2011 6:18 AM
  • 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

    • Marked as answer by johnboy0276 Wednesday, December 7, 2011 8:14 PM
    Wednesday, December 7, 2011 7:02 AM
  • 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. 

    Wednesday, December 7, 2011 8:14 PM