Forms :: Filter Subform By Months Using Combobox?
Aug 18, 2015
I made a subform based of a query that I put in a main form. The subform contains a date field, where a full date is inserted (example 24.12.2015.). What I am doing is making a form where the subform would be filtered by a combobox by months. This may be wrong (correct me if it is), but for values in the combobox I placed number from 1 to 12 (as months). This combobox would then only show records from that month. I used a Change function in VBA on the combobox but I keep getting an error.
This is the VBA on the combobox where I change the record source of the subform. I probably made the WHERE part wrong but dont know how to fix it.
Combobox name is cboMonth.
Field where the full date is, is DateOut.
Subform is called subCustomer.
Code:
Private Sub cboMonth_Change()
Dim SQL As String
SQL = "SELECT qryCustomers.SID, qryCustomers.KID, qryCustomers.MMT, " _
& "qryCustomers.Owner, qryCustomers.User, qryCustomers.Policy, " _
& "qryCustomers.DateOut " _
& "FROM qryCustomers " _
& "WHERE Month([DateOut]) LIKE '*" & Me.cboMonth.Text & "*'"
Me.subCustomer.Form.RecordSource = SQL
Me.subCustomer.Form.Requery
End Sub
View Replies
ADVERTISEMENT
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
Aug 18, 2006
I am running into a problem with just one form in my database. I was getting the error 'Can't build a link between unbound forms'. After searching here and elsewhere the solution is/might be to type the linked fields into the child and master proprty fields. However when I do that I still can't get this form to work. Would anybody please take a look at the file attached - I trimmed it down to two tables with a few records and a form/subform.
I want to make a selection in the combo box and have the filtered results displayed on the form below - simple and I have done the same thing in the database elsewhere but I just can't see why it won't work here.
Thanks
Steve
View 3 Replies
View Related
Jan 9, 2006
I have a main form with an unbound combo box. I also have a subform showing records in datasheet view.on the after_update event of the combobox I want to filter the subform. Here is my code (note frmqrysubmittals is the subform and the recordsource for the subform is initially the RS1 portion of code):Dim RS1 As StringDim RS2 As StringDim RS3 As StringRS1 = "SELECT tblSubmittals.SubmittalID, tblSubmittals.Format, tblSubmittals.[Specification Section], tblContractorInfo.Contractor, tblSubmittals.[Project Number], tblSubmittals.Description FROM tblContractorInfo INNER JOIN tblSubmittals ON tblContractorInfo.ContractorID = tblSubmittals.ContractorID"RS2 = " WHERE (((tblContractorInfo.Contractor)=" & Chr$(34) & Me.cboContractor.Value & Chr$(34) & "));"rs3 = RS1 & RS2Me.frmqrySubmittals.Form.RecordSource = rs3me.frmqrySubmittals.form.requeryWhen I update the cbo box the subform doesn't display any records. When I go to design view and look at the record source it displays the RS1 portion (the original RS), but all of the terms are placed in brackets []. Note, the subform and contorl are both named frmqrySubmittals.I've been at this for days. What am I doing wrong?Thank you everyone for your help. This group has been a great resource over the past few months as I stuble through learning Access.
View 6 Replies
View Related
Aug 5, 2015
I am currently having trouble filtering my subform by a different column than the bound column set in properties.
the comobobox shows the ID for the last email sent, with the combobox drop down showing the name and date of email when dropped down. I have tried 2 things and neither work...
1) Calling the filter on the combo-box column itself:
Code:
Dim myDate as string
myDate = [Forms]![BenSearchForm]![BenSearchSub]![LastEmail].[Column(5)]
DateFilt = " AND" & myDate & " BETWEEN " & "Nz([forms]![BenSearchForm].[Date3],#1/1/1900#) AND Nz([forms]![BenSearchForm].[Date4],#31/12/2100#)"
I have used similar code on another form, but I can't get the myDate variable to get to value of the 6th column in the dropdown (Date Of Email). an Easier way to look at this would be:
Code:
DateFilt = " AND [Forms]![BenSearchForm]![BenSearchSub]![LastEmail].[Column(5)]" & " BETWEEN " & "Nz([forms]![BenSearchForm].[Date3],#1/1/1900#) AND Nz([forms]![BenSearchForm].[Date4],#31/12/2100#)"
2) Inside the subform, I have set up a seperate field that reads the result of the dropdown box column(5) and shows it. For example the Email with ID 22 has a date of 4/8/15, so any record with last sent email being 22 has a record that says 4/8/15. I am trying to use this value to filter between, but am unable of passing the value to my filter.
The textbox is called "Email Date" and the Control source is "=[Forms]![BenSearchForm]![BenSearchSub]![LastEmail].[Column](5)"
It shows the correct data, but does not allow me to filter by this field, when the apply filter button is pressed it asks me for the parameter value of the LastEmail Field.
Code:
DateFilt = " AND" & " [EmailDate] BETWEEN " & "Nz([forms]![BenSearchForm].[Date3],#1/1/1900#) AND Nz([forms]![BenSearchForm].[Date4],#31/12/2100#)"
I use a number of set variables for my filters, so my filter ends up looking like
Code:
.Filter = IDFilt + EmailFilt+ DateFilt
where all but IDFilt begin with AND. I use this on a number of other forms so I am sure this is not the issue!
View 3 Replies
View Related
Jan 14, 2015
I am using Access 2010, Windows 7
I have an unbound form with a combo box and a bound subform on it.
I am using the combo box to get a value to filter (and display) records in the subform.
My code is:
Private Sub cboSessions_AfterUpdate()
On Error GoTo cboSessions_AfterUpdate_Err
Me.SSubform.Visible = True 'subform was initially invisible
Me.SSubform.Form.FilterOn = True
Me.SSubform.Form.Filter = "[LinkID]=" & Me.cboSessions
Me.SSubform.Form.Requery
cboSessions_AfterUpdate_Exit:
Exit Sub
cboSessions_AfterUpdate_Err:
MsgBox Err.Description, vbCritical, "PROGRAM ERROR"
Resume cboSessions_AfterUpdate_Exit
End Sub
The subform is initially invisible to stop it showing all records.
This works fine AFTER the first selection(!) - the first Update of the Combo Box just doesn't filter the subform??
I have tried putting code in the main form's On Load Event - allocating a value to the Combo Box and running cboSessions_AfterUpdate but that does not work either.
View 1 Replies
View Related
Mar 21, 2013
I have a table which has a "Start" and "End" fields, and i made a query based on this fields which continues to look up the next points until the last end points. I wanted to filter the last end points on the query table that was related on the combobox value.
View 3 Replies
View Related
Mar 3, 2014
I have almost completed creating a continuous form which I want users to be able to filter though the records based on to/from date, badgenum and a response string.
I then want to have a button, btnSelect, that will select the remaining records and open a report based off only the remaining records.
1.) Everything on the continuous form works except when trying to filter a combo box, cboBadge, field name "BadgeNum". BadgeNum is data type "short text". I have modified the code below from the following Allen Browne link. It keeps throwing a debug on me.Filter = strWhere at the bottom of the btnFilter sub.
2.) As far as the btnSelect and printing remaining records, I guess I'm not sure where to start.. I currently have another print button that will print individual records only which works fine.
[URL] ....
Code:
Private Sub btnFilter_Click()
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "#mm/dd/yyyy#" 'The format expected for dates in a JET query string.
[Code] ....
View 4 Replies
View Related
Jun 30, 2015
I have a database that is used to generate quotations. I have a form that I want to use to Re-Quote something. By Re-Quote, I mean I want the employee to be able to go in and search for the Part desired for Re-Quoting and create a new quotation with all of the information from that part and have the ability to change any of the information as needed. The Form is unbound, and has tab control with three tabs. I would like to search by three different categories "QuotationNumber", "Customer", Or "PartNumber". How do I have the combobox filter the list box below based on what is being typed in the combobox?
View 6 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
Apr 22, 2005
I have 2 comboboxes, the first one is called "activity", whereby I have 3 options to choose from, and the second is called "level".
When I click an "activity", for example Drawings, I want the "level" combobox to list a unique set of options for that category. and if I click on a different "activity" for example Planning, I want the "level" combobox to show a completely different set of options.
If anyone can give me any help on how to do this then I would be very grateful!!
Thanks
Angela :eek:
View 2 Replies
View Related
Dec 5, 2013
On my main form I have a subform which holds two table values. [Roles] & [TrainLevel] example of this looks like this:
Plant Manager - T1
Assistant - T2
and so on for about 12 rows with different roles, each with different training levels.
This subform is then based on a query: [qryRoleEventWithRoles] which also looks at all the other selections, selected on the main form.
eg. User selects a training course on the main form, this then changes the subform and shows what level of training each role requires training in, T1, T2 etc..
All this has been working fine for months, almost a year. Each selection on the subform is based on using two combo boxes [Role] & [TrainingLevel] these are both setup on the subform and using the autonumber field, RolesID and TraininglevelID from two separate tables.
Like I say, this has been working fine. Now, all of a sudden, they've stopped working and telling me I can't change the valve because there bound to an autonumber value. or something like that.
View 3 Replies
View Related
Jul 13, 2007
Can someone help?
I need the code for a filter or query which will show the past x (12, 24 etc) months worth of data. Each record has a date tag so i need a filter which will just show records for say the past 3 months.
Any ideas?
View 4 Replies
View Related
May 3, 2015
So I have a Form (frmPositions) with a subform attached (fsubPosSkill).They are linked through Position ID frmPositions Record Source is qryPositionNeeds
Code:
SELECT tblPositionNeeds.PositionNeedsID, tblPositionNeeds.PositionID, tblPositions.Position, tblPositionNeeds.NeedsID, tblNeeds.NeedStatus
FROM tblPositions INNER JOIN (tblNeeds INNER JOIN tblPositionNeeds ON tblNeeds.NeedID = tblPositionNeeds.NeedsID) ON tblPositions.PositionID = tblPositionNeeds.PositionID;
It has two texts boxes on it [PositionID] and [NeedID]
fsubPosSkill Record Source is qryPositionSkills
Code:
SELECT tblPositionSkills.PositionSkillID, tblPositionSkills.PositionID, tblPositionSkills.SkillID, tblSkills.SkillName, tblPositionSkills.SkillValueID, tblSkillValue.SkillValue
FROM tblPositionSkills, tblSkills, tblSkillValue;
It has two combo boxes, cmbo1 and cmbo2.
cmbo1: control source is SkillID and Rpw Source is qrySkill
cmbo2: control source is SkillValueID and Row Source is qrySkillValue
when I open frmPositions, the subform shows only the combobox label in the header, there is no combobox that allows me to select Skill or Skill Value.
View 1 Replies
View Related
Apr 2, 2014
I have a main form that identifies a Client File: frmClientFile (Single Form)
On the main form is a subform for Cases: subfrmCases (Continuous form)
Also on the main form below the Cases subform is a tab control that contains additional subforms to view/update different aspects of a case: subfrmCaseClients (Continuous Form).
The way this operates is that the user first chooses a file using a combobox on frmClientFile.
The subfrmCases is linked to the main form (using the caseFileID) and filters correctly.
The subfrmCaseClients is then linked using some hidden text fields on the main form (using caseFileID, CaseID) and this filters correctly.
On subfrmCaseClients is a combobox (cboClientID) that I need to requery based upon the Case Row selected on subfrmCases.
I have tried numerous combinations of options to force the requery by trying to apply a macro on the following:
OnCurrent, OnSelectionChange, OnChange and AfterUpdate events to no avail.
If I hit F5 to refresh the entire page the combo box gets updated as expected.
How do I get the Requery to work programatically? Is this even possible?
View 2 Replies
View Related
Aug 17, 2015
I am trying to use a combo box to refresh a subform. I am working with Access 2007 as my front end and SQL 2014 on the back end. On my main form called frmDealers I have an unbound combobox where the user selects a dealer and then text boxes are populated with address and phone number. I have it unbound because I don't want the user to make changes to the dealership at this point. On the subform I have people that work at the dealership. That form is called sfrmDealerContacts. The subform could have multiple contacts for each dealership. The subform is bound to a query called qryDealerContacts. I have it bound to a query because I want email hyperlinks in the table too. I tried searching the forum and found this thread number 279791.
After searching I added:
Form_sfrmDealerContacts.Form.RecordSource = "SELECT * FROM dbo_DealerContact WHERE DealerID = " & Me![cboDealerName].Column(1)
But this brings up an input box so I must have something wrong. My code is:
Private Sub cboDealerName_AfterUpdate()
Me.txtDealerAddress = Me![cboDealerName].Column(2)
Me.txtDealerAddress2 = Me![cboDealerName].Column(3)
Me.txtDealerCity = Me![cboDealerName].Column(4)
Me.txtDealerState = Me![cboDealerName].Column(5)
[Code] .....
View 2 Replies
View Related
Jan 30, 2015
I have a form with a combo/dropdown on the MAIN form and a child subform.I have designated a Master /Child link of ID to FGId respectively linking the combobox with the subform.
However, when a select an item from the combobox I expect to see the subform datasheet refresh accordingly. This is not happenining , even when I force a refresh call on the subform control itself....?
View 1 Replies
View Related
Feb 14, 2014
I've got an access front-end database running on Access Runtime on a Terminal Server with a back-end on a data server both of which are hosted by a remote company and accessed via Terminal Services. All is well and the database is working fine but because this is a cloud system I need a way that I can easily delve into the tables on the back-end without copying it down locally from the cloud each time (I've set up an "administrator" option so that this would be available to limited users). I've got the front-end forms working as I'd like but I have quite a few tables and I'd like to avoid creating a separate form for every table. I've been able to populate a combobox on my form with the list of linked tables and I'd like that when I choose a particular table from this list that the whole table is then visible within a subform below in case I need to manually intervene with the data for any reason.
View 1 Replies
View Related
Jun 25, 2015
I am setting up a subform in continuous with combobox, but when I enter the data on the first combobox, it duplicated to all the following records. Is there a way to stop that?
View 2 Replies
View Related
Sep 10, 2014
I am currently building a main form with a combo box control, two subforms and a hidden text box (optional). There are many different references to be made between tables, queries and forms to get to the result I am after, and I have tried many different codes from other people’s queries that seemed close to mine; however, had no luck getting my subforms to work as I’d like.
The names of my controls/forms/queries are as follows…
Main Form: frm_UReport
Subform 1: sbfrm_Query1 (refers to Query1, which refers to and calculates on tbl_C)
Subform 2: sbfrm_Query2 (refers to Query2, which refers to and calculates on tbl_S)
ComboBox: cmbo_ProductList (refers to tbl_ProductList)
TextBox: txt_ProductCode (refers to cmbo_ProductList, column 0)
I want the two subforms to run their respective queries after a Product Code has been chosen from the ComboBox. The relationship between the tables is one-to-many, from tbl_ProductList to each of tbl_S and tbl_C; with ProductCode as the primary key in tbl_ProductList.
Currently, I can choose a Product Code from the combo box, and it populates the text box successfully, but nothing happens in the subforms.
The codes I have worked with so far are as follows…
sbfrm_Query1 (Record Source):
SELECT Query1.ProductCode, Query1.PurchaseDate, Query1.ExpiryDate…
FROM Query1 WHERE (((Query1.ProductCode)=[Forms]![frm_UReport]![txt_ProductCode]));
sbfrm_Query2 (Record Source):
SELECT [Query2].[ProductCode], [Query2].[ProductDescription], [Query2].[PurchaseDate], [Query2].[AverageCost]…
FROM Query2;
[Code] ....
I have started working on integrating sbfrm_Query1 into the main form first, which is why the code looks different between the two subforms. Once I have it working, I will translate the same format to sbform_Query2.
Due to all the different levels of references that need to be made, I am having trouble identifying which (or if all) of the codes are incorrect.
View 8 Replies
View Related
Jul 17, 2013
I have a mainform in access with a subform (continous form mode). The subform's source is a query that has a criteria parameter that gets the values from a combobox in mainform.(list of projects) Combobox name= cmbProject
It works fine for some records (old records), so when I migrate new data into the table the comboxbox display the new data but when I select the record to be dispayed on the subform, the record or data is not in the subform, so the record is in blank.
View 1 Replies
View Related
Feb 25, 2014
I have a continuous subform of 'static data' whose record source is a SELECT query across multiple linked tables.
Most of the fields are locked and purely there for information purposes but I need to be able to allow end users to change one particular field in each record if they need to - choosing a value from a predetermined list (i.e. one of the tables)
Usually, when I need to do this, I add a command button to the subform and use that to open a separate pop-up form specific to that record, from which the user can make whatever changes they like and then update the record. So in this scenario, a simple unbound combobox linked to that table, on that separate form, would work quite easily.
But I would prefer if users could make their changes directly from the subform without (yet another) pop-up form required each time, to make managing these records more fluid and less time-consuming.
So my thought was to use a bound textbox in the continuous subform (to take the existing value for each record), hide it and overlap it with a visible unbound combobox whose row source is the table of available options and whose default value is driven by the hidden bound textbox. And then to use the AfterUpdate event of the combobox to run the UPDATE query on the record in question.
However, while the combobox is getting the correct default value and the correct list items, I can't make a selection from it. Now I am aware there are 'issues' with using a combobox in a continuous subform but I was hoping I could circumvent them by not binding it.
View 14 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
