By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,192 Members | 1,683 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,192 IT Pros & Developers. It's quick & easy.

Multiple List boxes using QBF

P: 5
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?
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearchCriteria_Click()
  2.     Dim strWhere As String
  3.     Dim lngLen As Long
  4.     Dim varItem As Variant
  5.  
  6.     strWhere = "" ' Main filter
  7.  
  8.  
  9.     'Gender Search
  10.     If Not IsNull(Me.cboGenderSearch) Then
  11.        strWhere = strWhere & " ([tblCandidatesDetails].[Gender] = '" & Me.cboGenderSearch & "') AND "
  12.     End If
  13.  
  14.      'Nationality Search
  15.     If Not IsNull(Me.cboNationalitySearch) Then
  16.        strWhere = strWhere & " ([tblCandidatesDetails].[Nationality] = '" & Me.cboNationalitySearch & "') AND "
  17.     End If
  18.  
  19.       'Academic Level Search
  20.     If Not IsNull(Me.cboAcademicLevelSearch) Then
  21.        strWhere = strWhere & " ([tblCandidatesDetails].[AcademicLevel] = '" & Me.cboAcademicLevelSearch & "') AND "
  22.     End If
  23.  
  24.      'Mother Tongue Search
  25.     If Not IsNull(Me.cboMotherTongue) Then
  26.        strWhere = strWhere & " ([tblCandidatesDetails].[MotherTongue] = '" & Me.cboMotherTongue & "') AND "
  27.     End If
  28.  
  29.      'Military Area Search
  30.     If Not IsNull(Me.cboMilitaryAreaInvolved) Then
  31.        strWhere = strWhere & " ([tblCandidatesDetails].[WhatMilitaryareaInvolvedin] = '" & Me.cboMilitaryAreaInvolved & "') AND "
  32.     End If
  33.  
  34.      'Police Rank Search
  35.     If Not IsNull(Me.cboPoliceRank) Then
  36.        strWhere = strWhere & " ([tblCandidatesDetails].[PoliceRank] = '" & Me.cboPoliceRank & "') AND "
  37.     End If
  38.  
  39.      'Do they need military exp Check box Search
  40.     If Me.chkDotheyhaveaMilitaryBackground = -1 Then
  41.       strWhere = strWhere & "  [tblCandidatesDetails!DotheyhaveaMilitaryBackground] = " & Me.chkDotheyhaveaMilitaryBackground
  42.     End If
  43.  
  44.  
  45.         '''''''''''''''Below is where the problems are'''''''''''''''''''''''''
  46.  
  47.  
  48.         ' Check for SpokenLanguages in multiselect list
  49.  
  50.     If Not IsNull(Me.lstSpokenLang.Column(0)) Then
  51.     For Each varItem In Me.lstSpokenLang.ItemsSelected
  52.         strWhere = strWhere & " ([tblCandidatesDetails!SpokenLanguages] = """ & Me.lstSpokenLang.ItemData(varItem) & """) And "
  53.         Next
  54.     End If
  55.  
  56.           ' Check for Written Languages in multiselect list
  57.  
  58.     If Not IsNull(Me.lstWrittenlang.Column(0)) Then
  59.     For Each varItem In Me.lstWrittenlang.ItemsSelected
  60.         strWhere = strWhere & " ([tblCandidatesDetails!WrittenLanguages] = """ & Me.lstWrittenlang.ItemData(varItem) & """) And "
  61.  
  62.     Next
  63.     End If
  64.  
  65.            ' Check for Professional Experience in multiselect list
  66.  
  67.     If Not IsNull(Me.lstProfessionalExpSearch.Column(0)) Then
  68.     For Each varItem In Me.lstProfessionalExpSearch.ItemsSelected
  69.         strWhere = strWhere & " ([tblCandidatesDetails!ProfessionalExperienceBackground] = """ & Me.lstProfessionalExpSearch.ItemData(varItem) & """) And "
  70.         MsgBox Me.lstSpokenLang.ItemData(varItem)
  71.     Next
  72.     End If                
  73.  '***********************************************************************
  74.     'Chop off the trailing " AND ", and use the string as the form's Filter.
  75.     '***********************************************************************
  76.     'See if the string has more than 5 characters (a trailng " AND ") to remove.
  77.     lngLen = Len(strWhere) - 5
  78.         If lngLen <= 0 Then     'Nah: there was nothing in the string.
  79.             MsgBox "There has been No Crtieria selected", vbOKOnly, "Search Error"
  80.         Else                    'Yep: there is something there, so remove the " AND " at the end.
  81.             strWhere = Left$(strWhere, lngLen)
  82.  
  83.         'Finally, apply the string as the form's Filter.
  84.         DoCmd.OpenForm "frmCriteriaSearchResults", acNormal, , WhereCondition:=strWhere
  85.  
  86.     End If
  87.  End Sub
Sep 29 '09 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 1,287
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.
Expand|Select|Wrap|Line Numbers
  1. i = 0
  2. While i < myList.ListCount
  3.   If myList.selected(i) Then
  4.     value = myList.ItemData(i)
  5.     ...
  6.   End If
  7.   i = i + 1
  8. End While
Sep 29 '09 #2

NeoPa
Expert Mod 15k+
P: 31,761
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.
Sep 29 '09 #3

This discussion thread is closed

Replies have been disabled for this discussion.