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 relationships 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
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 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