Hi there i have bit of an issue. i haver this database that is purely used for searching records under certain criteria. This criteria is chosen by the user on a form using list boxes and combo boxes. So when the criteria is chosen the user selects a click command button and then the results are displayed on another form. I have successfully created the code for the combo boxes on the form.The list boxes are are the problem which are set to 'multi select' for example spoken languages, so when the code is run it looks up all results that have a minimum of speaking those langauges. lastly The data for those are stored in lookup tables. I have attatched the database if any of you guys want to have a look, and also i have attatched my code. I know in my database that there are no relationships just the main table. This is due to the database only being used for one purpose the search and a candidate will only ever have one cv and record. Thanks for your efforts and time, i greatly appreciate it - Option Compare Database
-
Option Explicit
-
-
Private Sub cmdSearchCriteria_Click()
-
Dim strWhere As String
-
Dim lngLen As Long
-
Dim VarItem As Variant
-
-
-
'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
-
-
-
'Spoken Languages
-
For Each VarItem In Me.lstSpokenLang.ItemsSelected
-
strWhere = strWhere & " [tblCandidatesDetails!SpokenLanguages].[strWhere] = " & _
-
Me.lstSpokenLang.ItemData(VarItem) & " AND "
-
Next
-
'Written languages
-
For Each VarItem In Me.lstWrittenlang.ItemsSelected
-
strWhere = strWhere & " [tblCandidatesDetails!WrittenLanguages].[strWhere] = " & _
-
Me.lstWrittenlang.ItemData(VarItem) & " AND "
-
Next
-
'Professional Experience
-
For Each VarItem In Me.lstProfessionalExpSearch.ItemsSelected
-
strWhere = strWhere & " [tblCandidatesDetails!ProfessionalExperienceBackground].[strWhere] = " & _
-
Me.lstProfessionalExpSearch.ItemData(VarItem) & " AND "
-
Next[/b]
-
-
'***********************************************************************
-
'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 "No criteria, Do you want to view all candidates", vbYesNo
-
-
-
-
Else 'Yep: there is something there, so remove the " AND " at the end.
-
strWhere = Left$(strWhere, lngLen)
-
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
-
'Debug.Print strWhere
-
-
'Finally, apply the string as the form's Filter.
-
DoCmd.OpenForm "frmCriteriaSearchResults", acNormal, , WhereCondition:=strWhere
-
-
End If
-
End Sub
8 3326 @hollinshead
Hello hollinshead, and welcome to Bytes. Your Table Structure (tblCandidatesDetails) has broken one of the Cardinal Rules for Database Design, namely all Columns should be Atomic and contain a single value. You have several Columns in this Table that contain Multiple Values delimited by Semi-Colons(;). I ' strongly' suggest that you carefully read the following Link: http://bytes.com/topic/access/insigh...ble-structures
The above being stated, I'll reluctantly show you how you can Filter for Multiple Criteria within such a Delimited Value Field, but only in the hopes that you will modify your Database Structure somewhere down the line. The demo will relate to values stored in the [SpokenLanguages] Field of tblCandidatesDetails, several of which can be selected within the lstSpokenLang List Box. - You must set the Multiselect Property of lstSpokenLang to either Simple or Extended in order to allow for Multiple Selections.
- Replace your code segment within cmdSearchCriteria_Click() with the following to see how you can Filter for Multiple Languages only. Make the necessary adjustments for other similar List Boxes for which you wish to set Criteria.
- Dim strWhere As String
-
Dim VarItem As Variant
-
-
strWhere = ""
-
-
For Each VarItem In Me.lstSpokenLang.ItemsSelected
-
strWhere = strWhere & "Instr([tblCandidatesDetails!SpokenLanguages], '" & _
-
Me.lstSpokenLang.ItemData(VarItem) & "')>0" & " AND "
-
Next
-
-
strWhere = Left$(strWhere, Len(strWhere) - 5)
-
-
DoCmd.OpenForm "frmCriteriaSearchResults", acNormal, , WhereCondition:=strWhere
P.S. - As long as you maintain this Table Structure, I guarantee that you will run into more, similar problems down the line.
NeoPa 32,554
Expert Mod 16PB
In your code you have various examples of setting WHERE clause details where the field reference in the string is specified incorrectly.
As an example, your line #37 - strWhere = strWhere & " ([tblCandidatesDetails!PoliceRank] = """ & Me.cboPoliceRank & """) AND "
should instead be : - strWhere = strWhere & " ([tblCandidatesDetails].[PoliceRank] = '" & Me.cboPoliceRank & "') AND "
Notice I've also changed the type of quote used. See Quotes (') and Double-Quotes (") - Where and When to use them.
Hi everyone thanks for your efforts and support on this. I have done as requested and ammended the whole structure of the database so where there were fields with multiple values, however i have hit a wall as to how i am going to get to the stage where i would like. As you saw before that i had made it simplistic to the user so that say for example they were entering in a new candidate into the system and wanted to store that candidate as having more than one professional qualification. How could i do this? Could this be put together so that the user chooses the relevant options for a single combo box or list obx, where they can select more than one. And then that automatically puts into the tables the relevant data in single normalised form. I might not be clear what i mean. I have attatched the ammended database so that you can see if you want the relationships now. Im just trying to work out how i can now create the same system as before but using the method that you have advised. obviously my forms and things at the mins are in a bit of a state as they are still set to the old way of doing things, but i was just wondering if you could advise how i am to go about ammending the data, so that the forms worked like they did before. I think ill just have to use loads of VLookups, but not sure how i can collate them all together to show in one combo box for example like before.
You know you said that it was a problem to have multiple records in one field. Is it the same issue when your using a DLOOKUP and you enable the multi select option. I have created the database so that it is normalised i am jsut trying to link the tables all together and create the forms needed to enter details in.
With the below code that was provided. It results in no errors highlighted, however it opens the results form and it is showing no results where should be. Any ideas? Thanks for your time - For Each VarItem In Me.lstSpokenLang.ItemsSelected
-
strWhere = strWhere & "Instr([tblCandidatesDetails!SpokenLanguages], '" & _
-
Me.lstSpokenLang.ItemData(VarItem) & "')>0" & " AND "
-
Next
NeoPa 32,554
Expert Mod 16PB
You've been commendably brief in your posted code, however it may help to get a bit more context. I see nothing wrong with it immediately. There's no code posted to show how you call the form though.
When looking at your code, it may also be an idea to display (and maybe even post) the contents of your string strWhere. For some tips on debugging issues (such as this) see Debugging in VBA.
PS. Please notice the comment about the CODE tags. I'm afraid I'll need to make it official next time.
@hollinshead
I've downloaded the Attachment and it appears as though it will take a lot of work just to get the Base Code operational. If you are willing to be patient, I'll work on it from time to time, but I will not Restructure/Normalize the Database. If this is acceptable, just let me know.
NeoPa 32,554
Expert Mod 16PB
It seems the OP may have given up on this thread as they've reposted their problem elsewhere ( Multiple List boxes using QBF).
This stands as a warning to any member considering expending any effort on their behalf.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Robert Neville |
last post by:
The solution to my dilemma seems straight-forward, yet my mind has not
been forthcoming with a direct route. My Project form has a tab
control with multiple sub-forms; these distinct sub-forms...
|
by: Eskil |
last post by:
Hi
I have a form that supplies my query with information on two different
variables. The form uses a lookup to display a list of 5 different
customer types and 5 different types of...
|
by: RBohannon |
last post by:
I'm using Access 2000.
I currently have a report being generated using the results of a query
by form. The form used for this query is an unbound form,
frmListDialog. frmListDialog contains...
|
by: starace |
last post by:
I have designed a form that has 5 different list boxes where the
selections within each are used as criteria in building a dynamic
query. Some boxes are set for multiple selections but these list...
|
by: ljungers |
last post by:
Have Form-1 with 3 text boxes and 1 command button. With any of the 3 boxes filled out and button is clicked, a Macro is performed that Opens a Query that has a WHERE clause that uses the 3 test...
|
by: gerbski |
last post by:
Hi all,
I am relatively new to ADO, but up to now I got things working the way
I wanted. But now I've run into somethng really annoying.
I am working in MS Access. I am using an Access...
|
by: ljungers |
last post by:
Have a couple of conditions going on that I can't seem to get working correctly.
Have 1 Form only with 3 text boxes that are used in a query, when a command button is clicked using 'Event...
|
by: commodityintelligence |
last post by:
Greetings,
I am merging a series of different tables into one query to export decision-making information. I have some architecture issues I need to ask for help on. I have no programming...
|
by: dizzydangler |
last post by:
Hi all,
I am a new Access user and just starting to get my head around some of the basic concepts, so please take it easy on me :) My company has been managing client records on excel, and I’m in...
|
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...
|
by: MeoLessi9 |
last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
|
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...
|
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 =...
|
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...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
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...
|
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)...
| |