473,287 Members | 1,418 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Multiple List boxes using QBF

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
2 2997
ChipR
1,287 Expert 1GB
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
32,554 Expert Mod 16PB
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: james.dixon | last post by:
Hi I was wondering if anyone else had had this problem before (can't find anything on the web about it). I have three select elements (list boxes - from here on I'll refer to them as 'the...
1
by: jeffgeorge | last post by:
Trying to create multiple acct reports based on the selection in a list box. I've set the list box for multiple selections, and in the report data source, I have a SQL statement which is reading...
7
by: Siv | last post by:
Hi, I have an MDI application that uses a generic "ShowPage" routine in a module that is called when I want to display a child form. The basic idea is that in the module I have declared each form...
1
by: Rkwarner2 | last post by:
Hello everyone. This is my very first post in this forum. I'm taking my first Visual Basic.Net Class and am wanting to try some new things for one of my homework assignments. The assignment is to...
4
neo008
by: neo008 | last post by:
Hi all, Finally gave up and putting it here. I am new to visual basic stucked up with an error- Run time errors.'-2147217887 (8004021)': Multiple-step operation generated errors. check each...
6
by: Dave | last post by:
On my form I have combo boxes. These combo boxes, after updating them, populate respective listboxes that are located below the combo boxes on the same form. I am trying to use a "generate...
12
by: micarl | last post by:
How would i print a report based on criteria selected from several Combo Boxes as well as multiple Multi Select List Boxes, that are located on the same form? I can get one Multi List Box, just...
6
by: woodey2002 | last post by:
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...
1
by: woodey2002 | last post by:
Hi Everyone and many thanks for your time.. I am trying to begin access and a bit of VBA i am enjoying it but I have a annoying problem I just can’t get any where on. My databse mostly includes...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.