Forms :: Filter Subform By Combo Box Dates
May 28, 2013
Currently I have a main form w/ subform. In the main form, a user selects a bond's serial number from a combo box and the subform displays the bond's returns for the months owned along with a start date (each month has its own start date). So if I enter bond ABC in combo box, the subform will display "13-01-01, 4%" for January, "13-02-01, 8%% for February..etc.
So if we owned a bond from January to March, it will display the three months' returns. I want to add two combo boxes: begin and end date so that I can filter the subform to only show a particular set of months' returns.
I have three tables right now: Cusips, which lists each bond's serial number, CusipStartPK, which lists the monthly returns for each serial number, and ParameterDates, which is a list of four start dates--ie 13-01-01 for January, 13-02-01 for Feb, etc.
I've tried parameter queries and vba codes from other threads but I can't get this to work.
View Replies
ADVERTISEMENT
Jun 17, 2013
I am trying to filter a subform based in a combobox. What im doing wrong, is it the Sintax? This is what i have so far..
Private Sub Buscar_Click ()
Dim strFilter As String
strFilter = Me.CombNomes.Value
Me.subfrmBANCO.Form.Recordsource = "[Nome]=""&strFilter&"""
Me.subfrmBANCO.Requery
End Sub
'subfrmBanco is a subform based on a table called "BANCO"'
'[Nome] is the field in BANCO that im trying to filter on
'CombNomes is the combobox im using as filter parameter
View 10 Replies
View Related
Dec 14, 2014
I have a table contains 5/6 column. now i need to filter the table on a form randomly.
Suppose I have 6 different combo box for 6 column. Now on the sub form the control shows the whole table. i this case i have clicked the 3rd combo box, then it will show the whole column in its list. if i select any one of it the table will show the common values below. then i will click the second combobox which will populate the present data shown into the subform table after the first filter.
After that i wish to filter the fifth column where the fifth combobox will populate the data after the second filtering.
By all means the active data into the column will ony populate into the combobox.
View 1 Replies
View Related
Sep 21, 2014
I have a form called frmSearch with an option group called grpSearch ,a combobox called comboNyaba and subform called subform_CasesSearch has a checkbox called CaseClosed
The combobox filter the subform based on option group choice and value in textbox called NyID ... it works fine except that if the combobox is empty it give me error the code in main form i put it on Load and on current
Code:
Private Sub Form_Load()
With Me.subform_CasesSearch.Form
If Me.grpSearch.Value = 1 Then
.Filter = "NyID = " & Me.ComboNyaba
[code]....
View 3 Replies
View Related
Dec 9, 2013
I am trying to apply a filter to a datasheet subform using a combo box.
This is the code I have used on the "after update" event:
[Ordering - Price list items].Form.Filter = "[Secondary Category]=" & Combo72.Column(1)
[Ordering - Price list items].Form.FilterOn = True
When I run the query i get the following error:
Runtime error 3021:
No current record
I am not sure why it isn't working as this is how I have set up other combo box filters and it worked fine.
View 6 Replies
View Related
Mar 9, 2006
I have a form with a combo box on it.
I want to use the combo box to query a subform.
So basically when you click on the record in the combo box it will filter the subform based on the selection.
Is there is a simple way or do I need to be more specific about the requirements ?
Any help would be greatly appreciated.
Domble
View 2 Replies
View Related
Dec 13, 2005
Hello everyone!
I think I am asking the impossible, but can't think why it can't be done!
Here's the dilema:
I am working with an unbound form to search a Mobile Register.
On the form I have an unbound combo box which looks up User details from a table (LKP_User).
When I select a user, I want the subform below to filter all the details for that user and show me which mobiles they have.
PROBLEM:
Combo Box has 3 fields, User_ID, User, Dept_ID. User_ID is bound column but not displayed in combo (only User) is displayed.
I need to link the User_ID with the User_ID on the subform, but don't know how to tell it to look at column 1 of the combo box.
Can anyone point me in the right direction? Do I need some fancy code to get this to work?
Cheers in anticipation
NKA
View 6 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
Feb 2, 2015
Main form frmSiteDetails
frmRooms (subform) holds Room information for the rooms on each floor. This form has LinkMasterField [SlaveFloorsID] and LinkChildFields [FloorsID]
frmFloors subform holds floor information for each floor in a property. This form has LinkMaster [SiteID] and LinkChildFields [SiteID]
Both these forms sit on a Tab Control (TabCtl57) and do work properly.
What I would like to do is to also have a Combo to pick a floor number from frmFloors and filter frmRooms. Both these forms have FloorsID fields. This would save a user having to go back to frmFloors to pick a different floor and then go back to frmRooms to view the room data for the new chosen floor.
View 2 Replies
View Related
Jul 25, 2005
Hello All,
Thank you for taking time to view my access problem, I hope somebody can help / give advice on my problem.
I have a Form, within that form I have a subform. On the main Form I have several Combo Boxes.
ComboBoxes include; Year. Movie. Country.
What I want to do is when the user selects from the various values within each Combo Box to display the data in the subform accordingly.
I was able to use the Wizard to display the Movie Information correctly. The code is below for that. How can I go by doing this.
' Code to change the movie information, when they select the combo box
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[MovieID] = " & Str(Nz(Me![cmbMovieList], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
I am very sorry if my question seems broad, ill try to be more specific upon request. THANK YOU for your time and your help.
View 3 Replies
View Related
Jul 1, 2014
I thought I was in the home stretch of my project, everything worked great when I was messing with 200 records. Now that I'm messing with 2000+ records, things are very slow. So a search form redesign!
I have a form (frmSearch) that has a subform (frmSubSearch) embedded in it. The frmSubSearch is a datasheet only that is just pulling its info from a query (qrySearch). qrySearch has about 8 columns of data in it.
On the main form I have a combo box that is feeding its list from the qrySearch using a SELECT DISTINCT statement. So a user selects an item in the combo box and my After_Update fires. This sets a filter on frmSubSearch. I have three of these combo boxes that can add to the filter and they work great so far.
But I want to have the combo boxes filter themselves based on whats left on frmSubSearch. So if a user selects something in the 2nd combo box, the sub form filters and updates, but then I want the other two combo boxes to only have valid selections, and not something selectable that would wind up giving me a blank sub form result.
I have tried using .Requery in various ways, but its not working. I have also been looking into the idea of Cascading Comboboxes, but these don't seem to quite fit what I'm trying to do.
View 9 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 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
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
Apr 7, 2014
I am trying to apply a filter to a subform using the text filter Like "*text*" by setting up a dynamic SQL query for the subform, see code. The issue I have is that the SQL doesn't seem to apply to the form because it isn't filtering the form.
Code:
If Not IsNull(Me.ID) Then
pubRecCountPubs = 0
Dim strSQL, strSelectText, strWhereText, strOrderText, strFilterText, strAuthorText As String
[Code]....
I have this working fine in a 2010 database using the DoCmd.ApplyFilter method, but need to set up a similar database using 2003 and DoCmd.ApplyFilter doesn't work with subforms and I can't figure out why this code isn't working.
View 7 Replies
View Related
Jun 26, 2013
I have a CRM database which also handles billing for my company. I have an Order Form which displays info from the Orders table, and also various subforms from related tables (Order Details, Payments, Collection Notes). The Payments table has a One to One relationship to the Orders table and the two are linked by the Key Field Order ID.
Here is the problem, if I open the Order Form and navigate to any record the correct payment information is displayed. However if I use a Filter on the Order table the Payment information disappears and only a blank payment form displays - making it appear that a payment has not been made. If I remove the filter the payment information is still gone until I close the form and reopen it. Also, this does NOT happen to my other subforms (ie Order Details) - note that the other subforms are for tables with a One to Many relationship.I have tried basing the subform on the actual table Payments and also on a query of the payments table.
View 3 Replies
View Related
Jun 14, 2013
I have a subform in a form that displays all members of staffs Name and employee no. I am trying to find a way to search the subform on the main form it's on using textboxes to filter data, here is the code I have that searches the employee no.
Code:
Private Sub Command178_Click()
Dim dbs As Database
Dim qdf As QueryDef
Dim sqlstr As String
Dim sqlstrwhat As String
Set dbs = CurrentDb
[code]....
I do not get any errors and I haver used the same code structure for other subform filters but when I search for an existing employee no. the subform only displays a blank record with the employee no. 1?
View 5 Replies
View Related
Feb 25, 2015
I am trying to apply a filter to a subform.I have three tables as follows:
ITEM (primary key Item ID) (AutoNumber)
CREATOR (Primary key Creator ID) (AutoNumber)
ITEM CREATOR (junction table) (primary key Item ID and Creator ID)
ITEM has a one to many relationship with ITEM CREATOR.
CREATOR has a one to many relationship with ITEM CREATOR.
I have added a subform to the data entry form for ITEM. The fields in the subform are the fields in the ITEM CREATOR table, i.e. Item ID and Creator ID. The purpose of the subform is to link an Item to a Creator and populate the ITEM CREATOR table.These are the subform properties:
Record Source: Item Creator subform
Link Master Fields: Item ID
Link Child Fields: Item ID
The subform contains a combo box for Creator ID. These are the properties.
Control Source: Creator ID
Row Source: Item Creator Subform query
Bound Column: 1
Column Count: 9
When a user clicks on the combo box drop-down, the first 9 fields in the CREATOR table are displayed. The purpose of this is to give the user more information when linking ITEM to CREATOR, so that the correct Creator ID is chosen and the Item is linked to the correct Creator. Two of these fields are Creator Surname and Corporate Creator.
The issue is that CREATOR now contains over 2500 records. When trying to link an Item to a Creator the drop-down list that appears when the Creator ID combo box is clicked is very long and the user is potentially faced with a long time spent scrolling the list. I would like to filter the drop-down list by Creator Surname so that the user enters a surname and only the Creator IDs for the Creators that have that surname appear in the list.
View 1 Replies
View Related
Jan 14, 2014
I do review SQL sometimes to check what's going on. Access has been a steep learning curve, but my understanding of queries and relationships improved a lot recently.Some of the finer points of forms are just giving me frustration. My form related knowledge probably lags behind my data/table/relationship/query related knowledge. Its probably simple, but as you know these things are not too intuitive at first.
I have a query which (built on another query and some tables in turn) delivers exactly the data I want. I want that data to come up in my form (or datasheet subform actually) BUT, I want to be able to filter for PERSON.ID. I want the user to be able to use a combo-box to do that filtering.
OK then, main form, with unbound combo-box at the top. I assume it should be unbound because I don't want any data edited based on selections which are supposed to cause temporary filtering only (correct me if I'm wrong). For the Row Source I've used a qryWholeName. This query creates a WholeName for each person, and includes their PERSON.ID as well.
[URL]....I cannot see the example DB they have attached in the thread. Finally, my database is likely to be accessed by several people at once. Different users will want to be selecting and filtering for different people simultaneously.
View 3 Replies
View Related
Jun 22, 2014
Was trying to use a button to apply a filter from a form to a subform
the subform (frmLogOverview_Subform) has a field call AttendedBy_FK and I wish to filter this from the main form (frmCallOverview)
how can I do it ?
View 2 Replies
View Related
Jul 30, 2014
I'm trying to make it so I could filter a subform from the main form and it will hold those results and allow them to be edited in the subform without it refiltering it. I want it to still be able to update the table by whats edited in the subform, but I don't want it to "disappear" from the subform when the filtered "matching" criteria is changed on a record.
How could I go about accomplishing this?
Here is my code for the filter of my subform:
Private Sub Form_Load()
strFilter = "[Rep Number] = " & "RepNumberField" & _
" AND " & "[UserName] = " & "UserNameField" & ""
RepReassignmentSubform.Form.Filter = strFilter
RepReassignmentSubform.Form.FilterOn = True
End Sub
View 14 Replies
View Related
Dec 10, 2013
I have a main form (Frm ViewModel) with a subform (Frm class_attributes) in datasheet-mode based on a Query.Frm ViewModel has two controls, a ComboBox Select Model (presents you with a list of data models ) and a ComboBox Select Class (presents you with a list of Classes selected from a class-table, based on the model-id derived from the Select Model ComboBox). The subform Frm class_attrbutes shows you all class attribute combinations based on the model-id derived from the Select Model ComboBox.After the initial selection of the classes and attributes, the subform Frm class_attribute can be filtered on class by the Select Class ComboBox.
As long as I just switch between models through the Select Model ComboBox, it all works fine. The subform Frm class_attributes is updated (Requery) correctly.The problems start when I filter the subform Frm class_attributes, either through VBA OR by using the GUI filter possibilities of the sub form itself.
Private Sub select_class_AfterUpdate()
[Forms]![Frm ViewModel]![Frm class_attributes].[Form].Filter = "class.object_name ='" & [Forms]![Frm ViewModel]![class_name] & "'"
[Forms]![Frm ViewModel]![Frm class_attributes].[Form].FilterOn = True
The filter works fine, but once youve used it:
- The filter stays active, even when turned off, so when you Requery the subform it reutrns results with the original filter.
- The filter is removed, but now the subform after requery keeps showing the selection of classes & attributes for the model_id where the filter was first set.
I tried all possible combinations of FilterOn = False and .Requery but it doesnt solve it. I tried reassigning the RecordSource but since the underlying SQL is extensive (a few joins) Access seems to have a problem with this
[Forms]![Frm ViewModel TST]![Frm class_attributes].RecordSource = "SELECT [class.hierarchy],
[class.object_name], [class.technical_name], [class.definition], [parent_class.object_name]
AS ParentClassName, [parent_class.level], [A.AttributeName], [A.AttributeTechnicalName],
[A.AttributeDefinition], [A.AttributeType], [A.AttributeTypeDescription] FROM [parent_class]
[Code] .....
I tried making the sub-form a bound form by manually linking the masterfield to the Select Class ComboBox..I played around with the On-Focus and OnCurrent-events
View 4 Replies
View Related
Jan 15, 2014
In Dec 2013 I created an Access table "Donations" that contains fields regarding charitable donations (Date, Donor, Amount). I use an Access form "Select Donor Form" to select a specific Donor (via combo box) from the table. Then an Access Query "Select Donor Query" uses the Donor selected from the combo box in the form to display all of the donations from that Donor for the year as an Access report.
Since I just created this Access table last month, it only had data from 2013. Now that we are in 2014, in preparation for when I run this report next December, I obviously need to modify "something" to clarify that I want the donations from a specified Donor for a particular year (i.e 2014, 2015, 2016, etc.).
I added a field to the Access table called "Year". I probably can derive the year from the already present "Date" field, but sadly, I don't know how to do that. I want to simply edit the existing combobox in the existing form to return Donors that have a record in the existing table where the Year = 2014. That way, the only changes I will need to make over time is to update which year I need and all of the Donors for that year will populate in the combo box.
Below is the Access macro belonging to the combo box in the form. I converted the macro to Visual Basic. What to add to select the Donor for a particular year. You can see that right now it is only selecting "Donor Name" without regard to which year the donor has a record.
'------------------------------------------------------------
' Combo3_AfterUpdate
'
'------------------------------------------------------------
Private Sub Combo3_AfterUpdate()
On Error GoTo Combo3_AfterUpdate_Err
DoCmd.SearchForRecord , "", acFirst, "[Donor Name] = " & "'" & Screen.ActiveControl & "'"
[Code] .....
View 9 Replies
View Related
May 15, 2013
I am trying to use .Filter for two combo box but it does not work...My code is:
Private Sub btn_buscar_Click()
With Me.FormularioEmpresas.Form
.Filter = "" 'limpiar el filtro
.FilterOn = False
If Nz(Me.cbo_actividad, "") <> "" Then
.Filter = "Actividad='" & Me.cbo_actividad & "'"
[Code] ....
In the the black line appears an error...I do not know why..
View 3 Replies
View Related
Jul 16, 2014
I have a list box and a combo box on a form based on a query. The list box is a multi-select, with column values of Client, ClientEmail, and Medical.
Medical is a simple yes/no field, and it is what I want the combo box to sort by. I have a Where clause in the query: WHERE (Medical=[forms]![frmOne]![cmbbx]), and the only values in the combo box are Yes and No (1 column). I also have the combo box set to run a requery macro after updating. I've also tried to change the macro to VBA:
Code:
DoCmd.Requery "lstbx"
When I pick a string from the combo box, the list box just goes blank, rather than updating to clients who either do or don't have Medical, and I can't understand why.
View 1 Replies
View Related
