Problems Using A Combo Box To Filter Records In Another Combo Or A Sub-form
Nov 8, 2004
I have seen a few articles here and there on using a combo box to filter records in a sub-form and to filter records in another combo box, but I am not getting anywhere. I hope someone can belp
Exercise 1
For this exercise, I have the following tables:
tblClients containing client names
tblProjects containing some project details
I want to set up a simple form, so that the user can select a client name from a combo box on the main form which filters the project detail records in the Projects sub-form.
Once the user enters project details, I want this info as well as the selected ClientID to be fed back to tblProjects.
Exercise 2
I have the following tables:
tblProjects as above
tblWorkstream containiig names of workstreams and some other details.
Every project has one or more workstreams
I have a form where users will enter hours worked on each workstream. They will first select Project from a combo box on the main form. This should then filter records to be displayed in the Workstream Combo box, before they can then enter hours.
Please let me know if you need me to explain any part of this better.
Thanks in advance.
ps. I am a novice, so I'm hoping to do the above using default MS stuff, not with complex programming!!!
Thanks
View Replies
ADVERTISEMENT
Oct 1, 2014
I am using combo box to filter records from a form. The data source for the form is from a query.
I use the combo box within the query to filter the data, I would like the form to refresh/ run the query again every time a new selection is made from the combo box.
View 2 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
Aug 14, 2014
I have a combo box located on one of my forms, that is getting its information from a field in a table.This table has all its records displayed in a subform. I would like the combo box to basically requery the subform to only show records where the name matches what is in the combo box.
It is basically a list of customers, so if I was to select Person1 in the combo box, I would only want to see records for Person1 in the subform.I would also need a way of clearing the search results, in order to get the subform to display all records again.
View 9 Replies
View Related
Nov 18, 2004
this is a re-post - can anyone help? I think this is an easy one for the experts out there!
I have a form called frmTshtExp which is bound to a query called qryTshtExpenses. Most of the fileds in the form are combos and text boxes bound to the fields in the queries.
However, the first combo is unbound. It is called cboProjectSelect and it filters the second combo called cboWorkstreamExp. This is done using a query called qryTshtWorkstreamExp which has workstreamID, workstream and projectID as fields.
The filter works fine. The trouble is that selecting a project from the first combo filters workstreams from the second combo for ALL records that are entered. In other words, no matter how many rows are entered in the datasheet on the form, they all have to have a workstream belonging to just ONE project. I want to be able to enter different workstreams belonging to DIFFERENT projects on each row.
I hope you can help. I've put in a reduced version of my db in the following location:
http://uk.f2.pg.briefcase.yahoo.com...rum.zip&.src=bc
unfortunately, I didn't know how to disable the securities I've set up, so you need to log use the shortcut (after modifying the target location in it's properties) and use the word "forum" as username and password.
Thanks in advance
Sunil
View 1 Replies
View Related
Sep 2, 2013
i have a combo box, which looks up a list of school names from my main table. The user selects the desired school, then clicks the button underneath, which opens a report. What i want the report to show is the next 31 (that is how many fit on one page) schools AFTER the school that the user selects - the underlying query for the report has them in alphabetical order. There are ~250 schools in the list.Even if i can get it just to display the records after the selected one on the form, that would be fine.
View 4 Replies
View Related
Jun 29, 2014
I have a table with the following 5 fields. (Service Type), (Valve Name),(Size),(Rating),(Description).
I want to do two thing:
First: I want to select the required information from the first 4 fields using combo boxes and get the last field (description) based on the selected 4 fields. In other words, i want the record to be filtered using first 4 fields to give me the last field info.
Second: I want to store the filtered record (all 5 fields) in another table.
View 6 Replies
View Related
Nov 5, 2013
i have a table with three column Named
1-State
2-City
3-Customer
on a form i m placing 3 combo box for each column how can i filter combo 2 from table after select value from combo 1
View 1 Replies
View Related
Jan 27, 2015
I'm having a little difficulty setting up a combo box to filter another combo box. I've actually got one working but the second one is giving me all sorts of errors. On the attached database, there's a form called frmAddNewRecord. At the top of the form there's a combo box which allows you to select a name and another combo box beside it which acts as a filter so only names from a certain section are shown (working fine).
The subform which is attached to this form and contains the training details, has another combo box which allows you to select a job...I've tried to add a combo box beside this to filter it but I can't get it to work.
View 5 Replies
View Related
Jun 6, 2014
I have a form that has many fields with the same part number but with a different PO number
Example
part number po number
1 3
1 10
1 15
What I want to do is select the part number and the po number together
Say part number I po number 15
At present my combo box only will select one of these IE THE part number ...
View 2 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
Aug 21, 2013
I have a reservation form and I want to tick a checkbox that will filter the form based on what is in the "Reservation Status" combo box.When the checkbox is ticked, the code would remove all records that have "Complete" as a status in the "Reservation Status" combo box. The non-working code that I currently have is:
Code:
Private Sub chkHideComplete_AfterUpdate()
On Error Resume Next
If Me.chkHideComplete = True Then
Me.filter = "[ReservationStatus] = 1"
Me.FilterOn = True
[code]....
View 7 Replies
View Related
May 16, 2013
combo boxes and continuous forms.I have a continuous form (works great), with two combo boxes that navigate (rather than limit/ filter the list) to the desired selection.One for suburb and one for postcode.
I have expanded the select criteria for the suburb to include postcode as some suburb names are used in more than one state. (I do not want to have to select a state, then suburb).I changed the column count to see the postcode next to the suburb, however when the selection is made only the first instance is selected.
Example.
Epping 2121
Epping 3076
If I chose Epping 3076, Epping 2121 is selected and the continuous form navigates to that suburb (rather than the one with 3076).Can I change this so that I can still search by postcode, or search by suburb and have the form navigate to the corrected suburb postcode combination?
View 5 Replies
View Related
Sep 25, 2013
I Have 2 combo box on a form: Product_Type (6 values) and Product_Name (30 values and each belongs to a type), I want to narrow down the Product_Name to related Product_type whenever I select a type in the first field. Is there anyway to do this?
View 2 Replies
View Related
Aug 26, 2015
=Sum(IIf([Pass or Fail]="Pass" And [Pay Period]="02" And [Location]="Purple",1,0))/IIf(Sum(IIf([Pay Period]="02" And [Location]="Purple",1,0)) = 0, 1, Sum(IIf([Pay Period]="02" And [Location]="Purple",1,0)))
For [Pay Period] ="02" is there a way to make it filter by a combo box from the Mainform? This code is on a Subform the combo box is on a Mainform
Forms![Overview_]![Combo35] <the path for the combo box.
I tried inserting it where the 02 was but it just returns 0 and if I remove the "" it will error the whole thing out. Same if I try to just remove the [Pay Period]="02" and replace it fully it errors out.
View 5 Replies
View Related
Jan 18, 2006
On clicking a button to open a form I want to filter a list in a combo Box located on the forms subform
The main form is called 'Frm_orders'
The sub form is called 'sub'
The combo box field is called 'CostCodeID'
and I want to filter column 0 of the combobox which is Tbl_InvoiceCostCodes.Type to show records with a type of 'Repairs'
View 2 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
May 8, 2013
I have a database and a form that is based off of a simple table. The table has NO look ups and all of the formatting is text. The form is continuous and simply displays these fields. (Kind of like a company roster with name and department). I have two combo boxes at the top of this form in the header with the intent of filtering the form records by department and employee type (lets say A or B).
I have tried every way I know how to get these records to filter and they will not filter correctly. Utilizing methods I have used in multiple other databases, I set the two comboboxes to cascade based on a query in the recordsource. The vba I'm using in the after event of each combo is ....
Me.Filter = "[Brand] = '" & Me.cbobrand & "'"
Me.FilterOn - True
'Brand is the "department" and the other identical code is for Personel_Type
This filters the records but independently. So, cbobox1 filters the records to show all Brand 1. When cbobox2 selection is made it shows all the Personel_Type of the selection however the first filter is already disregarded. (i.e. When cbobox2 selection is made, it contains both brands instead of the one I've just selected and filtered in cbobox1. )
Outside of making two queries to account for each possible filter, I have tried setting a filter on the filterON, I have tried a SQL based VBA code, I have tried making two forms and attempting to set the filter on open but either way, nothing keeps the first filter selected (or the FilterON, or both SQL filters, etc). The ONLY way I have found to get it to filter both is with the filter function in Access, which is not useful as my users will not have access to the menu bars.
I have this EXACT same setup in another database and it works fine with 3 cbo boxes with an after even to set a filter for the recordsource for all three.
View 5 Replies
View Related
Apr 30, 2015
I am running access 2007 and have a continuous form that I am trying to filter. The table that I am pulling the data from has the following fields: Order_Date, Order_number, Model.
I want to filter based on Order_Date.
I tried creating an "after update" Macro builder on my combo box and using "Apply Filter" but I cannot seem to get my syntax right.
Filter is the name of my combo box
Here is my current setup:
Filter Name:
Where Condition: [Order_Date] Like [Filter]
Control Name:
However whenever I select a new date from my combo box it asks me to "Enter Parameter Value" for Filter, which I don't understand because my combo box name is Filter. I would think they would link up.
View 5 Replies
View Related
Feb 6, 2014
I have a modal form with a combo box and a search button.
When the button in pressed, the value of the combo box is the parameter of a query.
Copy and paste of the search button code:
Code:
Private Sub btnSearch_Click()
Dim db As DAO.Database
Dim qdef As DAO.QueryDef
Dim strSQL As String
[Code] ....
I've tested the SQL query produced by the search button and it works, but when I try to push the button, I receive an error on the following line:
Set qdef = db.QueryDefs("qryFilteredData")
View 6 Replies
View Related
Dec 29, 2013
I have a form that is filter based on a combo box. I would like to add another filter for date. but the code I'm using for the first combo box doesn't work for date.
the code is:
Sub SetFilter()
Dim LSQL As String
LSQL = "select * from Preventive_Q_View"
LSQL = LSQL & " where Item_Name = '" & Combo206 & "'"
Form_Preventive_View.RecordSource = LSQL
End Sub
How do I modify this code to work with the date combo box? Also, is there a way to get both filters to work together, as in filter based on the first combo OR the second combo, OR both?
View 1 Replies
View Related
Sep 6, 2014
I have a simple split form with datasheet on the bottom and a couple of comboboxes (unbound) on the form header which the user can use for filtering the data. When some users open the form the allowedit property of the form is set to false, because those users can only view the records. But the result is that also the comboboxes cannot be set, hence no filtering!
View 6 Replies
View Related
Jun 1, 2012
I use filter Combo boxes in a lot of places so that users can filter records easily. (Not combo boxes for input).I have them labeled as filters but nonetheless users keep trying to input into them for some reason.How would I add a message box to it so that it states that 'this box is not for data entry etc'.
View 2 Replies
View Related
Feb 26, 2015
In the Purchase Order details form a Supplier has to be chosen via a combo box. Based on that another combo box in the subform displays products only from this supplier (Products table is linked to supplier table).
I managed to let the subform combo show only relevant products using criteria referring to the main form combo box. Also other product data will show accordingly in text boxes. So far so good.
However when I close the form and reopen it the subform combo box is blank, other text boxes still show the right values. If I remove the filter criteria for the subform combo then all fields show all data correctly. (However the combo box is unfiltered again 8-/)
So somehow the 'criteria' prevents the combo to show the value that was previously chosen.
View 2 Replies
View Related
Apr 3, 2008
Hi All. I'm Sure this is simple enough, but i just cant seem to figure it out.
I want to use a Combo dropdown box with Codes FC000 - FC900. to return records in a second drop down box that begins with the Letters selected in the first dropdown box.
So When a Record is Selected in ComboBox1 eg: (FC0**), ComboBox2 brings up the record to match (FC000).
eg:
ComboBox1: ComboBox2:
FC0**FC000
FC010
FC020
I hope i have expressed what i want to do.
Any Help would be greatly appreciated.
View 9 Replies
View Related