the Manx Man's MS Access Pages
to subscribe - click here

Using List Boxes to Show Choices (029)

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. 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

All the code segments assume that you have DAO references active.  If you are not sure what this means, and you are using 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.