Filtering Combo Box Based On A Value From Another Combo Box (in Multiple Items Form)

Mar 11, 2012

Let's assume we have 3 tables:

Order_Category (Order_Category_ID, Order_Type_Name) with 2 records:
1, Minor
2, Major

Order_Type (Order_Category_ID, Order_Type) with 4 records:
1, Book
1, Pencil
2, Car
2, House

Orders (Order_Category_ID, Order_Type, value) with 2 records:
1, NULL, NULL
1, NULL, NULL
2, NULL, NULL

I want to create a Multiple Items form presenting Orders table with two Combo Boxes:

1. A combo box to select Order_Category_ID.
2. A combo box to select Order_Type. When 1 (Minor) is chosen in the first combo box it should show Book and Pencil, when 2 (Major) is chosen it should show Car and House.

Examples in the Internet show how to do it on a 'single row' forms using the RowSource property. I tried to use a query like:

SELECT Order_Type
FROM Order_Type
INNER JOIN Orders ON Order_Type.Order_Category_ID = Orders.Order_Category_ID
WHERE Order_Category_ID = [comboBoxOrderCategoryID]

But it sets same values for all records in the Multiple Items form and it should return different values in each rows based on value in the first combo box (Order_Category_ID).

View Replies


ADVERTISEMENT

Design Problem - Filtering Combo Box Items Based On Related Table Field

Apr 19, 2007

Hi im stuck on filtering a combo box (i am using an sql query to populate a combo box with a filtered selection, this is in Access but im after some design sanity checking). I have users who can be assigned a category. Jobs can be assigned a category and also have a list of people working on that job (in the JobDetails table). I have the following tables:

Categories Table:
CategoryID (PK)
Description


Users Table:
UserID (PK)
Username
CategoryID (FK on Categories.CategoryID)


Jobs Table:
JobID (PK)
CategoryID (FK on Categories.CategoryID)


JobDetails Table:
JobID (FK on Jobs.JobID)
UserID (FK on Users.UserID)

Then in the job details when listing users for a job (many users can be for one job) i would like to only show the users which have the same category as the jobs category. Is this possible? i tried the SQL below for the lookup column field JobDetails.UserID but it doesnt work:

SELECT Users.ID, Users.Username, Users.CategoryID
FROM Users, Jobs
WHERE (((Users.CategoryID)=[Jobs].[CategoryID]));

All the tables are linked with relationships but my SQL isnt so hot!
Any ideas as to how i would do this and get it working?

Even if it can be done, is this even recommended? I can see funny conditions happening if the job details category changes or the users category changes then even if they are existing in the job details list they will not be shown? Even so, i would be interested in the above to know how it is done (if possible).

Thanks in advance,

Chris

View 2 Replies View Related

Filtering Out Items In Combo Boxes

Aug 3, 2006

Need some help here....

I am designing a database to keep track of workers for a haunted house. I have a Roster table, a table of all the nights we are open, a table of Spots in the house, and a table to record who works what nights and what spot they are in. This table has a Room combo box and a Spot combo box. The Room combo has a Row Source of SELECT DISTINCT SpotsAll.Room FROM SpotsAll ORDER BY SpotsAll.Room; The Spot combo is then populated with VB code all the Spots that are in that room. That works fine. This is what I'm trying to accomplish: When a Spot is assigned to a Worker for that night, I want that spot to no longer be available in that list FOR THAT PARTICULAR NIGHT. So, lets say Joe Somebody works in Spot 1 (out of 4 lets say) of the Library Room, when we assign another person to the Library room, I don't want Spot 1 in the Spot list.

Can this be done with a query or VB code? If I'm not making sense, please let me know. I can also upload the Database I am creating so that you can play around with it and see what I mean.

(I have attached a word doc. that shows the table relationships.)


Thanks a lot ahead of time!!
ScrmingWhisprs

View 13 Replies View Related

Forms :: Filtering Combo Box Based On Another Combo Box

Jun 3, 2014

I am trying to select a value from one combo box and on the basis of this selection the other combo box show only those values which have link to the value I have selected.

View 14 Replies View Related

Tables :: Select Multiple Items In Combo Box In Form?

Mar 6, 2015

I have a form with a combo box named 'Venue'. The combo box values comes from a Table with a list of about 200 countries. As of now a user is able to select only one country. However, I want users to be able to select more than one country.

View 1 Replies View Related

Cascading Combo Boxes - Open A Form And Add Multiple Items To A Table

Mar 12, 2011

I have been trying to modify a sample database to suit what I want to do but I am getting stuck on the very first part cascading combo boxes i want to open a form and add multiple items to a table - first i want to select, from a combo box a department - then select a supplier from all the suppliers/manufacturers related to that department then select a stock item based on the description from all the items available from that supplier

