Making Sense of List Boxes

When I first started building Access forms I couldn't understand what was the point of a list box. To me it was just like a combo box, but it stayed open all the time and there was no place for the user to type. Why not just use a combo box? Then I discovered that by altering its properties I could allow the user to select more than one item at a time - you can't do that with a combo box! But I couldn't figure out what to do next... how do I get Access to understand what the user chose, and what would I want to use it for anyway? Well, I eventually found out! In this tutorial you will discover what a list box can do and how, with the help of some VBA programming, you can add this powerful tool to your Access forms.

What is a List Box?

A List Box is a control that provides a means of displaying a list of items (text, numbers, dates or whatever) on an Access form. Unlike the Combo Box it lacks a text box at the top in which the user can type. The list remains on view all the time, giving the List Box the appearance of a scrolling text box.

	If the list is longer than the size of the box, a scrollbar is shown (as in the picture on the left). So why not use a combo box? Because the list box's list is always open, the user can see the range of choices without having to do anything, but that is not much of a benefit. If you want the user to be able to pick a single item from a list you might as well use a combo box. The main benefit of a List Box is that it can be configured so that the user can select more than one item.
	With the Multi Select property set to None the user can not make multiple selections. They can choose only one item at a time. Selecting a second item de-selects the first item.
	When the Multi Select property is set to Simple the user is allowed to make multiple selections. They have to click on each item they want to chose (alternatively they can use the Spacebar). Clicking on an already selected item de-selects it.
	If the Multi Select property is set to Extended the user has the most control. They can select a block of items by clicking on the first one and shift+clicking on the last one. Alternatively they can drag the mouse down the list to select a block. Separate non-adjacent items can be selected by clicking on the first one then control+clicking on the others. Already selected items are de-selected with control+click.

When a list box has its Multi Select property set to "None" the list box has a value equal to the selected item, just like a combo box does. When used like this it can feed that value directly into a table field or query. However, when the Multi Select property is set to "Simple" or "Extended" the list box has a value of Null regardless of how many or which items are selected. In order to make use of the user's selection of single or multiple items it is necessary to use a VBA procedure to examine the list, determine what has been selected, and act on it.

When Would You Use a List Box?

I would probably not use a list box for data entry, to put a single value into a table field. If I was going to offer the user a reference list of items to choose from, I would normally use a combo box.
I sometimes use a list box in the same way as I use an option group (a collection of radio buttons of which you can only select one) where my list of choices is large and I don't have room for (or don't like the look of) a large collection of option buttons.
I might use a list box in multi select mode to allow a user to make more than one choice for multiple data entry, but I can't think of an example that wouldn't represent poor database design (would the choices all go in the same field... or into different fields... how would it decide?).
I would often use a list box when I wanted to ask the user to choose a number of items for which separate actions would be performed (e.g. "Which reports shall I print?", "Which contacts shall I e-mail?").
I would definitely use a list box to offer the user multiple choices when running a query (e.g. choose one or more cities... London OR Paris OR New York).

In the example that follows I am going to use a list box on a form to provide the user with multiple choices of criteria when running a query. The form contains a list box with suitable criteria values. The user will make their choice from the list and click a button which passes their choice to a query, and then opens the query. The form looks like this...

A list box on a dialog style form

If you want to learn how to make an Access form into a dialog box, like this one, take a look at the step-by-step tutorial on Custom Parameter Queries to see how it's done

Setting the List Box Properties

I'll assume that you know how to put a List Box control on to a form. If you are new to form design, take a look at the step-by-step tutorial on Custom Parameter Queries to see how to put a combo box on a form. Do the same, but choose the List Box tool instead.

To set the properties of the List Box right-click on it (in form design view) and choose Properties. You need to define what will make up the list, and how the user is permitted to make their choice:

1. Fill The List

On the Data tab you can set the Row Source property to determine what goes on the list.

If you already have a table or query that provides a list of items, set the Row Source Type to Table/Query and either:
1. Click in the Row Source text box, click the down-arrow and pick your table or query name from the list, or...
2. Click in the Row Source text box, click the build button ( ) and choose Query Builder to open a query design window. Here you can build and test a query to create a list. When you close the query builder window an SQL statement is returned to your Row Source property. It will run a query to create the list whenever the form opens.
If you prefer to enter your list directly, set the Row Source Type to Value List and type the list items directly into the Row Source text box, separating the items with semicolons e.g.:
Belgium;Denmark;France;Germany;Italy etc.

2. Determine How the User Will Select Items

On the Other tab set the Multi Select property to either Simple or Extended. This will allow the user to make multiple selections from the list (as described above).

Getting the Data from the List Box

Unless it is used in MultiSelect "None" mode the list box always returns a value of "Null". This means that you can't feed the user's multiple selection straight into a query or table. You must use a code procedure to extract the information from the list box and put it into a suitable state for whatever is needed.

