Using Multiple Selections In A List Box

by Patrick L. Steiner

Sometimes, the ability to select multiple items from a list would come in very handy. Instead of selecting single items several times, consider placing a List Box control on a form and using its Multi Select property. In this article, we'll show you a technique for adding this functionality to your forms.

The multi select property

By default, the Multi Select property for a list box is set to None. However, you can set this property to Simple or Extended to instantly allow multiple selections to be chosen from within the list box. The Simple setting allows you to point and click at multiple items in the list to select or de-select them. The Extended setting allows you to use the [Shift] and [Ctrl] keys in combination with mouse clicks or arrow keys to select or deselect items in the list.

If you want to bind the List Box control to a field (Control Source), keep the Multi Select property set to None. You do this because if you select more than one item, Access will set the List Box control's value to Null. As you'll see later, you must use the ItemsSelected collection to determine the values or items selected.

A form to update multiple items

Let's work through an example of selecting multiple list-box items. To begin, open a new database and create a table called tblSalesItems with the structure shown in Table A. Save the table and populate it with the seven records shown in the list box in Figure A of "Some List-Box Basics".

Table A: tblSalesItems data structure

Field Name Data Type
Description Text 20 (Primary Key)

SalePrice Currency

Field Name	Data Type
Description	Text 20 (Primary Key)
SalePrice	Currency

To demonstrate the list box's Multi Select property, refer to the form shown in Figure A. It uses a list box to display the list of sales items available (the Description and SalePrice of each item). The user can select as many items from this list as desired. We've selected four items in Figure A.

[ Figure A ]

Figure A: This example form uses the Multi Select list box.

Our form also includes a multiplier text box that will change the SalePrice value of items selected in the list box. The default value is 1.00, which won't cause any change (you'd be multiplying the SalePrice by one). But, if you change the value to 1.10, for instance, you'll apply a 10 percent increase to the SalePrice of the items selected.

Finally, the Update button triggers a VBA function to run and update the SalePrice for all items selected. To see the effect of the function on one form, we've placed the list box, multiplier text box, and Update button in the form's Header. The form's Record Source is set to tblSalesItems, so these records appear in the Detail section. When you update selected items, you can see their values change in the Detail section of the form.

Creating the update form

Next, create a form called frmSalePriceUpdate, set its Record Source property to tblSalesItems, and set its Default View to Continuous Forms, as you can see in Figure B.

[ Figure B ]

Figure B: The update form looks like this in design mode.

Turn the form's Header/Footer on by choosing View | Form Header/Footer from the form's design menu. Make the height of the Form Header about two inches. You won't use the Form Footer, so you can make its height zero inches.

In the Form Header, add the List Box control lstSalesItems. Don't use the Control Wizard, if it starts when you create the list box--instead, cancel out of the Wizard and manually set the list-box properties shown in Table B. Also, set the caption of the list box's associated label to Sales Items.

Table B: lstSalesItems list-box properties

Property Value
Name lstSalesItems

Control Source (blank)

Row Source Type Table/Query

Row Source tblSalesItems

Column Count 2

Bound Column 1

Multi Select Simple

Property	Value
Name	lstSalesItems
Control Source	(blank)
Row Source Type	Table/Query
Row Source	tblSalesItems
Column Count	2
Bound Column	1
Multi Select	Simple

Next, in the Form Header, add the txtMultiplier text box that will change the SalePrice value of items selected in the list box. Set the text box's properties as shown in Table C.

Table C: txtMultiplier text-box properties

Property Value
Name: txtMultiplier

Control Source: (blank)

Format: Standard

Decimal Places: Auto

Default Value: 1

Property	Value
Name:	txtMultiplier
Control Source:	(blank)
Format:	Standard
Decimal Places:	Auto
Default Value:	1

Now, add the Update button to the Form Header and set its properties as shown in Table D. Enter the following VBA code in the button's On Click event:


Private Sub btnUpdate_Click()
  Dim iRetVal As Integer
  iRetVal = UpdatePrices()
End Sub

As you can see, you'll be writing a function called UpdatePrices that will take the selected items and multiply them by the multiplier to get a new SalePrice for each item.

Table D: Update button properties

Property Value
Name: btnUpdate

Caption: &Update

On Click: [Event Procedure]

Property	Value
Name:	btnUpdate
Caption:	&Update
On Click:	[Event Procedure]

Next, add two labels to the Form Header: Description and Sale Price. Finally, just for cosmetic purposes, you can add a horizontal line between the list box and the two labels.

In the form's Detail section, add two text boxes with the Name property Description and SalePrice and the Control Source Description and SalePrice, respectively. Thus, you'll use these controls to display the data in table tblSalesItems.

As we mentioned earlier, the form's Default View is set to Continuous Forms--this allows Access to display multiple records on a form, as opposed to one record at a time. As a result, you'll be able to select sales items, update the price using the multiplier, and see the price changes--all on the same form.

VBA function to update prices

In the Update button's On Click event, you call a function named UpdatePrices. Listing A shows the code for this function. The function is saved in a module called modMultiSelectListBox (although the module's name has no bearing on the function).

Listing A: UpdatePrices function


Function UpdatePrices() As Integer

'--------------------------------------
' Purpose:  Using <frmSalesItems>, use selected entries 
` from [lstSalesItems] list box and [txtMultiplier] to 
` update [SalePrice] field in <tblSalesItems>.
'--------------------------------------
' By:       Pat Steiner
' Date:     6/30/98
' Version:  1.0
'--------------------------------------