I have attached the database I am using to modify and my database.

View 5 Replies View Related

Forms :: Filtering A Combo Box On A Subform From A Combo Box On A Main Form

Apr 27, 2013

I have a Suppliers database which contains a form that will allow me to place orders with Suppliers.The Main form has a combo box that allows me to select the supplier. The combo box is called SupplierID with the following:

Row source: SELECT Suppliers.SupplierID, Suppliers.CompanyName FROM Suppliers ORDER BY Suppliers.CompanyName;

The subform is called Stock Subform witha combo box called ProductID with the following:

Row source: SELECT DISTINCT Products.ProductID, Products.ProductName, Suppliers.CompanyName, Products.Discontinued FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID=Products.SupplierID WHERE (((Products.Discontinued)=0)) ORDER BY Products.ProductName;
Event Procedure - AfterUpdate: Private Sub ProductID_AfterUpdate()
On Error GoTo Err_ProductID_AfterUpdate
Dim strFilter As String
' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID

[code]..

The Link fields are done on the Purchase Order ID (PONoID).What I want to achieve is to select the supplier from the combo box (SupplierID) on the main form and then the combo box (ProductID) on the subform to filter to only show products directly supplied by the Supplier selected on the Main Form.

View 11 Replies View Related

Forms :: Filtering Records On Multiple Items Web Form Not Working

Nov 26, 2014

trying to enable database users to filter records based on column names which i have in a combobox. They enter the required value in a textbox and click the "Find" button.The code (linked to button click event) is not throwing any errors but the records are not being filtered.

View 4 Replies View Related

Combo Box To Add Data Items Based Upon Table

May 13, 2005

I have a small database, that I would like to use a combo box to allow user to select a value based upon values already entered on table, and then if value is not found enable the user to enter a new value that will then become part of the selection for future record adds.

View 1 Replies View Related

Selecting Multiple Items In Combo Box...

Feb 1, 2005

Is it possible to select more than one item in a combo box? I have attached a sample of a database I am working on. I have a table called Interests and on the Bookings form, I would like to be able to select more than one interest in the Interests combo box, but I can't figure out how to do it. In databases I have seen, you simply have to hold down ctrl or shift to make multiple selections - that's what I want to do.

Can anyone help, please?

Thank you!!

View 13 Replies View Related

Forms :: Change Items In A Combo Box Based On A Field

May 29, 2014

So I have a combobox that pulls values from a table. I am being requested to do an update to the database which means that I have to filter some values on that combobox based on a field on a previous form.For example:The main form is called "frmcontacts" and has a table field called "txttypeofcustomer" which can have two values: "customer" or "dealer".

The form that contains the combobox is called "frmnewissue" the combobox is called "cmbissuetype". "frmNewIssue" is opened through a button on "frmContacts". This combobox pulls the values from a table "tblissuetype". The values in that combobox are then: "Repair" "Return" "Question" "Replace".

If the value on txtTypeofCustomer is "Dealer" then all the values for the combobox remain visible. If the value of txtTypeOfCustomer is "Customer" then "Replace" cannot be visible or has to be greyed out.

View 6 Replies View Related

Forms :: Populating Text Box Upon Selecting Multiple Items In Combo Box?

Nov 28, 2014

Now I have a combo box "cbo_items" and a text box "txt_selectedItems", I want to populate text field with items selected from the combo box, every time i select an item it appears in text box and to be separated with comma, so text box appears like this ( item1, item2, item3), and after finishing the whole thing the result appears in lable "lbl_result" as "total items selected: 3 items".

View 1 Replies View Related

Forms :: Change Record Source Of Combo Box On Form Based On Another Combo Box

Mar 31, 2014

I have a form that currently uses a "catch all" table for listing available equipment to choose from for an equipment field. I call it tblEquipment. What I want to do is to make it so when I type a name in (1 of 35) in one field of the current record, the record source for the equipment field immediately looks at a different table that has equipment available only for that name. To do this I plan on making 35 different tables with limited data originally found in tblEquipment. I would call these tblEquipment1, tblEquipment2, etc. I do not use a sub form, nor do I want to.

So my questions are:

1) can this be done
2)If it can be done, how can I do it?

View 3 Replies View Related

Filtering Combo Boxes Within A Form

Jul 5, 2006

