Multiple select listbox
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Multiple select listbox
Just wondered if this is possible
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Multiple select listbox
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:
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
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Multiple select listbox
Cheers Hans.
Not really used these previously, so hadn't noticed that option.
Appreciated :)
Not really used these previously, so hadn't noticed that option.
Appreciated :)
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Multiple select listbox
OK, next step; how to use the 'selected items' as criteria for a a report?
(bit stumped on this too )
(bit stumped on this too )
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Multiple select listbox
You'd use code like this:
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
Chr(34) is the double quote " that is required around string values.
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
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) & ")"
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 72
- Joined: 08 Mar 2010, 13:05
- Location: Manassas, VA
Re: Multiple select listbox
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.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Multiple select listbox
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.
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.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 72
- Joined: 08 Mar 2010, 13:05
- Location: Manassas, VA
Re: Multiple select listbox
Thanks. I figured that might be the case.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Multiple select listbox
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).
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Multiple select listbox
Thanks everyone, that was lots of help <thumbs up>
-
- StarLounger
- Posts: 72
- Joined: 08 Mar 2010, 13:05
- Location: Manassas, VA
Re: Multiple select listbox
I will save your sample Enrollment3 database and experiment to see if I can get it worked into my file. Thanks for this sample.