Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011
    Location
    Chicago
    Posts
    37

    Question Unanswered: List Box to List Box Help

    I'm trying to set up two list boxes in Access 2007 where one LB will display items to select in order to be moved to the other LB. I'm unfamiliar with VBA and Macros, so I would appreciate any help regarding this. I've posted a couple of threads previously regarding this but could never get it to work with the examples that were given. If someone could take me step by step that would be a huge help. I've included a screenshot of the form I have set up thus far. The buttons on the form have no functionality currently.
    Attached Thumbnails Attached Thumbnails skills form.bmp  
    Last edited by ctown; 08-02-11 at 12:07. Reason: Need to add a screenshot

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The quickest and easiest way to do this consists in using two tables that are used as the RowSource of the combos.

    When you need to transfer data from combo1 to combo2, it then consists in inserting one row into table2 with data selected from table1, then delete the same row from table1, then requery both combos. If Column(0) of both combos holds a unique identifier for both tables (Id), then you can use:
    Code:
    '
    ' Insert selected row into Table2.
    '
    strSQL = "INSERT INTO Table2 ( Id, <Column1>, <Column2>, ... ) SELECT Id, <Column1>, <Column2>, ... FROM Table1 WHERE Table1.Id = " & Me.Combo1.Column(0)
    CurrentDb.Execute strSQL, dbFailOnError
    '
    ' Delete the inserted line from Table1.
    '
    strSQL = "DELETE FROM Table1 WHERE Table1.Id = " & Me.Combo1.Column(0)
    CurrentDb.Execute strSQL, dbFailOnError
    '
    ' Refresh the data in both combos.
    '
    Me.Combo1.Requery
    Me.Combo2.Requery
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •