Queries :: Cascading Combo Box - Filter By Field?
Mar 16, 2013
I want to create a cascading combo box - so far so simple, but in all the tutorials I can find the options in the second combo box are exclusive to the first e.g. combo box 1 selects "state", combo box 2 then displays "city". Each city can only be in one state.
In my scenario each city is in several different states - and so far I'm failing to come up with any sort of strategy as to how to deal with this. I tried giving each state a "yes/no" formatted field in the "city" table, figuring if I could use the first combo box to point access to the right field in the "city" table I could filter those records by "WHERE [fieldnominatedbycombobox1] =True" - but I don't know how to make a one combo box determine which field a subsequent combo box filters by.
View Replies
ADVERTISEMENT
Jun 3, 2013
How to properly use 3 cascading combo boxes.
I have them set up and they work, to some degree.
I have three boxes Type/Sub-Type/Item. They all filter down to the next one, the problem is, when I get to the 3rd box, it filters ONLY from the 2nd box and loses the filter from the 1st. What do I need to do to get it to maintain the filter from the first box and then ADD the filter from the second box when populating the 3rd box?
View 2 Replies
View Related
Jul 31, 2014
I'm trying to use a cascading combo box to filter the options that can be entered into a second combo box.
In frmBabies, a Mode of Delivery is selected from 8 options. 4 of these are "normal" deliveries and 4 are caesarean sections.
A second combo box selects the Indication for Operative Delivery. The table tblIndOpDel contains options for normal as well as caesareans. I would like to filter the indications so that the user cannot enter an indication for normal delivery for a caesarean section.
Both tblIndOpDel and tblDelMode have a foreign key to tblDelModeCat, which categories deliveries as normal or caesarean.
I'm stuck on how to proceed from here. Sample database attached.
View 2 Replies
View Related
Jan 31, 2014
I have 2 unbound combo box's on a form 1 called cboclient1 and the another called cboclient2. would like to be able to filter field name client name twice by cboclient1 and cboclient2 here my sql from my query
Code:
WHERE (((Assets.Client)=Forms![report gen]!cboclient1)) Or (((Forms![report gen]!Cboclient1) Is Null));
This works perfectly for cboclient1 problem comes when I try and add cboclient2
I have tried
Code:
WHERE (((Assets.Client)=Forms![report gen]!cboclient1)) Or (((Forms![report gen]!Cboclient1) Is Null))AND(((Assets.Client)=Forms![report gen]!cboclient2)) Or (((Forms![report gen]!Cboclient2) Is Null));[/
This doesn't work at all....
View 2 Replies
View Related
Apr 25, 2008
hi,
I have a db which uses the data taken from cboInstructors which is used in a query and a produces a list in cboExpertise. This procedure is repeated from cboInstructors for cboAssessors and then cboIVName.
This subform then shows all the modules each student have taken, who taught it, who assessed and who IV'd it. The problem is when one selection is made all the subsequent choices are the same as the cbo above it, i.e. the queries do not run again when a diferent selection is made in cboInstuctors.
I hope this clear.
Help please.
Iain :eek::confused:
View 7 Replies
View Related
Dec 18, 2013
I am working on a database where law clerks input the tasks that they accomplish each day. They use a form called "TaskDetails" to input all of the information related to their daily tasks via combo boxes. The problem I have is: often times the clerks work on the same task more than once and I would like them to be able to continue tasks already in progress - using notes to indicate what particular portion they completed that day.
In order to do this I would like to have a combo box that is based on a query of all of the tasks the clerks have completed. When they select a Task Name from the combo box, I would like all other combo boxes on the page to auto-populate based on the selection.The query that my combo box is based on includes all relevant fields.
When I click on the "Task Name" combo box I am presented with all of the records related to tasks. The fields/columns are divisionofwork, subcategory, workassignedby. I have combo boxes for all of these fields on the same page, bound to the table of tasks. Ideally, a clerk makes a selection from the "Task Name" combo box, and the subsequent 3 combo boxes will reference their relevant column in this selection and auto-populate.
View 1 Replies
View Related
May 15, 2013
Currently, I have a form with a combobox that lists all of the names of the tables inside my database. Depending on the table selected in the first combobox, I would like to have another combobox which allows the user to choose from the field names inside that table.
View 3 Replies
View Related
Nov 27, 2013
I have a form with cascading combo boxes pulling from a table. They work perfectly, no worries. My problem now is if I do not enter information in every combo box (i.e. only two out of four combo boxes), how can I still run the query and get the appropriate information?
For example (these are my combo box titles in order):
Product
Type
Customer
Contract #
I don't want to necessarily look by Contract # all the time, but sometimes just by the general Product and Type to get a larger view. How do I set up expressions/criteria in my query to accurately produce that information? Right now it just produces a blank query table if I don't fill out all the boxes.
I've tried a couple of expressions with "isnull" criteria, but I must be doing it incorrectly.
View 2 Replies
View Related
Aug 7, 2013
I've been able to make a normalized relational database with cascading combo boxes, etc.
How to validate a field? The format needs to be according to the following:
<0 AND <=9999 [Optional: followed by a capital letter]
Examples of Valid Data:
7
4A
354
8756G
View 3 Replies
View Related
Mar 15, 2013
Here is what I have in my Query
IIf([Forms]![Report Form]![Combo56]=0,1 or 3,[Forms]![Report Form]![Combo56])
in the combo box I have it setup like this
Yes = 1
No = 3
All = 0
it is pulling for a SQL database and what I am looking to do is if they want them all select 1 and 3 but when I save the Query is changes it to this and doesn't work.
IIf([Forms]![Report Form]![Combo56]=0,([BigAssQuery MTD].[Calc_ID])=1 Or ([BigAssQuery MTD].[Calc_ID])=3,[Forms]![Report Form]![Combo56])
View 2 Replies
View Related
Nov 18, 2013
On my database, I have 2 forms. One is to enter data into a Master table, and the other is to specify search terms for a query, and I'm having a problem with the search form.
I've got 2 combo boxes, a tick box and some text boxes which I use to specify criteria for the query. All of the text boxes and the tick box work fine, however the combo boxes are giving me trouble.
I'm using the critera Like "*" & Form![SearchForm]![Combo/Text/Whatever1] & "*" for the text boxes and tick box and this worked fine. However, no matter what way I try to use this, it doesn't work for the combo box. I've tried manipulating the Like function as much as possible, with and without the wildcards, the & signs, everything.
What happens is that either they do not filter out results at all and don't affect the query, or they bring up a blank query if I leave the combo box blank.
View 4 Replies
View Related
Jan 23, 2014
I have some code in which I enter a startdate and a enddate into textbox's and a combo box where I enter a client name when I run the code it will show all record's generated with in the date range and same client name set in the combo box "cboclient" what I would like to be able to do is have another combo box call cboclient2 which will allow me to enter another client name so then when the report opens it will show me with in the date range set in startdate and enddate and filter the client field by cboclient and cboclient2 so for e.g.
startdate
01/01/14
enddate
11/01/14
cboclient
peter
cboclient2
steve
Run code show all record in date range and with client name peter and steve
Here's the code I have
Private Sub cmdPreview_Click()
'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it working.
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "#mm/dd/yyyy#" 'Do NOT change it to match your local settings.
[Code] ....
View 7 Replies
View Related
Feb 21, 2008
If anybody could shine some light it would be much appreciated.
I have 3 tables:
Task table---*Task ID, Task(txt), Start(date/Time), finish(date/Time)
Tasking table---*TaskingID, TaskID, EmployeeID
employee table---*EmployeeID, Name(txt)
The employee and task table are joined to the tasking table. This is so i can have a task which has many employees and an employee with many tasks. What i'm try to do is create a form which starts a task with a subform to add employees, but with combo box which filters employees with tasks which over-lap the task in focus(start and finish fields) but still showing employees which do not yet have any tasks. the start and finish fields are hh:nn dd/mm/yy.
If anybody can help i would be very grateful
Jim
View 3 Replies
View Related
Feb 21, 2008
If anybody could shine some light it would be much appreciated.
I have 3 tables:
Task table---*Task ID, Task(txt), Start(date/Time), finish(date/Time)
Tasking table---*TaskingID, TaskID, EmployeeID
employee table---*EmployeeID, Name(txt)
The employee and task table are joined to the tasking table. This is so i can have a task which has many employees and an employee with many tasks. What i'm try to do is create a form which starts a task with a subform to add employees, but with combo box which filters employees with tasks which over-lap the task in focus(start and finish fields) but still showing employees which do not yet have any tasks. the start and finish fields are hh:nn dd/mm/yy.
If anybody can help i would be very grateful
Jim
View 3 Replies
View Related
Feb 19, 2015
I would like to filter a data from a table using a combo box in a form.
The field I would like to filter is called ManufacturersID in MainTable and consist of text and wildcard characters [e.g., Teledyne O&G (ODI), TE Connectivity (Deutsch), etc...], so are the combo strings which are extracted from the table ManufacturersTable. It is not filtering anything. I suspect that the wildcards and spaces are the problem. I also tried to get the combo string to a text box in the form and use it in the query with no success. Additionally, I also tried Like [Forms]![FormName]![textbox] in query criteria but still not filtering...
How can I overcome this?
View 2 Replies
View Related
Jul 13, 2015
I have a form where I am trying to use 4 combo boxes(nomenclature,BPN,vendor, and reference) to filter a list box containing part numbers. The way I have it set up right now is in the listbox it is searching for each field and then in the criteria section i have [Forms]![myform]![respectedFieldsCombo].
This works when selections are made from the combo boxes but when one is blank (not being used to filter) then I assume it passes null for that value and the listbox doesn't return anything. I have tried to make it so the listbox ignores null values but im still having this problem.
For example: If i only have a selection for the vendor combo box then i want the list box to show all respective part numbers for that vendor, where nomenclature or any of the other fields are irrelevant. I also want to be able to stack these filters upon every new combo box selection.
View 7 Replies
View Related
Jun 15, 2013
I want to filter the records in a combo box (cboSupplier) using the In() function.
On the form I have an unbound control called "intFilter". It contains the following string: 23, 58
The SQL statement for the combo box is
Code:
SELECT SupplierID, SupplierName
FROM tblSupplier
ORDER BY SupplierName;
In the criteria for field SupplierId I want to use the In() function so that the only records returned by the combo box are those in "intFilter". I have tried
Code:
In (Forms![frmSupplierReport]![intFilter]
but this does not work.
If I put: In (23, 58) in the criteria it works, but I cannot hard code it because the string in "intFilter" will change on the fly!
How would I achieve this?
View 4 Replies
View Related
Jul 1, 2013
I have a subform with the following fields:
Client name, Country, Country ID
I want to add to every row a combobox with a list of towns depending on the value in the field Country ID.how do I filter the comboboxes individually based on a value in another field on the same row?
View 1 Replies
View Related
Jan 25, 2015
I'm working on a table for work which will serve as a database of bins and the products currently in those bins, as follows:
ID Bin Amt Product
1 34 25 110001
2 33 15 200005
3 32 23 110003
etc.
Basically: employee A will use a form to update "Bin 34" (ID=1), with information (Amt, Product) until it is emptied by employee B and cleared using a separate form. No new records will be made or deleted, just the adjacent fields (Amt, Product) cleared and filled in and cleared again over and over.
What I'm trying to figure out is, when employee A clicks on the combo box on his/her form to select a Bin and enter information, how can I filter what is listed in the combo box to display only those bins which have no values under Amt & Product. The real-world risk of overwriting a bins fields before it has been cleared by employee B (who physically empties the bin) can be extremely high monetarily so I am trying to reduce that risk by eliminating it from the combo box until employee B has cleared those fields.
View 1 Replies
View Related
Dec 7, 2005
Dear members,
I have a form with 4 Combo boxes bound to fields in 4 tables;
1) Oblast Name
2) District Name
3) Jamoat Name
4) Village Name
The selections work fine. Cascade works from Oblast to Village Name. My question is ;
Currently the ID's of the bound columns are stored in the tables instead of the corresponding data. For example for the Oblast name; I have the value "3"=" rather than "Khatlon" for District_Name, I have "223", rather than "Muminobad".
Is there way to capture the names of the respective places rather than storing ID's in the table?
Thanks in advance.
Irshad
View 9 Replies
View Related
Jul 29, 2013
I have a query which is run from a form via a command button. On this form i have a couple control sources (not sure if this is the correct term, but i have text boxes and drop down lists which the query uses in its criteria).
Everything works fine except i can't figure out how to filter my checkbox field (aka MyDeliv). I'd like to have a drop down box with 3 options: "yes", "no", and "ignore". i would like "yes" to return only records with the MyDeliv box checked, "no" to return only records with the MyDeliv box unchecked, and "ignore" to return records regardless of the MyDeliv checkbox.
View 3 Replies
View Related
Apr 11, 2006
This is driving me a little mad, and its probably something quite simple!
I am creating a form which will allow users to narrow down their search based on criteria entered into three combo boxes.
The first combo box pop's up a calendar and is populated when the date is chosen on the calendar.
The second combo box will provide a list of systems that have a record entered on that date
The third combo box will then show a list of case id's based on the system and date selected.
I can get it working when ignoring the date, but as soon as I bring the date combo into the equation I get a data mismatch error. Now I presume that this is to do with holding a date in a text field and comparing with a date/time field in the table, so I have set both to 'short date', but still get the error.
Can anyone provide any pearls of wisdom to help stop me from going insane? (that might be a bit late tho!)
View 1 Replies
View Related
Dec 12, 2006
I have a few cascading combo boxes that restricts the value of one combo box depending on what the the value of other one is.
The problem I have is that some of the data is not displayed in the combo boxes when scrolling through the records after data entry.
I have tried to re query them on the form on open event but it does not seem to help much.
Any Ideas?
thanks :)
View 9 Replies
View Related
Nov 4, 2007
Hello
I have a database (Access 2007) that has two tables, reports, queries and so far all seems to be working and going well. Quite impressed with it to be honest for my lack of skills.
However, I've run into a bit of trouble and need assistance getting a combo box to work with another combo box to produce list kind of thing.
The database is a vehicle database and I need the 'Vehicle' combo box to give me a list of vehicles. I then need the 'Model' combo box to produce the models of the car selected in the 'Vehicle' combo box.
Example - If I select 'Ford' from the 'Vehicle' combo box list I want all the models to be displayed in a list form thing in the 'Model' combo box so the user can easily select make and model of the car.
I have two tables:
CarMM
VD
The 'CarMM' table contains a list of around 1,500 records of car makes and models. First column has the 'Vehicle' make and the second field coloumn has the 'Model'.
The 'VD' table contains all other information like the charges, name, the make and model of car once selected in combo box.
I then have the form called 'VD'. In this form contains the combo boxes I want to be cascading.
Any advice on how to do this? I'm quite new to this so any help would be nice. Thanks you very much.
View 2 Replies
View Related
Jul 28, 2005
I need some help setting up my tables to allow cascading combo boxes. I need to document when a Pharmacy IV room and chemotherapy room are cleaned. Each Room requires different cleaning procedures.
Here is my table structure:
TblCleaningDuties
DutyID-Auto# (PK)
Duty - text (Example- mop floor, clean hood, wash ceiling, wash sink)
Frequency-text
TblLocation
LocationID- Auto# (PK)
Location - Text (Clean Room, Chemo Room, Anteroom)
TblCleaning
CleanID-Auto# (PK)
LocationID-(FK)
DutyID- (FK)
Date/Time
TechID
If the user selects Clean Room I don't want them to see wash sink and anything else not pertinent to the Clean Room. How do I set up the tables?
Thanks,
Jason
View 1 Replies
View Related
Jun 2, 2005
Just wondering if anyone can help Im creating two combo boxes where the first one determines the values in the second one.
The tables are made up of the following;
Line
Line number (PK)
.....
....
Main Setting
Part Number (PK)
Press Line (FK)
Now ive took the advice of previous threads but im having the following error message when im creating this;
The record source 'SELECT [Main Settings].[Part Number] FROM Main AS Settings WHERE [Main Settings].[Press Line]=[Forms]![Example]![Line]' specified on this form does not exist.
I know that this doesnt exist but im typing it correctly. Access seems to be changing my statement i.e. inserting the AS command. Any help will be much appreciated
View 3 Replies
View Related
