Picture 2 listboxes on a form. The one on the left has the names of all the Sales Reps in the office. The one on the right is empty. In between the two list boxes are 2 buttons. One with an arrow facing left and one with an arrow facing right.

(article continues after sponsor spot)

The purpose of the right facing arrow is to allow the user to pick a subset of the sales reps one by one, where upon their name gets moved to the right hand listbox. A mistake is corrected using the left facing arrow.

Here is one way of doing this.

1. create a table named "tblSalesReps", with 2 fields: SalesRep (text) and flag1 (yes/no)
2. create a form (the remainder of the steps occur on the form)
3. create a list box named "lstAvail" (on left side of form)
4. create a list box named "lstChoosen" (on right side of form)
5. create 2 small buttons between the 2 list boxes; one named "cmdChoose" (right arrow) and the other named "cmdUnChoose" (left arrow)
6. in the row source of lstAvail put this: "SELECT [tblSalesReps].[salesrep] FROM tblSalesReps WHERE ((([tblSalesReps].[flag1])=True)) ORDER BY [tblSalesReps].[salesrep];"
7. in the row source of lstChosen put this: "SELECT [tblSalesReps].[salesrep] FROM tblSalesReps WHERE ((([tblSalesReps].[flag1])=False)) ORDER BY [tblSalesReps].[salesrep];"
8. in the OnClick event of cmdChoose and cmdUnChoose put the code listed below
9. in the OnOpen event of the form put the code listed below

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

Code for cmdChoose

Code for cmdUnChoose

Code for Form Open

How it Works

When the form opens, all the flags are set to true, meaning that all the sales reps are available. When you click on the Choose button (right arrow) the program makes sure that only one sales rep has been selected and then it changes the selected sales rep's flag to False, meaning that they are to be in the chosen group. The requery commands make the list boxes refresh based on the changes to the table data.

This is just a simple example. It can be done by having a temporary table as well, instead of the flag. You can also make it so that a double click in the list moves the names appropriately.

I leave it to the reader to try to make it possible to select more than one name at a time (using the Ctrl + click method) and move multiple names at once. Maybe I'll answer this one later.

The Microsoft® Access sample database (Access 2000 version) that I used to test these techniques is available by request at this page. The download is only supplied in Zip format (20 KB), so you will need WinZip or similar to open it.

This sample database has 1 table and 1 form. Just open the form to test it. Then examine the designs, including the very simple, easy to understand VBA code.

Happy Coding

 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.
 

© 2006, 2007, 2008 Richard W. Killey. All Rights Reserved. - Privacy Policy