Using Multi-Selection Listboxes in a query 3

[Jengo]

How do you use a multi-selected list box, in a query. I mean how do you search or any of the selected items in the listbox?

 

[DougP]

you will probably have to Write VBA code to do that.<br><br>Look at Listbox in Help<br>there is a .selected property and a .listcount <br>here is a simple example to get you started.<br>-----------------------------<br>Private Sub Command1_Click()<br>&nbsp;&nbsp;&nbsp;&nbsp;dim a as integer<br>&nbsp;&nbsp;&nbsp;&nbsp;For a = 0 To List1.ListCount - 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If List1.Selected(a) = True Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print &quot;Got it&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;Next<br>&nbsp;&nbsp;&nbsp;&nbsp;' this will let you know how many were selected<br>&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print List1.SelCount<br>End Sub <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[Jengo]

I need to be able to search through a query, any of the possible values selected in a list box.

 

[DougP]

Access does not allow a query to see a multi-selected Listbox that's why I said use VBA.<br><br>How many selected items are we talking about 10? 20? 9000?<br><br>using VBA you can create a string Like:<br>Item1 And Item3 AND Item4 and Item8<br>where Item# is the list selection and VBA puts the word &quot;AND&quot; or &quot;OR&quot; in between then your query looks at a text box that has all of tha in it.<br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[Jengo]

Can you give me a head start.&nbsp;&nbsp;I have never had to do it manually?

 

[DougP]

This works if the Bound Column is the one which has your data in it. &gt;&gt;&gt;&gt;&gt;List1.ItemData(a)<br><br><br>--------------------------------<br>Private Sub Command2_Click()<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim a As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim QryString As String<br>&nbsp;&nbsp;&nbsp;&nbsp;For a = 0 To List1.ListCount - 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If List1.Selected(a) = True Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;QryString = QryString & List1.ItemData(a) & &quot; AND &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print &quot;Got it&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Next<br>&nbsp;&nbsp;&nbsp;&nbsp;QryString = Left(QryString, Len(QryString) - 5)<br>&nbsp;&nbsp;&nbsp;&nbsp;' this will let you know how many were selected<br>&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print QryString<br>End Sub<br>-----------------------<br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[DreewToo]

Thank you Doug,<br><br>I stuggled with making the listboxes selections work as query criteria longer than I care to admit. <br><br>Drew

 

[DougP]

One thing I forgot to mention<br>Below the QryString = Left(QryString ...<br>Put your Text box name like so<br><br>Me!Text1 = QryString <br><br>Then in your query put this in the Criteria of the field you want to see the Listboxes selected items.<br><br>[Forms]![Formname]![Text1]<br><br>So your Query will reflect the selections made in the list box. <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[DougP]

Also after testing my example further I noticed that I had to add single quotes if the items were strings<br>so here is the modifed and tested version<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Dim a As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim QryString As String<br>&nbsp;&nbsp;&nbsp;&nbsp;For a = 0 To List1.ListCount - 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If List1.Selected(a) = True Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;QryString = QryString & Chr$(34) & List1.ItemData(a) & Chr$(34) & &quot; OR &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print &quot;Got it&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;Next<br>&nbsp;&nbsp;&nbsp;&nbsp;QryString = Left(QryString, Len(QryString) - 4)<br>&nbsp;&nbsp;&nbsp;&nbsp;Text1 = Trim(QryString)<br><br>Notice: that because the Word &quot;OR&quot; is shorter than AND, you subtract 4 characters instead of 5 <br>QryString = Left(QryString, Len(QryString) - 4)&lt;&lt;&lt;&lt;&lt;&lt;<br><br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[Jengo]

Thank you so very much, this was extremely helpful