Dim DB As Database
Dim RSSales As Recordset
Dim F As Form

Dim sMessage As String
Dim sMsgTitle As String
Dim sFunction As String
Dim sDescription As String

Dim iSelected As Integer
Dim iIndex As Integer
Dim iLoop As Integer

Dim vMultiplier As Variant

'--------------------------------
' 1. Initialize "local" variables and error trap.
'--------------------------------
UpdatePrices = 0
sMessage = ""
sFunction = "UpdatePrices"
sMsgTitle = "FN: " & sFunction & " ( )"

On Error GoTo Err_UpdatePrices

Set DB = CurrentDb()
Set F = Forms![frmSalePriceUpdate]

'--------------------------------
' 2. Make sure form has valid entries: at least 1 item 
` selected from list box and Multiplier is not Null.
'--------------------------------
iSelected = 0
iSelected = F![lstSalesItems].ItemsSelected.Count

vMultiplier = 1#    'Default value
vMultiplier = F![txtMultiplier]

If iSelected = 0 Or IsNull(vMultiplier) Then
  sMessage = _
    "You must select at least 1 entry from SALES LIST "
  sMessage = sMessage & "and a % CHANGE value." & _
    vbCrLf & vbCrLf
  sMessage = sMessage & "- - - PROCESS ABORTED - - -"
  Beep
  MsgBox sMessage, vbCritical, sMsgTitle
  Exit Function
End If

'--------------------------------
' 3. Open the <tblSalesItems> recordsets, [RSSales].
'--------------------------------
Set RSSales = DB.OpenRecordset("tblSalesItems", _
  dbOpenTable)
If RSSales.EOF Then
  RSSales.Close
  sMessage = "Table <tblSalesItems> has no data ..." & _
    vbCrLf & vbCrLf
  sMessage = sMessage & "- - - PROCESS ABORTED - - -"
  Beep
  MsgBox sMessage, vbCritical, sMsgTitle
  Exit Function
End If

RSSales.Index = "PrimaryKey"
'PrimaryKey is [Description] field

'--------------------------------
' 4. Data table OK ... begin processing selected data in
' the List Box control.
'--------------------------------
For iLoop = 0 To (iSelected - 1) 
'ItemsSelected collection is zero-based array
  
  iIndex = F![lstSalesItems].ItemsSelected(iLoop)
  sDescription = F![lstSalesItems].ItemData(iIndex)
  
  RSSales.Seek "=", sDescription
  
  RSSales.Edit
  RSSales![SalePrice] = RSSales![SalePrice] * vMultiplier
  RSSales.Update
    
Next iLoop
  
'--------------------------------
' 5. Close recordset.
'--------------------------------
RSSales.Close
  
'--------------------------------
' 6. Refresh form data.
'--------------------------------
DoCmd.SelectObject acForm, "frmSalePriceUpdate"
DoCmd.ShowAllRecords

'--------------------------------
' 7. Set function value and exit.
'--------------------------------
UpdatePrices = -1   '-1 indicates normal exit

Beep
sMessage = "All selected items were updated OK!"
MsgBox sMessage, vbInformation, sMsgTitle

Exit Function

'================
Err_UpdatePrices:
'================
Select Case Err

  Case 94:  'Invalid use of NULL
    Resume Next
    
  Case Else:
    Beep
    sMessage = "Error #" & Err & ": " & Error(Err)
    MsgBox sMessage, vbExclamation, sMsgTitle
    Exit Function

End Select

End Function

The UpdatePrices function code is well documented, but we'll point out the sections related to the Multi Select list box. In code section 2, you want to be sure the user has selected at least one item from the list box and that the multiplier text box hasn't been set to Null. In either of these cases, there's no need to proceed, and you display a warning message. In code section 4, you determine which list box items have been selected, and then perform the price update on these items.

The Multi Select list box has an ItemsSelected collection that stores the list of items selected in the list box. To check how many items are selected in the list box, you use the ItemsSelected.Count property, as in the following line:


iSelected = _
  F![lstSalesItems].ItemsSelected.Count

The ItemsSelected collection contains the index or list numbers of the items selected in the list box. It's zero-based, meaning if you select items 1, 3, 5, and 6--as we did in Figure A--they're stored in the ItemsSelected collection as 0, 2, 4, and 5 index values.

The list box has a property called ItemData(index) (where index is the value obtained from the ItemsSelected collection). ItemData really contains the data that you want--that is, the names of the items (Description) selected. Referring again to the items selected in Figure A, the following information is stored in the list box:


ItemsSelected.Count = 4
ItemsSelected(0)=0  (1st list box entry)
ItemsSelected(1)=2  (3rd list box entry)
ItemsSelected(2)=4  (5th list box entry)
ItemsSelected(3)=5  (6th list box entry)
ItemData(0)="Golf Balls"
ItemData(2)="Golf Hats"
ItemData(4)="Golf Shoes"
ItemData(5)="Irons"

Testing the form

In Figure C, we've selected four items in our list box.

[ Figure C ]

Figure C: Our form reflects price changes to our multi-selected items.

We've set the multiplier to 1.50 (or a 50 percent price increase). After clicking the Update button, you can see that the selected items shown in the Detail section of the form reflect the new prices. We purposely don't refresh the List Box control in this example, so you can see the changed prices easily.

Conclusion

The List Box control's Multi Select property is a very powerful feature that the Access developer can put to work in form design and use. You need to understand the ItemsSelected collection and the ItemData property values, and some VBA coding is required. We hope that our example application will get your creative juices flowing and take the mystery out of the required VBA programming.