I have a form that we are creating to issue project numbers. We have about 80 different clients. I have set up a combo box for the client name, where we can choose the client from the drop down menu. We want the form to filter according to the client name that you choose. For example, if you pick Lawrence as the client name, the next combo box is the project name and I want it to only show the project names that have been assigned to Lawrence in the past. Can someone please help me on how to do this and if it is even possible at all? Thank you so much for your help!! I have been wrestling with this for about a week and a half now.

View 1 Replies View Related

Forms :: Filtering A Combo Box On A Form

Jan 21, 2014

Im having trouble filtering a combobox on a form. I have two Comboboxes, one is called (FleaTickWorming) depending on what is elected in this cobo, will deside on what is available in the other combo which is called (Product). The form is a continuous form. The filtering works, in as far as it changes the list each time, but it also deletes the item that I have chosen on previous records if the list is different. My code is as follows:

Code:
Private Sub FleaTickWorming_BeforeUpdate(Cancel As Integer)
If Me.FleaTickWorming.Column(0) = 1 Then
Me.Product.RowSource = "qry_FleaProducts"
ElseIf Me.FleaTickWorming.Column(0) = 2 Then

[code]....

View 3 Replies View Related

Help Needed Filtering A Form Using Combo Boxes

Jul 26, 2006

Please help. My inability to achieve this is very frustrating.

I have a tabular form based on a query, which returns a large number of records. I need users to be able to filter the records to display data relating to what they have entered in a number of combo boxes.

For example I have fields showing Product ID, Manufacturer ID, Purchase Order number and due date. Each of these fields has a corresponding unbound combo box where users can select values that appear in the table. I need to be able to filter the form based on what is in these combo boxes. I.e. Filler for a product ID to show all purchase orders numbers and due dates relating to that product ID or filter for a manufacturer ID and due date to show all Product IDs and purchase orders relating to that a manufacturer and date.

Ideally this would be achieved by pressing a command button.

The query, table and combo boxes are all built and work fine but I am unable to get the filter to work, I am using Access 97 and am not very familiar with visual basic. Any help would be appreciated.

Thanks.

View 4 Replies View Related

Modules & VBA :: Filtering Form With Combo Boxes

Sep 19, 2013

I'm attempting to filter my form with combo boxes. I've added the below code, but the if statement that checks if one of the combo boxes is null won't work. If the combo box is null or "", it's supposed to assign a wildcard to the variable. But it doesn't pass the test and goes straight to the Else statement. I've highlighted the if statement that keeps failing in red.

Code:
Private Sub btn_Search_Click()
'create variables to store the combo box values
Dim str_Country As String
Dim str_Vendor As String
Dim str_Survey As String

[Code] ....

View 4 Replies View Related

Modules & VBA :: Combo Box Will Display Specific List Of Items - Form Asking For Parameter

May 5, 2015

I have the below code behind a form so that a combo box will display a specific list of items based on the data in another combo box on my form.

I have two copies of this same form for two different departments. One of the forms works like a dream. However, when I copy that form, change the name, and update the code as pictured below, the form is asking for a parameter FROM MY ORIGINAL FORM and will not requery the combo box. I can't figure out why...there is no reference to the original form in my VBA as you can see below. I tried deleting the form and re-creating it, I tried deleting the code and re-typing it to no avail.

Private Sub cmboType_AfterUpdate()
Me.cmboAction.RowSource = "SELECT tblStatusList.Status FROM tblStatusList WHERE (((tblStatusList.Department)=[forms]![frmInquiryFraud]![cmboType]));"
End Sub

View 4 Replies View Related

Multiple Combo / List Boxes - Sort Based On Entries

Sep 23, 2013

I am trying to put together a form where employees will be entering maintenance information into my database. One of the requirements for this data is that when they perform repair work on an asset, they have to list a problem, cause and remedy. These are codes that are specific to each asset.

The closest thing I can think of to what I am trying to acheive is the selectors on car search websites. You select a MAKE, and then the MODEL list is narrowed down based on that, then you pick the TRIM, which is narrowed down based on the model selection. I have an Excel spreadsheet of the Assets(Failure Class), Problems, Causes, and Remedies...I'm just not sure how to put this into Access to get the desired output. I have attached a sample of the Excel spreadsheet for illustration.

I believe I'm going to have to put each of the columns in a seperate table and use relationships..but I'm not sure about that.

View 2 Replies View Related

Change Combo Contents Based On Previous Combo Selection

Oct 20, 2005

Hi everyone,

I have two combo boxes on the same form bound to a table. I want the contents of the next combo box to change based on the previous combo selection e.g

cboContinent cboCountry
Africa Zambia
Africa Congo
Africa South africa
Europe England
Europe Holland

If I choose Africa in cboContinent, I want to see only Zambia, South Africa and Congo under cboCountry and if I choose Europe I want to see only England and Holland

