Multiple select listbox

[Egg 'n' Bacon]

Just wondered if this is possible

[HansV]

Yes, it is. The Multi Select property of a list box (in the Other tab of the Properties window) has 3 possible values:

None: this is the default, you can select only one item at a time.

Simple: you can select multiple items. Click on an unselected item to add it to the selection, click on a selected item to remove it from the selection.

Extended: you can select multiple items the same way as you can select files in Windows Explorer:
- Click on an item, Shift+click on another item to select a range of items.
- Ctrl+click to add individual items to the selection or to remove them from the selection.

In Visual Basic, you can use the ItemsSelected property to get at the selected items of a multi-select list box:

Code: Select all

Dim varItm As Variant
For Each varItm In Me.ListBox1.ItemsSelected
  Debug.Print Me.ListBox1.ItemData(varItm)
Next varItm

[Egg 'n' Bacon]

Cheers Hans.

Not really used these previously, so hadn't noticed that option.

Appreciated :)

[Egg 'n' Bacon]

OK, next step; how to use the 'selected items' as criteria for a a report?

(bit stumped on this too )

[HansV]

You'd use code like this:

Code: Select all

  Dim strIn As String
  Dim strWhere As String
  Dim varItm As Variant
  For Each varItm As Variant
    strIn = strIn & ", " & varItm
  Next varItm
  If Not strIn = "" Then
    strWhere = "[FieldName] In (" & Mid(strIn, 3) & ")"
  End If
  DoCmd.OpenReport "rptMyReport", acViewPreview, , strWhere

You'll have to substitute the appropriate names, of course.

Note: the above assumes that the bound column of the list box corresponds to a number field. If it corresponds to a text field, use

Code: Select all

    strWhere = "[FieldName] In (" & Chr(34) & Mid(strIn, 3) & Chr(34) & ")"

Chr(34) is the double quote " that is required around string values.

[JudyJones]

Can a muli select listbox be used to store the multiple items in a field. Each sample I looked at simply output the data to a report. I am trying to make use of this on a form and have it save the one, two, three, or more choices I make into a field, separated by commas. There is a separate field for each day of the month: 01Serv, 02Serv, 03Serv etc. I would like each day's field to hold all the services a child receives on that day.

[HansV]

It'd be better to create a "child" table with a separate record for each day, and to use a subform for data entry. This makes it much easier to perform calculations across the days of the month.

And although Access 2007 introduces a new field type: multivalued, I wouldn't recommend using it. Working with multiple values in a single field is difficult.

[JudyJones]

Thanks. I figured that might be the case.

[HansV]

Although I prefer to use a subform to display records from a "child" table, it is possible to use a multi-select list box for this purpose. The attached (zipped) database demonstrates how to do this (it requires VBA code, whereas a subform often doesn't).

Enrollment3.zip

[Egg 'n' Bacon]

Thanks everyone, that was lots of help <thumbs up>

[JudyJones]

I will save your sample Enrollment3 database and experiment to see if I can get it worked into my file. Thanks for this sample.