Multi Select ListBox to populate field(s)
Multi Select ListBox to populate field(s)
(OP)
I want the selection(s) in a list box to populate field(s) in the underlying table. Can MULTI selections populate one field? If my list box had these choices; red(1), blue(2), green(3), orange(4), and the user chose 1 and 3, can the color field in my uderlying table be populated 1,3 ? And then, if that can be done, how do I use this in reporting?
RE: Multi Select ListBox to populate field(s)
First if you use the multi-select feature of the list box, then the list box must remain unbound.
So with this in mind, you will need to have a textbox on the form to receive the information from the listbox. This txtbox can be hidden from view if necessary. Below is an example of code that should do what you want. I assumed you wanted a comma and a space seperating each choice. If not, alter the code to your needs.
You will also need some way of triggering when the last selection has been made. In the example below I used a cmdButton. First select all choices, then click the button to transfer the results to the textbox that is bound to the table where the information is to be stored.
Private Sub cmdSelectItems_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strTemp As String
Set frm = Me 'sets form to active form
Set ctl = frm!YourListBox 'Name of your listbox
For Each varItem In ctl.ItemsSelected
strTemp = strTemp & ctl.ItemData(varItem) & ", " 'adds the comma and space
Next varItem
strTemp = Left$(strTemp, Len(strTemp) - 2) 'Remove the last comma and space
Me![txtResultOfList] = strTemp
End Sub
"Me.[txtResultOfList]" is the textbox where the information is populated from the listbox.
Hope I haven't lost you, (hope I haven't lost myself)
HTH
RDH
Ricky Hicks
rdhicks@mindspring.com