By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,491 Members | 1,209 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,491 IT Pros & Developers. It's quick & easy.

Multiple, MultiSelect Listboxs that run a Query on a Form

100+
P: 112
I am a newbie running Access 2003. A couple of weeks back a member named ADezil help me write a code that let a , MultiSelect Listboxs that run a Query on a Form (many thanks). I have used this code so much that I am trying to expand it to run off more then one list box. However I canít get it to work. Original code is below.

Expand|Select|Wrap|Line Numbers
  1. Private Sub PhotoSearch1_Click()
  2. Dim strOr As String
  3. Dim strDoc As String
  4. Dim varSelect As Variant
  5. Dim ctl As Control
  6.  
  7. strDoc = "frmDescription"
  8.  
  9. Set ctl = Forms!frmPhotoKeywordSearch.Controls("list1")
  10.  
  11. For Each varSelect In ctl.ItemsSelected
  12.   strOr = strOr & "[Photo Keywords] Like '*" & ctl.ItemData(varSelect) & "*' OR "
  13. Next varSelect
  14.  
  15. '   lose the last ' OR '
  16. strOr = Left(strOr, Len(strOr) - 4)
  17.  
  18. DoCmd.OpenForm strDoc, acNormal, , strOr
  19. DoCmd.Close acForm, "frmPhotoKeywordSearch"
  20. End Sub
Here is what I have been trying but it is not working. "list1" is my first listbox and "list2" is the one I am trying to add. Bothe are Multiselect list boxes.

Expand|Select|Wrap|Line Numbers
  1. Private Sub PhotoSearch1_Click()
  2. Dim strOr As String
  3. Dim strDoc As String
  4. Dim varSelect As Variant
  5. Dim ctl As Control
  6.  
  7. strDoc = "frmDescription"
  8.  
  9. Set ctl = Forms!frmPhotoKeywordSearch.Controls("list1")
  10.  
  11. For Each varSelect In ctl.ItemsSelected
  12.   strOr = strOr & "[Photo Keywords] Like '*" & ctl.ItemData(varSelect) & "*' OR "
  13. Next varSelect
  14.  
  15. Set ctl = Forms!frmPhotoKeywordSearch.Controls("list2")
  16.  
  17. For Each varSelect In ctl.ItemsSelected
  18.   strOr = strOr & "[Photo Keywords] Like '*" & ctl.ItemData(varSelect) & "*' OR "
  19. Next varSelect
  20.  
  21. '   lose the last ' OR '
  22. strOr = Left(strOr, Len(strOr) - 4)
  23.  
  24. DoCmd.OpenForm strDoc, acNormal, , strOr
  25. DoCmd.Close acForm, "frmPhotoKeywordSearch"
  26. End Sub
Is this simple and I am missing something or really complicated? Any help would be great.

Thanks
Nov 13 '07 #1
Share this Question
Share on Google+
1 Reply


nico5038
Expert 2.5K+
P: 3,072
The code looks OK, but I find it strange that you're selecting the same field from multiple listboxes.
Another thing that can cuase trouble is the limitation of the length of the string. When too long it might get truncated. Use a breakpoint and the F8 to single step through your code to see what string is build.

Nic;o)
Nov 16 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.