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

2 multi select list boxes on one search form

P: 15
Hi Guys and thanks for your time.

I have a search form for my database that allows users to select multiple criteria from multi select list boxes.

I successfully integrated a multi select listbox for users to select and search for counties.

On the same page however I would like to integrate a similar multiselect box for nationality. I would like the user to be able to search for nationality with county or individually.

After inserting my nationality list box and adding the code. i can now only search for either
nationality or county and cant perform a combined search.


Expand|Select|Wrap|Line Numbers
  1. Private Sub btnSearch_Click()
  2.  
  3.     ' Update the record source
  4.     'Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew " & BuildFilter
  5.  
  6.     ' Update the record source
  7.     If BuildFilter = "" Then
  8.         Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew " & BuildFilter
  9.     Else
  10.         Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew WHERE " & BuildFilter
  11.     End If
  12.  
  13.     'Requery the subform
  14.     Me.sbfrmSearchResults1.Requery
  15.     End Sub
  16. Private Function BuildFilter() As Variant
  17.     Dim varWhere As Variant
  18.     Dim CountyCode As Variant
  19.     Dim varItem As Variant
  20.     Dim intIndex As Integer
  21.     Dim NationalityCode As Variant
  22.     Dim strSQLOrderBy As String
  23.  
  24.     varWhere = Null  ' Main filter
  25.     CountyCode = Null  ' Subfilter used for colors
  26.     NationalityCode = Null
  27.     ' Check for LIKE First Name
  28.  
  29. If Me.txtFirstName > "" Then
  30.         varWhere = varWhere & "[FirstName] LIKE """ & Me.txtFirstName & "*"" AND "
  31.     End If
  32.  
  33.     ' Check for LIKE Last Name
  34.     If Me.txtSurname > "" Then
  35.         varWhere = varWhere & "[surname] LIKE """ & Me.txtSurname & "*"" AND "
  36.     End If
  37.  
  38.     If Me.txtRegNumber > "" Then
  39.         varWhere = varWhere & "[regnumber] like """ & Me.txtRegNumber & """  And "
  40.    End If
  41.  
  42.  ' Check for Colors in multiselect list
  43.     For Each varItem In Me.lstCountyCode.ItemsSelected
  44.         CountyCode = CountyCode & " [tblMemberDetails_CountyCode] = """ & _
  45.                     Me.lstCountyCode.ItemData(varItem) & """ OR "
  46.  
  47.     Next
  48.  
  49.     'Test to see if we have subfilter for colors...
  50.     If IsNull(CountyCode) Then
  51.         ' do nothing
  52.     Else
  53.         ' strip off last "OR" in the filter
  54.         If Right(CountyCode, 4) = " OR " Then
  55.             CountyCode = Left(CountyCode, Len(CountyCode) - 4)
  56.         End If
  57.  
  58.         'Add some parentheses around the subfilter
  59.         varWhere = varWhere & "( " & CountyCode & " )"
  60.     End If
  61.  
  62.      'NationalityCode
  63.  
  64.         ' Check for Nationality in multiselect list
  65.     For Each varItem In Me.lstNationality.ItemsSelected
  66.         NationalityCode = NationalityCode & " [tblmemberdetails.NationalityCode] = """ & _
  67.                     Me.lstNationality.ItemData(varItem) & """ OR "
  68.  
  69.     Next
  70.  
  71.     'Test to see if we have subfilter for colors...
  72.     If IsNull(NationalityCode) Then
  73.         ' do nothing
  74.     Else
  75.         ' strip off last "OR" in the filter
  76.         If Right(NationalityCode, 4) = " OR " Then
  77.             NationalityCode = Left(NationalityCode, Len(NationalityCode) - 4)
  78.         End If
  79.  
  80.         'Add some parentheses around the subfilter
  81.         varWhere = varWhere & "( " & NationalityCode & " )"
  82.     End If
  83.  
  84.      'Check if there is a filter to return...
  85.     If IsNull(varWhere) Then
  86.         varWhere = "''"
  87.     Else
  88.  
  89.         ' strip off last "AND" in the filter
  90.         If Right(varWhere, 5) = " AND " Then
  91.             varWhere = Left(varWhere, Len(varWhere) - 5)
  92.         End If
  93.  
  94.     End If
  95.      BuildFilter = varWhere
  96.      End Function
  97.  
I think it may be something to do striping off last "OR".

Any help would be greatly appreciated


Many thanks for the lifeline. JAMES
Feb 24 '09 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 1,287
Given that you have lines 89-92, you can simply change line 59 to:
varWhere = varWhere & "( " & CountyCode & " ) AND "
Feb 24 '09 #2

P: 15
ChipR

Your the man.

Worked like a charm.

I owe you a beer.

Many thanks and the best of Luck all the way from Ireland.

Regards

thanks so much again

JAMES
Feb 24 '09 #3

Post your reply

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