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.
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.
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...
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
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:
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
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).
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.
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:
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.
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:
And finally set the object variables to Nothing to ensure that these are cleared from the memory:
Set db = Nothing Set qdf = Nothing
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
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().
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...
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 '*'"
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.
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
©2002 Martin Green - www.fontstuff.com - firstname.lastname@example.org - All rights reserved