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.
Code:
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