Using multi select in list box

  • Thread starter Thread starter Keith
  • Start date Start date
K

Keith

I'm trying to set up a list box to allow the selection of
up to 4 choices. Holding control allows the selection of
multiple choices, but the choices are not retained if I go
to a new record, nor are they saved in the table. Is
there a way to keep these choices so that they can be
viewed in the form and saved in the table for future
queries?
 
You have to use code to retrieve selections from a multi-
select list box. Here's an example...

Controls: lstMyListBox, txtMySelections
'lstMyListBox' is an unbound, multi-select list box control
'txtMySelections' is a text box control bound to a field
in your database

Private Sub lstMyListBox_AfterUpdate()
Me!txtMySelections = GetSelections(Me!lstMyListBox)
End Sub

Function GetSelections(ctl As ListBox, _
Optional strDataType As String = "Text") As String
Dim var As Variant
Dim str As String
For Each var In ctl.ItemsSelected
Select Case strDataType
Case "Number": str = str _
& "," & ctl.ItemData(var)
Case "Text": str = str _
& ",'" & ctl.ItemData(var) & "'"
Case "Date": str = str _
& ",#" & ctl.ItemData(var) & "#"
End Select
Next var
GetSelections = Mid(str, 2)
End Function

You will end up with a string of comma separated values
stored in a single field in your table. The records can
be queried later by making use of the 'Like' comparison
operator in your query criteria.

eg) "SELECT myTable.* FROM myTable " _
& "WHERE myField Like'*" & myValue & "*'"

I hope this helps. good luck.
 
Keith said:
I'm trying to set up a list box to allow the selection of
up to 4 choices. Holding control allows the selection of
multiple choices, but the choices are not retained if I go
to a new record, nor are they saved in the table. Is
there a way to keep these choices so that they can be
viewed in the form and saved in the table for future
queries?
Click to expand...

Storing multiple values in a single field violates relational database design
principles. For that reason it is understandable that the Multi-Select option
for ListBoxes does not provide this functionality. In fact when you enable the
Multi-Selection feature the ListBox will always have a value of Null. The only
legitimate use of a Multi-Select ListBox is so you can manipulate the choices in
a code routine of some sort.

When you need to "relate" multiple values to a row in a table you should be
using a second table. There is then a one-to-many relationship between the
original table and the new one. This allows you to create a form/subform
combination that would allow as many entries in the related table as you need
and each value still gets its own field in its own row of the new table.
 
Back
Top