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
- Private Sub btnSearch_Click()
- ' Update the record source
- 'Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew " & BuildFilter
- ' Update the record source
- If BuildFilter = "" Then
- Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew " & BuildFilter
- Else
- Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew WHERE " & BuildFilter
- End If
- 'Requery the subform
- Me.sbfrmSearchResults1.Requery
- End Sub
- Private Function BuildFilter() As Variant
- Dim varWhere As Variant
- Dim CountyCode As Variant
- Dim varItem As Variant
- Dim intIndex As Integer
- Dim NationalityCode As Variant
- Dim strSQLOrderBy As String
- varWhere = Null ' Main filter
- CountyCode = Null ' Subfilter used for colors
- NationalityCode = Null
- ' Check for LIKE First Name
- If Me.txtFirstName > "" Then
- varWhere = varWhere & "[FirstName] LIKE """ & Me.txtFirstName & "*"" AND "
- End If
- ' Check for LIKE Last Name
- If Me.txtSurname > "" Then
- varWhere = varWhere & "[surname] LIKE """ & Me.txtSurname & "*"" AND "
- End If
- If Me.txtRegNumber > "" Then
- varWhere = varWhere & "[regnumber] like """ & Me.txtRegNumber & """ And "
- End If
- ' Check for Colors in multiselect list
- For Each varItem In Me.lstCountyCode.ItemsSelected
- CountyCode = CountyCode & " [tblMemberDetails_CountyCode] = """ & _
- Me.lstCountyCode.ItemData(varItem) & """ OR "
- Next
- 'Test to see if we have subfilter for colors...
- If IsNull(CountyCode) Then
- ' do nothing
- Else
- ' strip off last "OR" in the filter
- If Right(CountyCode, 4) = " OR " Then
- CountyCode = Left(CountyCode, Len(CountyCode) - 4)
- End If
- 'Add some parentheses around the subfilter
- varWhere = varWhere & "( " & CountyCode & " )"
- End If
- 'NationalityCode
- ' Check for Nationality in multiselect list
- For Each varItem In Me.lstNationality.ItemsSelected
- NationalityCode = NationalityCode & " [tblmemberdetails.NationalityCode] = """ & _
- Me.lstNationality.ItemData(varItem) & """ OR "
- Next
- 'Test to see if we have subfilter for colors...
- If IsNull(NationalityCode) Then
- ' do nothing
- Else
- ' strip off last "OR" in the filter
- If Right(NationalityCode, 4) = " OR " Then
- NationalityCode = Left(NationalityCode, Len(NationalityCode) - 4)
- End If
- 'Add some parentheses around the subfilter
- varWhere = varWhere & "( " & NationalityCode & " )"
- End If
- 'Check if there is a filter to return...
- If IsNull(varWhere) Then
- varWhere = "''"
- Else
- ' strip off last "AND" in the filter
- If Right(varWhere, 5) = " AND " Then
- varWhere = Left(varWhere, Len(varWhere) - 5)
- End If
- End If
- BuildFilter = varWhere
- End Function
Any help would be greatly appreciated
Many thanks for the lifeline. JAMES