Multiple List boxes using QBF

[hollinshead]

hi there, i have been having this issue for quite some time now and i cant seem to get my head around it. I am trying to create a database for candidates CV's and covering letters. basically the data that is stored is made up of qualifications and areas of business that candidate is involved etc.

My problem is with the query by form where i have a range of combo boxes and list boxes. The combo boxes are working fine, no problem however the multi select list boxes are the problem. I keep getting an error saying 'Data type Mismatch in criteria Expression'.

The structure of my database some would say is not the best way to go about it, as it is not fully normalised. However ive found that if i want to keep the same layout, displaying all the candidates data in list boxes and combo boxes then and using these features on the search form, then i am stuck with it.

The code i have used in the command button to start the filter process is displayed below. thanks for your time and efforts. Anyone got any ideas as to how i could get this resolved?

Private Sub cmdSearchCriteria_Click()
    Dim strWhere As String
    Dim lngLen As Long
    Dim varItem As Variant
 
    strWhere = "" ' Main filter
 
 
    'Gender Search
    If Not IsNull(Me.cboGenderSearch) Then
       strWhere = strWhere & " ([tblCandidatesDetails].[Gender] = '" & Me.cboGenderSearch & "') AND "
    End If
 
     'Nationality Search
    If Not IsNull(Me.cboNationalitySearch) Then
       strWhere = strWhere & " ([tblCandidatesDetails].[Nationality] = '" & Me.cboNationalitySearch & "') AND "
    End If
 
      'Academic Level Search
    If Not IsNull(Me.cboAcademicLevelSearch) Then
       strWhere = strWhere & " ([tblCandidatesDetails].[AcademicLevel] = '" & Me.cboAcademicLevelSearch & "') AND "
    End If
 
     'Mother Tongue Search
    If Not IsNull(Me.cboMotherTongue) Then
       strWhere = strWhere & " ([tblCandidatesDetails].[MotherTongue] = '" & Me.cboMotherTongue & "') AND "
    End If
 
     'Military Area Search
    If Not IsNull(Me.cboMilitaryAreaInvolved) Then
       strWhere = strWhere & " ([tblCandidatesDetails].[WhatMilitaryareaInvolvedin] = '" & Me.cboMilitaryAreaInvolved & "') AND "
    End If
 
     'Police Rank Search
    If Not IsNull(Me.cboPoliceRank) Then
       strWhere = strWhere & " ([tblCandidatesDetails].[PoliceRank] = '" & Me.cboPoliceRank & "') AND "
    End If
 
     'Do they need military exp Check box Search
    If Me.chkDotheyhaveaMilitaryBackground = -1 Then
      strWhere = strWhere & "  [tblCandidatesDetails!DotheyhaveaMilitaryBackground] = " & Me.chkDotheyhaveaMilitaryBackground
    End If
 
 
        '''''''''''''''Below is where the problems are'''''''''''''''''''''''''
 
 
        ' Check for SpokenLanguages in multiselect list
 
    If Not IsNull(Me.lstSpokenLang.Column(0)) Then
    For Each varItem In Me.lstSpokenLang.ItemsSelected
        strWhere = strWhere & " ([tblCandidatesDetails!SpokenLanguages] = """ & Me.lstSpokenLang.ItemData(varItem) & """) And "
        Next
    End If
 
          ' Check for Written Languages in multiselect list
 
    If Not IsNull(Me.lstWrittenlang.Column(0)) Then
    For Each varItem In Me.lstWrittenlang.ItemsSelected
        strWhere = strWhere & " ([tblCandidatesDetails!WrittenLanguages] = """ & Me.lstWrittenlang.ItemData(varItem) & """) And "
 
    Next
    End If
 
           ' Check for Professional Experience in multiselect list
 
    If Not IsNull(Me.lstProfessionalExpSearch.Column(0)) Then
    For Each varItem In Me.lstProfessionalExpSearch.ItemsSelected
        strWhere = strWhere & " ([tblCandidatesDetails!ProfessionalExperienceBackground] = """ & Me.lstProfessionalExpSearch.ItemData(varItem) & """) And "
        MsgBox Me.lstSpokenLang.ItemData(varItem)
    Next
    End If                
 '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
        If lngLen <= 0 Then     'Nah: there was nothing in the string.
            MsgBox "There has been No Crtieria selected", vbOKOnly, "Search Error"
        Else                    'Yep: there is something there, so remove the " AND " at the end.
            strWhere = Left$(strWhere, lngLen)
 
        'Finally, apply the string as the form's Filter.
        DoCmd.OpenForm "frmCriteriaSearchResults", acNormal, , WhereCondition:=strWhere
 
    End If
 End Sub

[ChipR]

The problem is the .ItemData(varItem). varItem is not a valid index into the collection. You would just need to access the string value of varItem. If you want a quick fix, this works, but it goes through each element of the list.

i = 0
While i < myList.ListCount
  If myList.selected(i) Then
    value = myList.ItemData(i)
    ...
  End If
  i = i + 1
End While

[NeoPa]

As this thread seems to be a reposting of an earlier thread (Query by form using list boxes) that is still active and members are still spending time trying to help you on I will lock this now. This is very much against the rules and not appreciated in the least.