MS Access listbox

This set of VBA functions and MS Access example show how to use code to fill a list box, clear a list box, and retrieve selected items from a list box.

The code is composed of 4 functions:

ClearListBox[] – Clears a list box.  It uses a For Next loop and the list box ListCount property to set the Selected property for each item to False.

SelectAllListBox[] – Selects all the items in a list box.  It uses a For Next loop and the list box ListCount property to set the Selected property for each item to True.

FillList[] – Fills a list box with values parsed from a comma+space delimited string.  It uses Split[] to change the string to an array.  Then it uses a For Next loop to work through the array values and compare them to each item value in the list box.  If a match is found, it sets the Selected value of item to True.

BuildDelimList[] – Passes back a string of comma+space delimited values from selected values in a list box.  It uses the ItemsSelected collection of the list, combined with the ItemData collection, to concatenate the selected item values together.

The Microsoft Access example is called ListBox Example.mdb.  The functions are located in the code module named basListBox.  When the example opens, the Select Patio form appears.  The left side of the form contains the list box, which uses the Patio table for a record source.  The right side of form contains the text box value, from the Data table, created by selecting values from the list.  View the form code module to see the event code that drives the results.

basListBox Code:

' basListBox Version 1.0.0 ' Copyright © 2009 Extra Mile Data, www.extramiledata.com. ' For questions or issues, please contact [email protected] ' Use [at your own risk] and modify freely as long as proper credit is given. Public Sub ClearListBox[ListCtl As ListBox] ' This procedure clears the ListCtl list box. On Error GoTo Err_ClearListBox Dim intCount As Integer ' For each item in the list, deselect it. For intCount = 0 To ListCtl.ListCount ListCtl.Selected[intCount] = False Next intCount     ' Put the cursor back at the top of the list.     ListCtl.Selected[0] = False Exit_ClearListBox: On Error Resume Next Exit Sub Err_ClearListBox: MsgBox Err.Number & " " & Err.Description, vbCritical, "ClearListBox" Resume Exit_ClearListBox End Sub Public Sub SelectAllListBox[ListCtl As ListBox] ' This procedure selects all the items in the ListCtl list box. On Error GoTo Err_SelectAllListBox Dim intCount As Integer ' For each item in the list, select it. For intCount = 0 To ListCtl.ListCount ListCtl.Selected[intCount] = True Next intCount Exit_SelectAllListBox: On Error Resume Next Exit Sub Err_SelectAllListBox: MsgBox Err.Number & " " & Err.Description, vbCritical, "SelectAllListBox" Resume Exit_SelectAllListBox End Sub Public Sub FillList[ListCtl As ListBox, DelimList] ' This procedure fills the ListCtl list box with values parsed from ' a comma+space delimited string of values found in DelimList. On Error GoTo Err_FillList Dim blnFound As Boolean Dim strValue As String Dim intCount As Integer Dim intListItemsCount As Integer Dim avarDelim ' If there is no list to parse, just exit. If IsNull[DelimList] Then GoTo Exit_FillList End If ' Create an array of values from DelimList. avarDelim = Split[DelimList, ", "] ' Initialize some other variables. intCount = 0 ' Clear the list box selections. ClearListBox ListCtl For intCount = 0 To UBound[avarDelim] ' Get the array value. strValue = avarDelim[intCount] ' Initialize the "found" variable and the list item count. blnFound = False intListItemsCount = 0 ' Keep looping through the list items until a match with the array ' value is found or until there are no more items in the list to ' compare to. Do ' Compare the array value to the list value. If StrComp[Trim[ListCtl.ItemData[intListItemsCount]], Trim[strValue]] = 0 Then ' If they are a match, then select the item in the list box ' and flag that one has been found. ListCtl.Selected[intListItemsCount] = True blnFound = True End If ' Increment the list item count. intListItemsCount = intListItemsCount + 1 Loop Until blnFound = True Or intListItemsCount = ListCtl.ListCount Next intCount Exit_FillList: On Error Resume Next Exit Sub Err_FillList: MsgBox Err.Number & " " & Err.Description, vbCritical, "FillList" Resume Exit_FillList Resume End Sub Public Function BuildDelimList[ListCtl As ListBox] As Variant ' This function passes back a string of comma+space delimited values from ' the ListCtl list box. If there are no selected values in ListCtl, ' it passes back a null. On Error GoTo Err_BuildDelimList Dim objItem As Variant Dim strTemp As String Dim intCount As Integer ' Initialize the variable that lets us know whether or not we are ' at the first item in the list box. intCount = 0 ' Initialize the variable that will hold the delimited list. strTemp = "" ' Only proceed if there are items in the list box. If ListCtl.ItemsSelected.Count > 0 Then For Each objItem In ListCtl.ItemsSelected ' For each item that is selected, add it to the delimited list. ' The first one does not need a comma in front of it. If intCount = 0 Then strTemp = strTemp & ListCtl.ItemData[objItem] intCount = intCount + 1 Else strTemp = strTemp & ", " & ListCtl.ItemData[objItem] intCount = intCount + 1 End If Next objItem BuildDelimList = strTemp Else BuildDelimList = Null End If Exit_BuildDelimList: On Error Resume Next Exit Function Err_BuildDelimList: MsgBox Err.Number & " " & Err.Description, vbCritical, "BuildDelimList" BuildDelimList = False Resume Exit_BuildDelimList End Function

