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

Multiple list selection in search form

100+
P: 374
Hi everyone,

I am using the following code adapted from Allen Browne:

Expand|Select|Wrap|Line Numbers
  1. 'Purpose:   This module illustrates how to create a search form, _
  2.             where the user can enter as many or few criteria as they wish, _
  3.             and results are shown one per line.
  4. 'Note:      Only records matching ALL of the criteria are returned.
  5. 'Author:    Allen Browne (allen@allenbrowne.com), June 2006.
  6. Option Compare Database
  7. Option Explicit
  8.  
  9. Private Sub cmdFilter_Click()
  10.     'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
  11.     'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
  12.                         we remove the trailing " AND " at the end.
  13.     '           2. The date range works like this: _
  14.                         Both dates      = only dates between (both inclusive. _
  15.                         Start date only = all dates from this one onwards; _
  16.                         End date only   = all dates up to (and including this one).
  17.     Dim strWhere As String                  'The criteria string.
  18.     Dim lngLen As Long                      'Length of the criteria string to append to.
  19.     Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
  20.  
  21.     '***********************************************************************
  22.     'Look at each search box, and build up the criteria string from the non-blank ones.
  23.     '***********************************************************************
  24.     'Text field example. Use quotes around the value in the string.
  25.    ' If Not IsNull(Me.txtDepartment) Then
  26.   '      strWhere = strWhere & "([Department] = """ & Me.txtDepartment & """) AND "
  27.    ' End If
  28.  
  29.     'Another text field example. Use Like to find anywhere in the field.
  30.     If Not IsNull(Me.txtRaisedBy) Then
  31.         strWhere = strWhere & "([Raised By] Like ""*" & Me.txtRaisedBy & "*"") AND "
  32.     End If
  33.  
  34.     'Number field example. Do not add the extra quotes.
  35.     If Not IsNull(Me.txtNCCID) Then
  36.         strWhere = strWhere & "([NCC ID Number] = " & Me.txtNCCID & ") AND "
  37.     End If
  38.  
  39.     'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
  40.   '  If Me.txtDepartmemt = -1 Then
  41.     '    strWhere = strWhere & "([Department] = True) AND "
  42.    ' ElseIf Me.txtDepartment = 0 Then
  43.     '    strWhere = strWhere & "([Department] = False) AND "
  44.    ' End If
  45.  
  46.     'Date field example. Use the format string to add the # delimiters and get the right international format.
  47.     If Not IsNull(Me.txtDate) Then
  48.         strWhere = strWhere & "([Date] >= " & Format(Me.txtDate, conJetDate) & ") AND "
  49.     End If
  50.  
  51.     'Another date field example. Use "less than the next day" since this field has times as well as dates.
  52.     'If Not IsNull(Me.txtEndDate) Then   'Less than the next day.
  53.    '     strWhere = strWhere & "([EnteredOn] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
  54.    ' End If
  55.  
  56.     '***********************************************************************
  57.     'Chop off the trailing " AND ", and use the string as the form's Filter.
  58.     '***********************************************************************
  59.     'See if the string has more than 5 characters (a trailng " AND ") to remove.
  60.     lngLen = Len(strWhere) - 5
  61.     If lngLen <= 0 Then     'Nah: there was nothing in the string.
  62.         MsgBox "No criteria", vbInformation, "Nothing to do."
  63.     Else                    'Yep: there is something there, so remove the " AND " at the end.
  64.         strWhere = Left$(strWhere, lngLen)
  65.         'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
  66.         'Debug.Print strWhere
  67.  
  68.         'Finally, apply the string as the form's Filter.
  69.         Me.Filter = strWhere
  70.         Me.FilterOn = True
  71.     End If
  72. End Sub
  73.  
  74. Private Sub cmdReset_Click()
  75.     'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
  76.     Dim ctl As Control
  77.  
  78.     'Clear all the controls in the Form Header section.
  79.     For Each ctl In Me.Section(acHeader).Controls
  80.         Select Case ctl.ControlType
  81.         Case acTextBox, acComboBox
  82.             ctl.Value = Null
  83.         Case acCheckBox
  84.             ctl.Value = False
  85.         End Select
  86.     Next
  87.  
  88.     'Remove the form's filter.
  89.     Me.FilterOn = False
  90. End Sub
  91.  
  92. Private Sub Form_BeforeInsert(Cancel As Integer)
  93.     'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
  94.     'We prevent new records by cancelling the form's BeforeInsert event instead.
  95.     'The problems are explained at http://allenbrowne.com/bug-06.html
  96.     Cancel = True
  97.     MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
  98. End Sub
  99.  
  100. Private Sub Form_Open(Cancel As Integer)
  101.     'Remove the single quote from these lines if you want to initially show no records.
  102.     Me.Filter = "(False)"
  103.     Me.FilterOn = True
  104. End Sub
What I am trying to get my head around is how would I code within the filter script to be able to filter based on multiple/single or no selections within a list box I have created.

The list box contains "Departments", so their names. If I select Blading and Projects, I want the filter to only display records for those two departments.

As you can see in the code I have commented out a couple of attempts with no luck and I dont want to be going around in circles for too long without any help.

I have named the list box lstDepartment.

Thanks for any help,

Chris
May 7 '10 #1

✓ answered by Megalog

Updated Reset Code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdReset_Click() 
  2.     'Purpose:   Clear all the search boxes in the Form Header, and show all records again. 
  3.     Dim ctl As Control 
  4.  
  5.     'Clear all the controls in the Form Header section. 
  6.     For Each ctl In Me.Section(acHeader).Controls 
  7.         Select Case ctl.ControlType 
  8.         Case acTextBox, acComboBox 
  9.             ctl.Value = Null 
  10.         Case acCheckBox 
  11.             ctl.Value = False 
  12.         Case acListBox
  13.             call ClearList(ctl.Name)
  14.         End Select 
  15.     Next 
  16.  
  17.     'Remove the form's filter. 
  18.     Me.FilterOn = False 
  19. End Sub 

Place this function anywhere, it will clear the listbox when called from the Reset code.
Expand|Select|Wrap|Line Numbers
  1. Public Function ClearList(lst As ListBox)
  2.     Dim varItem As Variant
  3.  
  4.     If lst.MultiSelect = 0 Then
  5.         lst = Null
  6.     Else
  7.         For Each varItem In lst.ItemsSelected
  8.             lst.Selected(varItem) = False
  9.         Next
  10.     End If
  11.  
  12. End Function

Share this Question
Share on Google+
7 Replies


Megalog
Expert 100+
P: 378
@munkee
What's the name of your list box control? Is it bound to a numeric value, or the department text value (are you using a single column, or multiple columns)? What we'll need to do is loop through all the selections and build a big OR string out of them.
May 7 '10 #2

Megalog
Expert 100+
P: 378
This should work in the script by Allen Browne, place it anywhere in the function where it is building the strWhere string. Make sure the lisbox is set to Extended type (for muliple selections). Replace "lstDeptFilter" & "[Department]" with your list control name and your Department field name.

First column is bound to a text value
Expand|Select|Wrap|Line Numbers
  1. 'Listbox example.
  2.     If Me.lstDeptFilter.ItemsSelected.Count > 0 Then
  3.         Dim itm As Variant
  4.         Dim strList As String
  5.         For Each itm In Me.lstDeptFilter.ItemsSelected
  6.             If strList = "" Then
  7.                 strList = "([Department] = '" & Me.lstDeptFilter.Column(0, itm) & "')"
  8.             Else
  9.                 strList = strList & " OR " & "([Department] = '" & Me.lstDeptFilter.Column(0, itm) & "')"
  10.             End If
  11.         Next
  12.         strWhere = strWhere & "(" & strList & ") AND "
  13.     End If
First Column is bound to a numeric value:
Expand|Select|Wrap|Line Numbers
  1. 'Listbox example.
  2.     If Me.lstDeptFilter.ItemsSelected.Count > 0 Then
  3.         Dim itm As Variant
  4.         Dim strList As String
  5.         For Each itm In Me.lstDeptFilter.ItemsSelected
  6.             If strList = "" Then
  7.                 strList = "([Department] = " & Me.lstDeptFilter.Column(0, itm) & ")"
  8.             Else
  9.                 strList = strList & " OR " & "([Department] = " & Me.lstDeptFilter.Column(0, itm) & ")"
  10.             End If
  11.         Next
  12.         strWhere = strWhere & "(" & strList & ") AND "
  13.     End If
May 7 '10 #3

100+
P: 374
Thank you for the code Megalog I will try and implement it at work on Monday.

Whilst you are viewing, I have also been trying to clear selections from the list whenever I press the reset button which is situated in the code at this point: Private Sub cmdReset_Click().

What is the convention you use to clear a list box? is it acList or ListBox for my case?
May 7 '10 #4

Megalog
Expert 100+
P: 378
Updated Reset Code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdReset_Click() 
  2.     'Purpose:   Clear all the search boxes in the Form Header, and show all records again. 
  3.     Dim ctl As Control 
  4.  
  5.     'Clear all the controls in the Form Header section. 
  6.     For Each ctl In Me.Section(acHeader).Controls 
  7.         Select Case ctl.ControlType 
  8.         Case acTextBox, acComboBox 
  9.             ctl.Value = Null 
  10.         Case acCheckBox 
  11.             ctl.Value = False 
  12.         Case acListBox
  13.             call ClearList(ctl.Name)
  14.         End Select 
  15.     Next 
  16.  
  17.     'Remove the form's filter. 
  18.     Me.FilterOn = False 
  19. End Sub 

Place this function anywhere, it will clear the listbox when called from the Reset code.
Expand|Select|Wrap|Line Numbers
  1. Public Function ClearList(lst As ListBox)
  2.     Dim varItem As Variant
  3.  
  4.     If lst.MultiSelect = 0 Then
  5.         lst = Null
  6.     Else
  7.         For Each varItem In lst.ItemsSelected
  8.             lst.Selected(varItem) = False
  9.         Next
  10.     End If
  11.  
  12. End Function
May 7 '10 #5

100+
P: 374
Appreciate the hard work I will put some feedback up on Monday, Thanks.
May 7 '10 #6

100+
P: 374
Just tried the code and it works like a charm thanks!
May 10 '10 #7

Megalog
Expert 100+
P: 378
Glad it all worked for you!

Welcome to Bytes!
May 10 '10 #8

Post your reply

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