Like most other form controls, a list box has its own set of Event Procedures and you might be tempted to attach your code to the AfterUpdate event of the list box. Unfortunately, this would probably not be suitable. The AfterUpdate event fires each time the list box selection changes. So, if the user selects just a single item, or selects multiple items by dragging down the list, then it would be fine. The selection would be made in one step and the AfterUpdate event would fire when the user finished. But, if they selected items using the Click/Click, Click/Shift+Click or Click/Control+Click methods the AfterUpdate event would fire several times. This is not suitable for our purposes.

In my example, I use the OnClick event of a separate command button to run the code.

What Does the Code Have to Do?

I'm going to use the items that the user selects in the list box to build the criteria of a query. In this example, there is data from five different regions: North, South, East, West and Central. If the user wants to see records from just one of those regions the criteria for the Region field would simply be "East" (for example). But supposing they want to see records for the South, West and Central regions? There are several ways to specify this when constructing a query in the Access query design window, but the most convenient way is either:
"South" OR "West" OR "Central" or IN("South","West","Central")

Here is a list of tasks for that I want the code to do:

Check that the user has selected something. If they have selected nothing, maybe I could cancel the procedure - or show them everything.
Assuming they have chosen one or more items, the code must extract the information and put it into a format (a text string) that a query will understand.
The text string should be incorporated into an SQL string and passed to a query. To make things easier I'll use a stored (ready-made) query.
Finally the code should open the query to display the results to the user.

Prepare the Query

Create and save a query. It can be anything you like, we just need a stored query with a name. The code will determine the result that the query returns by re-writing its SQL each time it is run, so it doesn't matter what it is now. I have called my query qryMultiSelect.

Writing the Code

In form design view open the properties window for the Command Button and find On Click on the Events tab. Click in the On Click text box then click the build button ( ) and choose Code Builder. The code window will open with the cursor between the start and end lines of the command button's Click procedure:

Private Sub cmdOK_Click()

End Sub

I'm going to be using DAO code (Data Access Objects) because I prefer it. It was the default for Access 97 but not for Access 2000 and 2002, which default to ADO (ActiveX Data Objects). If you are using Access 2000 or 2002 you need to add a code reference to DAO. Open the Tools menu and choose References. In the References dialog find the entry for Microsoft DAO 3.6 Object Library and put a tick in the box then click OK (NOTE: you don't have to do this if you are using Access 97). This makes sure that Access understands the DAO code vocabulary.

Now to write the code. In the code shown below you will see the names of objects in my sample database. You should replace these with the names of the corresponding objects in your own database. My list box is called lstRegions. My stored query is qryMultiSelect. The query will refer to a field called Region in a table called tblData.

First of all, enter the variable declarations:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

The next two lines assign values to the DAO variables. The first line tells Access that we are referring the current database; the second line identifies our stored query:

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelect")

Now for the code that reads the user's selection from the listbox:

For Each varItem In Me!lstRegions.ItemsSelected
   strCriteria = strCriteria & ",'" & Me!lstRegions.ItemData(varItem) & "'"
Next varItem

This For ... Next loop looks at each selected item in the list box, adding each in turn to the string variable strCriteria. As it does so it adds a comma and quote mark in front of the item, and a quote mark after it. This is because I'm building an SQL statement containing an IN(...) expression in which the criteria have to be in quotes and separated by commas. (I will use the OR operator in the next example, below.)

NOTE: I have used single quote marks (') to enclose the criteria. SQL is happy with that. I could not use the more usual double quotes (") because these are used by VBA as a text qualifier (to define a text string). As an alternative I could have used the ASCII character code Chr(34) which represents the double quote mark without actually typing it. The code line would look like this:

=============Alternative code line=============
strCriteria = strCriteria & "," & Chr(34) & 
Me!lstRegions.ItemData(varItem) & Chr(34)
================================================

At this point it's a good idea to check that that the user actually selected something. In this example I will assume that if the user didn't select anything they must have forgotten, or accidentally clicked the OK button. I will show them a message box reminding them to select something before they click the button, then cancel the procedure:

If Len(strCriteria) = 0 Then
   MsgBox "You did not select anything from the list" _
          , vbExclamation, "Nothing to find!"
   Exit Sub
End If

Alternatively, I could interpret the user's failure to select anything as their wish to see all the data (see the code for this in the next example).

There are different ways to check for no selection. The method I used here looks at the length of the criteria string that was made by looping through the list. If the string has a length of zero, the loop didn't find any selections. (I'll use a different method in the next example.)

Now I have to edit the string. To simplify its construction, each component of the string was added in the same way i.e.: comma + quote + item + quote. The result, regardless of the number of items, is that it starts with a comma e.g.: ,'South','West','Central'.

The following line removes the first character (the leading comma) from the string:

strCriteria = Right(strCriteria, Len(strCriteria) - 1)

The criteria string is now ready to be incorporated into an SQL statement. For this example I have kept the SQL statement simple. You can see how the criteria string variable is added between the brackets of the IN( ---string goes here--- ) expression:

