Hi Everyone. Thanks for your time.
I am trying to create a search form that will allow users to select criteria from multiple multi select boxes. So far i have managed to achieve a search option for 2 list boxes:- county and nationality, while trying to add a third multi select list box for qualifications search is where i encounter my problem.
I've copied the working code from my working list boxes, however it cant seem to pick up the search value from the qualification list box.
I think this error may stem from the relatioships but i am not sure.
This is the record source property of one of my working list boxes:
lstNationality :SELECT [tblNationality].[NationalityCode], [tblNationality].[Nationality] FROM tblNationality;
This is the record source property of my new troublesom qualification list box:
SELECT [tblQualifications].[QualCode], [tblQualifications].[ShortQual] FROM tblQualifications ORDER BY [ShortQual];
Am i correct in asuming that it's a relationship issue?
I have attached a screnshot of my relationships, and included a copy of my code in this post, if any body has any ideas they would be greatly appericated.
Many thanks once again for your time Kind regards, JAMES
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 varItem As Variant
Dim intIndex As Integer
Dim CountyCode As Variant
Dim NationalityCode As Variant
Dim qualCode As Variant
varWhere = Null ' Main filter
CountyCode = Null ' Subfilter used for CountyCode
NationalityCode = Null ' Subfilter used for NationalityCode
qualCode = Null ' Subfilter used for qualCode
' 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
' Check for LIKE regNumber
If Me.txtRegNumber > "" Then
varWhere = varWhere & "[regnumber] like """ & Me.txtRegNumber & """ And "
End If
' Check for counties 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 & " ) AND "
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 & " ) AND "
End If
'qualCode
' Check for qualCode in multiselect list
For Each varItem In Me.lstqual1.ItemsSelected
qualCode = qualCode & " [tblqualifications_qualCode] = """ & _
Me.lstqual1.ItemData(varItem) & """ OR "
Next
'Test to see if we have subfilter for colors...
If IsNull(qualCode) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(qualCode, 4) = " OR " Then
qualCode = Left(qualCode, Len(qualCode) - 4)
End If
'Add some parentheses around the subfilter
varWhere = varWhere & "( " & qualCode & " ) "
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