Search form multiple multi select list box's Error

[woodey2002]

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

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
 

Thanks again.

[NeoPa]

I will only get involved as far as to suggest checking out Example Filtering on a Form.

[ADezii]

1. Could it be as smple as a Syntax Error, I am referring to Code Line #98
   

   'Check for qualCode in multiselect list
   For Each varItem In Me.lstqual1.ItemsSelected
     qualCode = qualCode & " [tblqualifications].[qualCode] = """ & _
                Me.lstqual1.ItemData(varItem) & """ OR "
   Next

2. You are treating the [qualCode] Field as if it were a String, is this Field, in fact, a String?

[woodey2002]

Thanks for the reply hugely appreciated.

I have tried your suggestion and its giving me a runtime error
"data type mismatch in criteria expression"

If i try [tblqualifications]_[qualCode] i get a syntax error in query expression
'( [tblqualifications]_[qualCode] = "3") this error seems somewhat correct because its recognising the qualCode from the listbox.

In answer to your question i am confident that its a string because the two working search options county and nationality seem to work following this principle.

Its strange because my code works for county search with
[tblMemberDetails_CountyCode] where as my code for nationality search has to be [tblNationality.nationalityCode].

I dont know why this is but i am assuming that this might be giving me issues with qualification searching via the multi select list box.

having reviewed my database i discovered that the fields for my successful searches are stored in tblMemberDetails where as the field for qualification is located in another table. i think i need to reference tblMemberQualifications.qualCode as opposed to tblQualifications.qualCode but yet again i am not sure.

I have attached a sample version of my database, if anybody could have a look at it i would greatly appericate it as this is driving Me CRAZY.

Thanks again for all the support.

Regards James.

[ADezii]

@woodey2002
Right now, I'm on vacation and working with an older Version of Access (2000), which is essentially useless in this case. When I return home on Friday, and if this Thread has not yet been resolved, I'll have a good look at it and get back to you during the weekend.

[OldBirdman]

If line 12 were Debug.Print Me.sbfrmSearchResults1.Form.RecordSource, you would have the actual SQL string used. If you paste that into the SQL window in query design, then switch to DataSheet view, what happens? Switching to Design view and looking at the grid, are there any obvious errors? Does removing some of the criteria get the results it should? This is one quick way to narrow the problem down, and you should quickly find the error.

If not, using the Design Query, design a new query which exactly mimics your problem. Enter the criteria that you are using in the various controls on your form. Switch to DataSheet view to be sure you got it right. Switch to SQL view and compare the generated SQL statement to the Debug.Print you inserted in line 12.

[woodey2002]

Hi
thanks for the responce.

I figured is out it was finally with some advice from my friend.

qualCode a numeric value in the table (the data type) so, i don't want quotes around the value.

Thanks so much for all the assistance.

All the best and thanks again.

Regards

JAMES.