List box, multiple selection

  • Thread starter Thread starter Harmony
  • Start date Start date
H

Harmony

I have been working to try to get a list box to allow
multiple names selected and then those names selected, be
a criteria in my query. With the gracious help of others I
am pretty close, but haven't quite got it yet. I have the
following code on the LostFocus property of my list box.
This returns #Name? in my textwhere text box on my form,
but when I look in the control source it shows 'First Name
OR Second Name', which I think is what I want. So I guess
my question is, how do I get it to show up in my query
criteria?


Private Sub List28_LostFocus()
Dim strItems As String
Dim ctl As Control
Dim varItem As Variant

Set ctl = Me.List28

For Each varItem In ctl.ItemsSelected
strItems = strItems & ctl.ItemData(varItem) & "
OR "
Next varItem

'Trim the end of strSQL
strItems = Left$(strItems, Len(strItems) - 3)
txtWhere.ControlSource = strItems

End Sub
 
Is this textbox supposed to be "unbound", not connected to a table field? If
so, the Control Source should be blank or an equation. In this case, blank.
Set the Value of the textbox to your variable instead.

txtWhere = strItems

Value is the default, so the above is equivalent to

txtWhere.Value = strItems
 
Thanks, I got that to work! But now have encountered
another problem. When I select two or more names selected
my query does not return any records. If I select only one
name, it works. I think it is because it is not
recognizing them as two seperate texts. For example, the
text box is displaying: Amber Last OR Bernie First.

Any ideas?
 
Thanks, I got that to work! But now have encountered
another problem. When I select two or more names selected
my query does not return any records. If I select only one
name, it works. I think it is because it is not
recognizing them as two seperate texts. For example, the
text box is displaying: Amber Last OR Bernie First.

Any ideas?
Click to expand...
You need to enclose the text values (the names) in quotes.
Try this...

For Each varItem In ctl.ItemsSelected
strItems = strItems & Chr(34) & ctl.ItemData(varItem) _
& Chr(34) & " OR "
Next varItem

- Jim
 
Jim is correct. Also, if you have an ID field that would be unique for each
of these text values, it may be easier to make the list box a multi-column
box. Hide the ID column and make it the bound column. This would then be the
data that you pass to the query (changing the field in the query also, of
course). This may give you one item to pass for each selection instead of
the two items as you are indicating (i.e. Amber Last)
 
Back
Top