MultiSelect Property

[LizChappell]

I want to use a List Box on dialog form to select several items in a query - I can set the List Box MultiSelect property to 'Simple' OK - but don't know how to get the query to read the selected values as the list box control value gets set to null - help please!!

 

[DougP]

I was working on a solution.<br>I have to pieces of code here.<br>First is a function the would be called from a query.<br>--------------------------------------<br>Public Function BuildSelectedList(IsSelected)<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim ctlList As Control, varItem As Variant<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim Criteria As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Set ctlList = Forms!Form1!List1<br>&nbsp;&nbsp;&nbsp;&nbsp;For Each varItem In ctlList.ItemsSelected<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' Print value of bound column.<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print ctlList.ItemData(varItem)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Criteria = Criteria & &quot;'&quot; & ctlList.ItemData(varItem) & &quot;' OR &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;Next varItem<br>&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print Left(Criteria, Len(Criteria) - 3)<br>&nbsp;&nbsp;&nbsp;&nbsp;BuildSelectedList = Left(Criteria, Len(Criteria) - 3)<br>End Function<br>--------------------------------<br>It does not work though maybe someone can figure out why.<br>Second is code behind a command button on the form.<br>It builds a SQL string that you could then use to open a recordset with.<br>--------------------------------------<br>Private Sub Command3_Click()<br>On Error GoTo Err_Command3_Click<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim A As Integer, SQL, Criteria As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim ctlList As Control, varItem As Variant<br>&nbsp;&nbsp;&nbsp;&nbsp;SQL = &quot;Select * From Employees Where EmployeeID = &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;' Return Control object variable pointing to list box.<br>&nbsp;&nbsp;&nbsp;&nbsp;Set ctlList = Forms!Form1!List1<br>&nbsp;&nbsp;&nbsp;&nbsp;' Enumerate through selected items.<br>&nbsp;&nbsp;&nbsp;&nbsp;For Each varItem In ctlList.ItemsSelected<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' Print value of bound column.<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print ctlList.ItemData(varItem)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Criteria = Criteria & ctlList.ItemData(varItem) & &quot; AND &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;Next varItem<br>&nbsp;&nbsp;&nbsp;&nbsp;Criteria = Left(Criteria, Len(Criteria) - 4)<br>&nbsp;&nbsp;&nbsp;&nbsp;SQL = SQL & Criteria<br><br>Exit_Command3_Click:<br>&nbsp;&nbsp;&nbsp;&nbsp;Exit Sub<br><br>Err_Command3_Click:<br>&nbsp;&nbsp;&nbsp;&nbsp;MsgBox Err.Description<br>&nbsp;&nbsp;&nbsp;&nbsp;Resume Exit_Command3_Click<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>End Sub<br>---------------------------------<br>OK good luck

 

[misterstick]

you have to loop through the items of the ListBox testing the Selected property<br><br><b><FONT FACE=monospace><br>Dim lngIter as Long<br>For lngIter = 0 to ListBox1.ListCount - 1<br>&nbsp;&nbsp;&nbsp;&nbsp;If ListBox1.Selected(lngIter) Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'...code block...<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>Next<br></font></b> <p>mr s. <<br><a href=mailto: > </a><br><a href= > </a><br>why does it never do what is says it does in the manual?

 

[misterstick]

<i>does anybody else get irritated by posts that happen while you're posting (interposting?)? especially when the poster sees the problem more clearly than you and provides a better but incomplete solution?</i><br><br>code block one doesn't work because the criteria string should be a selection of fieldname=value pairs separated by ORs.<br><br>Try:<br><br><b><FONT FACE=monospace><br>' Takes: a multiselect listbox of values and the fieldname<br>' Returns: an SQL boolean string<br>Public Function BuildSelectedList(ctlList as Control, strFieldName as string) As String<br>Dim varItem As Variant<br>For Each varItem In ctlList.ItemsSelected<br>&nbsp;&nbsp;BuildSelectedList = BuildSelectedList _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& &quot; OR [&quot; & strFieldName & &quot;] = '&quot; _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& ctlList.ItemData(varItem) & &quot;'&quot;<br>Next<br>BuildSelectedList = Mid$(BuildSelectedList, 5)<br>End Function<br><br>Private Sub Command3_Click()<br>Dim strSQL As String<br>strSQL = &quot;Select * From Employees Where &quot; _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& BuildSelectedList(Forms!Form1!List1, &quot;Employee&quot & &quot;;&quot;<br>' some code that uses the SQL...<br>End Sub<br></font></b><br><br> <p>mr s. <<br><a href=mailto: > </a><br><a href= > </a><br>why does it never do what is says it does in the manual?