Download Code:

ListBox Example.zip

No matter what your MS Access needs are, Carl can provide live help to make sure you get the solution you need.

A Microsoft Access list box is very similar to a combo box, it contains multiple values and allows the user to choose from the list of items. There are obvious differences however, the list box is open at all times and does not allow the user a space to type in values.

So why would you use a Microsoft Access List Box?

There are many other properties available when using the List Box; the user can select multiple items, unlike a combo box, but what can you, as the developer, do with these values? The obvious benefit of a List Box is that it can be configured so that the user can select more than one item at once. The properties of the list box allow the user to pick One choice at a time [if this is the case, it may be as easy to just use a combo box], the user can select multiple items by clicking each one separately [to de-select click the same item twice] or you can allow the user to adjacent or non-adjacent quite easily.

With the help of some VBA programming code, you can add this powerful tool to your Access forms

When Would You Use a List Box?

• A developer may use a list box when they need to offer the user multiple choices when running a query or report; for example allowing the user to choose one county or another to locate customers for a mail shot.
• A developer may use a list box in the same way as you could use an option group, where the list of choices is large and it would look more appropriate listed in one control.

The following example details using a list box within a form to provide the user with multiple choices of criteria for running a query.

The form contains a list box with suitable criteria values. The user can make their choice from the list and click the Run Query button which will pass their choice to a query, and then opens the query. The form below shows the list box in place:

Microsoft Access List Box visible on the form to allow the user to specify multiple criteria for a query

The list box data is created by using Row Source Type of Table/Query and uses the SQL Row source of:

SELECT DISTINCT tblCompanies.strCompanyCounty FROM tblCompanies UNION SELECT "All" FROM tblCompanies;

This returns the Company Counties based upon tblCompanies and will also return ALL to allow the user to choose to return all of the records if no value is picked.

Now the code behind the command button, cmdOpenQuery, which is required to generate the query results needs to do a couple of things:

• It needs to check that the user has made a selection. The selection can either consist of a single selection, multiple selections or using the ALL option return all results. If the user has not selected any item[s] from the list, an error message should be returned.
• Once a selection is made by the user, the selection[s] should be returned to a query to create the results.
• The command should open the query and display the correct result[s]

The query is created by the code behind the command button within the procedure and the criteria is passed at the creation stage. The associated code is shown below, this is attached to the On Click event of the command button - cmdOpenQuery:

'------------------ Code Start ------------------ Private Sub cmdOpenQuery_Click[] On Error GoTo Err_cmdOpenQuery_Click Dim MyDB As DAO.Database Dim qdef As DAO.QueryDef Dim i As Integer Dim strSQL As String Dim strWhere As String Dim strIN As String Dim flgSelectAll As Boolean Dim varItem As Variant Set MyDB = CurrentDb[] strSQL = "SELECT * FROM tblCompanies" 'Build the IN string by looping through the listbox For i = 0 To lstCounties.ListCount - 1 If lstCounties.Selected[i] Then If lstCounties.Column[0, i] = "All" Then flgSelectAll = True End If strIN = strIN & "'" & lstCounties.Column[0, i] & "'," End If Next i 'Create the WHERE string, and strip off the last comma of the IN string strWhere = " WHERE [strCompanyCounty] in " & _ "[" & Left[strIN, Len[strIN] - 1] & "]" 'If "All" was selected in the listbox, don't add the WHERE condition If Not flgSelectAll Then strSQL = strSQL & strWhere End If MyDB.QueryDefs.Delete "qryCompanyCounties" Set qdef = MyDB.CreateQueryDef["qryCompanyCounties", strSQL] 'Open the query, built using the IN clause to set the criteria DoCmd.OpenQuery "qryCompanyCounties", acViewNormal 'Clear listbox selection after running query For Each varItem In Me.lstCounties.ItemsSelected Me.lstCounties.Selected[varItem] = False Next varItem Exit_cmdOpenQuery_Click: Exit Sub Err_cmdOpenQuery_Click: If Err.Number = 5 Then MsgBox "You must make a selection[s] from the list" _ , , "Selection Required !" Resume Exit_cmdOpenQuery_Click Else 'Write out the error and exit the sub MsgBox Err.Description Resume Exit_cmdOpenQuery_Click End If End Sub '------------------ Code End -------------------

The code begins by building the IN expression needed to create the criteria for the query, by looping through the list box. The code adds the ['] single quote marks around the values and also the [,] comma separator to separate the values in the criteria.

If the user selects the ALL option then the criteria is not required and builds the statement using UNION ALL.

If no selection is made a message box is invoked advising the user that they must make a selection:

Message Box returned when no item[s] are selected from the list box.

Once the item[s] are selected from the list box, and the query has ran the list is cleared using:

'Clear listbox selection after running query For Each varItem In Me.lstCounties.ItemsSelected Me.lstCounties.Selected[varItem] = False Next varItem

To see this example in action you can download a copy of the Microsoft Access 2000 database example from the Microsoft Access Queries page or the Microsoft Access Downloads section.