strSQL = "SELECT * FROM tblData " & _
         "WHERE tblData.Region IN(" & strCriteria & ");"

Earlier, I set a reference to my stored query in the variable qdf. The next line applies the completed SQL statement to the stored query. This replaces the query's current SQL (which is why I said earlier that it didn't matter what the original query did).

qdf.SQL = strSQL

Having created the desired query all that remains is to open it to display the result to the user:

DoCmd.OpenQuery "qryMultiSelect"

And finally set the object variables to Nothing to ensure that these are cleared from the memory:

Set db = Nothing
Set qdf = Nothing

Complete Procedure: Example 1

Private Sub cmdOK_Click()
   Dim db As DAO.Database
   Dim qdf As DAO.QueryDef
   Dim varItem As Variant
   Dim strCriteria As String
   Dim strSQL As String
   Set db = CurrentDb()
   Set qdf = db.QueryDefs("qryMultiSelect")
   For Each varItem In Me!lstRegions.ItemsSelected
      strCriteria = strCriteria & ",'" & Me!lstRegions.ItemData(varItem) & "'"
   Next varItem
   If Len(strCriteria) = 0 Then
      MsgBox "You did not select anything from the list" _
             , vbExclamation, "Nothing to find!"
      Exit Sub
   End If
   strCriteria = Right(strCriteria, Len(strCriteria) - 1)
   strSQL = "SELECT * FROM tblData " & _
            "WHERE tblData.Region IN(" & strCriteria & ");"
   qdf.SQL = strSQL
   DoCmd.OpenQuery "qryMultiSelect"
   Set db = Nothing
   Set qdf = Nothing
End Sub

Alternative Methods

In the following example, I will use a different method to check whether the user has selected anything and if not it will be assumed that they want to see all the data (i.e. as if they had selected all the items in the list - which, of course, they could have done). I will also construct an SQL statement using the OR operator instead of IN().

Checking for a Null Selection

An alternative method for checking whether the user has selected anything is to count how many items have been selected. If the answer is zero then the user has not made a selection. the code is very simple, and would probably be included in an If Statement:

If Me!lstRegions.ItemsSelected.Count > 0 Then
   === code to handle selected items goes here ===
Else
   === code if user selected nothing goes here===
End If

Suitable code to go inside the If Statement is shown below...

Letting the User See All the Data

In the previous example, if the user failed to make a selection from the list box, I assumed they had made a mistake and cancelled the procedure. But what if the user wants to see all the data? If I were writing a simple SQL statement, I could simply omit the WHERE clause, and all the data would be returned by the query. However, because my basic SQL statement already contains a WHERE clause (and in many cases the query will be more complex than this example) I can ask it to return all the records for the Region field by using the expression: Like "*". My line of code would look like this:

 strCriteria = "tblData.Region Like '*'"

Using the "OR" Operator

Whilst Access allows you to simply type for example: "South" OR "West" OR "Central" into the Query design grid, this is not acceptable SQL. Access understands what you have typed and converts it into an SQL string. Here's what the SQL string should look like when using the OR operator:
tblData.Region = "South" OR tblData.Region = "West" OR tblData.Region ="Central"

Although it looks a bit more complicated than using the IN() operator, it can be done in the same way. I have also used the ASCII code Chr(34) to represent the double-quote (") character, as I described earlier, to avoid any conflicts with quote marks in the VBA.

For Each varItem In Me!lstRegions.ItemsSelected
   strCriteria = strCriteria & "tblData.Region = " & Chr(34) & 
   Me!lstRegions.ItemData(varItem) & Chr(34) & "OR "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)

Note that this time the resulting string is tidied up by removing the last three characters.

Because I have changed the way the criteria string is written, I now need to change the basic SQL statement as follows:

strSQL = "SELECT * FROM tblData " & _
         "WHERE " & strCriteria & ";"

Apart from these changes, the remainder of the code is as in the previous example.

Complete Procedure: Example 2

Private Sub cmdOK_Click()
   Dim db As DAO.Database
   Dim qdf As DAO.QueryDef
   Dim varItem As Variant
   Dim strCriteria As String
   Dim strSQL As String
   Set db = CurrentDb()
   Set qdf = db.QueryDefs("qryMultiSelect")
   If Me!lstRegions.ItemsSelected.Count > 0 Then
      For Each varItem In Me!lstRegions.ItemsSelected
         strCriteria = strCriteria & "tblData.Region = " & Chr(34) _
                       & Me!lstRegions.ItemData(varItem) & Chr(34) & "OR "
      Next varItem
      strCriteria = Left(strCriteria, Len(strCriteria) - 3)
   Else
      strCriteria = "tblData.Region Like '*'"
   End If
   strSQL = "SELECT * FROM tblData " & _
            "WHERE " & strCriteria & ";"
   qdf.SQL = strSQL
   DoCmd.OpenQuery "qryMultiSelect"
   Set db = Nothing
   Set qdf = Nothing
End Sub