473,837 Members | 1,531 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Query by form using list boxes

5 New Member
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
Expand|Select|Wrap|Line Numbers
  1.  Option Compare Database
  2.  Option Explicit
  3.  
  4. Private Sub cmdSearchCriteria_Click()
  5.     Dim strWhere As String
  6.     Dim lngLen As Long
  7.     Dim VarItem As Variant
  8.  
  9.  
  10.     'Gender Search
  11.     If Not IsNull(Me.cboGenderSearch) Then
  12.        strWhere = strWhere & " ([tblCandidatesDetails!Gender] = """ & Me.cboGenderSearch & """) AND "
  13.     End If
  14.  
  15.      'Nationality Search
  16.     If Not IsNull(Me.cboNationalitySearch) Then
  17.        strWhere = strWhere & " ([tblCandidatesDetails!Nationality] = """ & Me.cboNationalitySearch & """) AND "
  18.     End If
  19.  
  20.       'Academic Level Search
  21.     If Not IsNull(Me.cboAcademicLevelSearch) Then
  22.        strWhere = strWhere & " ([tblCandidatesDetails!AcademicLevel] = """ & Me.cboAcademicLevelSearch & """) AND "
  23.     End If
  24.  
  25.      'Mother Tongue Search
  26.     If Not IsNull(Me.cboMotherTongue) Then
  27.        strWhere = strWhere & " ([tblCandidatesDetails!MotherTongue] = """ & Me.cboMotherTongue & """) AND "
  28.     End If
  29.  
  30.      'Military Area Search
  31.     If Not IsNull(Me.cboMilitaryAreaInvolved) Then
  32.        strWhere = strWhere & " ([tblCandidatesDetails!WhatMilitaryareaInvolvedin] = """ & Me.cboMilitaryAreaInvolved & """) AND "
  33.     End If
  34.  
  35.      'Police Rank Search
  36.     If Not IsNull(Me.cboPoliceRank) Then
  37.        strWhere = strWhere & " ([tblCandidatesDetails!PoliceRank] = """ & Me.cboPoliceRank & """) AND "
  38.     End If
  39.  
  40.  
  41.  'Spoken Languages
  42.     For Each VarItem In Me.lstSpokenLang.ItemsSelected
  43.             strWhere = strWhere & " [tblCandidatesDetails!SpokenLanguages].[strWhere] = " & _
  44.                     Me.lstSpokenLang.ItemData(VarItem) & " AND "
  45.     Next
  46.     'Written languages
  47.     For Each VarItem In Me.lstWrittenlang.ItemsSelected
  48.             strWhere = strWhere & " [tblCandidatesDetails!WrittenLanguages].[strWhere] = " & _
  49.                     Me.lstWrittenlang.ItemData(VarItem) & " AND "
  50.     Next
  51.     'Professional Experience
  52.     For Each VarItem In Me.lstProfessionalExpSearch.ItemsSelected
  53.             strWhere = strWhere & " [tblCandidatesDetails!ProfessionalExperienceBackground].[strWhere] = " & _
  54.                     Me.lstProfessionalExpSearch.ItemData(VarItem) & " AND "
  55.     Next[/b]
  56.  
  57.  '***********************************************************************
  58.     'Chop off the trailing " AND ", and use the string as the form's Filter.
  59.     '***********************************************************************
  60.     'See if the string has more than 5 characters (a trailng " AND ") to remove.
  61.     lngLen = Len(strWhere) - 5
  62.     If lngLen <= 0 Then     'Nah: there was nothing in the string.
  63.         MsgBox "No criteria, Do you want to view all candidates", vbYesNo
  64.  
  65.  
  66.  
  67.     Else                    'Yep: there is something there, so remove the " AND " at the end.
  68.         strWhere = Left$(strWhere, lngLen)
  69.         'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
  70.         'Debug.Print strWhere
  71.  
  72.         'Finally, apply the string as the form's Filter.
  73.         DoCmd.OpenForm "frmCriteriaSearchResults", acNormal, , WhereCondition:=strWhere
  74.  
  75.     End If
  76.  End Sub
Attached Files
File Type: zip ForumExample2000DB.zip (245.1 KB, 137 views)
Sep 9 '09 #1
8 3368
ADezii
8,834 Recognized Expert Expert
@hollinshead
Hello hollinshead, and welcome to Bytes. Your Table Structure (tblCandidatesD etails) 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 tblCandidatesDe tails, several of which can be selected within the lstSpokenLang List Box.
  1. You must set the Multiselect Property of lstSpokenLang to either Simple or Extended in order to allow for Multiple Selections.
  2. Replace your code segment within cmdSearchCriter ia_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.
    Expand|Select|Wrap|Line Numbers
    1. Dim strWhere As String
    2. Dim VarItem As Variant
    3.  
    4. strWhere = ""
    5.  
    6. For Each VarItem In Me.lstSpokenLang.ItemsSelected
    7.   strWhere = strWhere & "Instr([tblCandidatesDetails!SpokenLanguages], '" & _
    8.                          Me.lstSpokenLang.ItemData(VarItem) & "')>0" & " AND "
    9. Next
    10.  
    11. strWhere = Left$(strWhere, Len(strWhere) - 5)
    12.  
    13. 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.
Sep 9 '09 #2
NeoPa
32,584 Recognized Expert Moderator MVP
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
Expand|Select|Wrap|Line Numbers
  1. strWhere = strWhere & " ([tblCandidatesDetails!PoliceRank] = """ & Me.cboPoliceRank & """) AND "
should instead be :
Expand|Select|Wrap|Line Numbers
  1. 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.
Sep 9 '09 #3
hollinshead
5 New Member
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.
Attached Files
File Type: zip Database41.zip (340.9 KB, 107 views)
Sep 24 '09 #4
hollinshead
5 New Member
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.
Sep 24 '09 #5
hollinshead
5 New Member
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
Expand|Select|Wrap|Line Numbers
  1. For Each VarItem In Me.lstSpokenLang.ItemsSelected 
  2.   strWhere = strWhere & "Instr([tblCandidatesDetails!SpokenLanguages], '" & _ 
  3.                          Me.lstSpokenLang.ItemData(VarItem) & "')>0" & " AND " 
  4. Next
Sep 29 '09 #6
NeoPa
32,584 Recognized Expert Moderator MVP
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.
Sep 29 '09 #7
ADezii
8,834 Recognized Expert Expert
@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.
Sep 29 '09 #8
NeoPa
32,584 Recognized Expert Moderator MVP
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.
Sep 29 '09 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

1
2717
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 relate addresses (multiple addresses); companies, contacts, and tasks to each project (one to many). My challenge lies with the task sub-form which links to the Project form through ProjID. The task record links back to the respective master...
1
1688
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 meetings/activities in two different combo boxes. Now, the values of form variables go into a query upon which a report is generated. By using those two variables my form can produce 25 different reports. But the report turns out empty on any combination...
4
2618
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 two unbound text boxes, txtExamTitle and txtExamNumber. The user enters either the exam title in txtExamTitle or the exam number in txtExamNumber as a parameter for the query. This is currently working fine, however because the exam titles are...
0
3520
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 boxes do not necessarily need to have a selection made to be used in the dynamic query. In essence the form can have selections made in all or none of its list boxes to form the dynamic query I am looking to get some feedback in reference to...
7
4528
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 boxes of data from form-1. 1) How do I cause the SQL command window not to show for that SQL command? The query results is placed in a ListBox on Form-2 and desired rows in the list box are selected/clicked, followed by a click on a button that...
6
2781
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 frontend separately from a backend. The tables from the backend database are linked in the frontend database. In the frontend there is a Form with a listbox in it. The listbox rowsource is a query that selects all the records from a (linked)
3
2232
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 Procedure'. The VBA Sub that runs (code below) checks to see if 1 or more text boxes had something entered, and if OK the list box row source should be poplulated with a query results. Only need 1 or more of the 3 boxes filled in. For some reason it wants...
1
1876
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 training. I will explain my structure before asking my questions. I have data from a table (updated monthly by adding the most current month’s data) that is evaluated by certain arithmetic, given an adjective to describe it, and then being exported...
4
9506
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 the process of migrating the data over to Access 2007 (Windows XP), kind of learning as I go. I’ve managed to import the client records into a single table, and set up a “single view” form that streamlines how we input new client data. Now I’m...
0
9682
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10870
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10621
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9392
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6998
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5668
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5845
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4038
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3123
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.