Thanks
Humphrey

View 1 Replies View Related

Forms :: Fill Text Box Based On Combo Box (not Using Columns In Combo Box)

May 21, 2014

I'm looking for a way to have a text box auto fill based on the selection of a combo box on the same form. I cannot use the method i find all over the internet of using multiple columns in the combo box and basing the text box on that because the combo box already has multiple columns being used to determine its own possibilities and other combo box possibilities.

I would really just like the text box to work like this, but im still kinda inexperienced in VBA...

If combo box is "F004-001", then text box is "237"
If combo box is "F004-003", then text box is "280"

I know how to add in an "after update" thing, but i do not know how to do If/then statements.

View 1 Replies View Related

Populate Combo Box Based On Selection In Another Combo Box (Access 2010)

Jul 11, 2012

I'm have quite a difficult time getting a form in Access 2010 to perform the way I would like it to. I have multiple tables that I've created, and a query that contains the data from the various tables. I then ran the form wizard to select the data from the query that I wanted to populate in my form and I've added 2 combo boxes.

What I want to do:
1. I want users to be able to select a category in combo box #1 (example: "Bag")
2. I want users to be able to select a detail in combo box #2 based on the category they selected in combo box #1 (example: Combo box #1, "bag" would populate the following selections for combo box #2: "sandwich" and "tool")
3. I want users to then receive a list of suppliers that provide the product they have selected, either "Bag: Sandwich" or "Bag: Tool"

I have combo box #1 populating a list of categories already. However, I am not able to get combo box #2 to provide choices based on the selection chosen for combo box #1.

View 1 Replies View Related

Queries :: Query To Run Before Report Based On Criteria Based From Two Combo Boxes On Form

Mar 20, 2013

I have a report that gets its data from a query. I need the query to run before the report based on criteria based from two combo boxes on a form.

View 3 Replies View Related

Constraining Combo Box Values Based On The Value Selected In Another Combo Box

Sep 7, 2006

Thanks in advance for your help.

I would like to set up rules/constraints such that the value selected in combo box A determines the available values in combo box B. For example, "Combo box A" is bound to the [PartnerType] field and "Combo box B" is bound to the [PartnerRole] field. Let's say that the two choices in Combo Box A are "LLC", "LP", and "Corporation". There are 5 possible choices in Combo Box B: "X", "Y", "Z", "Q", and "U". If a user selects "LP" in Combo Box A, I would like Combo Box B to only show choices "X" and "Y". And if a user selects "LLC", only choices "Y", "Z", "Q", and "U".

Similarly, I would like to set this up so that Combo Box B is not initially visible--it becomes visible when a user selects "LLC" or "LP". If a user selected "Corporation", Combo Box B would remain hidden.

View 2 Replies View Related

Populating Combo Box Based On Data In Another Combo Box

Jan 23, 2006

Hi all!

It's been a while since I've used Access, and I seem to have forgotten this.

I have a form which allows entry of Borrower's details. I have 2 combo boxes on the form.

They are:

cboBorrName
cboLoanSN

Once the user selects the name from the cboBorrName, I want the cboLoanSN to display all loans associated with the Borrower so that they can select the correct loan and add repayment details accordingly.

I have added the following SQL statement as the record source of cboLoanSN

Code:SELECT * from tblBorrower WHERE tblBorrower.fldLoanSN=[Forms]![frmBorrower]![cboBorrName]

I have also added the following code in the After Update event of cboBorrName

Code:Me.cboLoanSN.Requery

However, it doesn't work.

Can someone please help me out?

Thanks a bunch!

View 6 Replies View Related

Forms :: Combo Box Value Based On Selection In Other Combo Box?

Nov 29, 2014

I'm trying to build a database for daily work. My work in daily basis I have to fill all condition for several items.

We have two areas, with two locations under each area, and three systems under each location and each system contain more than 500 items.

I created all tables and fill by all information:

1-Table 1: Area 1, Area 2, Area 3, Area 4, Area 5, Area 6.
2-Table 2: Location 1, Location 2.
3-Table 3: System 1, System 2, System 3.
4-Table 4: all items under System 1-1-1
5-Table 4: all items under System 1-1-2

[Code] ....

Last table will be LogBook which will be as follow:

date l time l area l location l system l item l Conition1 l Conition2 l Conition3

My question regarding to the form of the above table:

How can I make a combo box for area field and when I select for example area1 will appear only the locations which under area1 in location field, and when I select location1 for example will appear only the systems under location1 in system field, and when I select system1 one for example will appear only items under system 1.Combo box list will be based on the selection in previous combo box and so on.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved