Modules & VBA :: Filter Subform By Nth Column In Combobox?
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!
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.
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.
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.
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
I have a form that shows multiple rows of linked/child records.The form in question is the "frmFilterNumberTypeView" form. When a particular filter is used (installed on an automobile), I would like to be able to click on the corresponding "Installed 1" button left of the filter number, so that by code, I can reduce the Qty on Hand by One, and insert the corresponding filter ID to the History table, along with the date/time the filter was used.
how to reduce the Qty on Hand, nor how to do the updates to the History table, I'll figure that out myself over the next week or two (hopefully it won't take that long). What I'm trying to figure out here is how I can associate the red "Installed 1" button with the filter to the right of whichever button I press.
how to sort the filter column on this same form. I'm fairly certain that this would be very easy to do if the subform in question was populated by means of a corresponding query, but I'm afraid that if I go that route that I'll spend another 20+ hours trying to figure out how to get the proper filter records to match the filter manufacturer, not to mention having to next figure out how to link the table so that if I modify any particular filter record, that I'll be able to have the associated table update accordingly.
Is it possible to sort the filter column without the subform record source being a query?
I have a subform in datasheet view for tracking calls from clients. The Client Name is a combination of the first and last name. I want to create a text box that allows me to start typing in the client name and it will filter for only those Clients that match. Currently, I have the following code (below) for the textbox, however when I start typing in a client name all the clients disappear from the table. The client name is in a combo box so I don't know if that is making a difference.
Private Sub Text3_Change() Dim strFilter As String Me.Refresh
I have a form which has a combobox called Task_Ref which looks up values in a table column.
I would like to be able to set the tickbox value of tickbox called P1 to True if the combobox contains the word "test", each entry on the combobox selection may vary such as:-
Test number 1 Yesterdays Test
As long as the word "Test" appears I would like the above to happen?
I was thinking of something along the lines of:-
If InStr(Task_Ref.Value, "Test") > 0 Then P1.Value = True Else P1.Value = False End If End Sub
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!!
I'm filtering a sub form in datasheet using an option group frame, and the on click does exactly what I want it to do.However, Once I'm done filtering, I can either set the filter to all ...
Code:
Case 1 Forms!frmmain!frmtoplinelist.form.FilterOn= False Or click a Button "Remove Filter"
Code:
Private Sub btnClear_Click() Me.frmTopLineList.Form.FilterOn = False End Sub
(I've tried both references to the subform too... "Me." and "forms!etc. However, the subform filter isn't switching off.
I am a VBA beginner and am using Allen Browne's method for filtering a field in a subform: URL....It works well except that I cannot seem to figure out how to order the results when the subform field is filtered. I want to order the results by CompanyName.Here is the code I am using for changing the record source from the main form to the subform:
Code: Dim sSQL As String If Me.cboFilterFavorites = Null Or Me.cboFilterFavorites = "In Business" Or Me.cboFilterFavorites = "Out of Business" Or Me.cboFilterFavorites = "Unsure" Then If Me.RecordSource <> "qryCompanies" Then Me.RecordSource = "qryCompanies" End If
[code]...
I feel as if I have tried everything. I even changed the subform record source from a table to a query (qryCompanyTypes) so that I could add the CompanyName field and sort but to no avail. I have tried adding the CompanyName field to the SQL string but keep getting errors. I have tried simply using Order By in the SQL and still get errors.
I am trying to get my VBA to filter the subform, as currently it does nothing! I have copied it from a source on another forum and at the end of the VBA it originally opened up the query.
However I am trying to tailor this query so that instead of it opening the query I can have a datasheet on the form that displays the filtered records..
There are 3 multiselect listboxes
Here is my code
' This code uses ADO and ADOX and is suitable for Access 2000 (and later). ' A reference must be set to Microsoft ADO Ext. 2.7 for DDL and Security. Private Sub cmdOK_Click() Dim cat As New ADOX.Catalog Dim cmd As New ADODB.Command Dim qry As ADOX.View
I am trying to use this code to filter a continuous subform based on two criteria but getting a data type mismatch error.
It works fine if I filter by just one or another. Perhaps it is not possible
Code: Dim intSpouseEntityID As Integer intSpouseEntityID = Nz(DLookup("[EntityID]", "qryEntitiesLocations", "[ContactIDNumber] =" & Me.Spouse), 0) If intSpouseEntityID > 0 And Not IsNull(Me.subformContactsHomeAddress.Form.EntityID) Then MsgBox ("There are two spouse addresses please delete one and try again") DoCmd.Save DoCmd.OpenForm "frmContactAddresses", , , "EntityID=" & Me.txtEntityID Or "EntityID =" & intSpouseEntityID End If
It works as I expect to see in my Subform. Now I want to export the results out to excel.
The problem is that I want to only export what is actually being viewed on the subform. Not the underlying query that it uses which has many more columns that are not displayed on the subform.
Is it possible to use the result being displayed on the subform and make that into a temporary table and export that to EXCEL?
I have a navigation form which has two subforms on it (ie 2 tabs linking to different forms). I have got aq filter working for one of the forms but not on the other. This is using
If IsNull(Me.txtoperationscontract) Then MsgBox "Please type a contract number into the textbox!", vbInformation + vbOKOnly, "Incorrect Details" Else Me.OperationsSubform.Form.Filter = "[Contract]='" & Me.txtoperationscontract.Value & "' " Me.OperationsSubform.Form.FilterOn = True End If
The problem is that it does not work on subform B because the Contract field does not exist.
I am updating an existing database and I have a parent/child form and I want to filter the subform for the current parent form record? The two forms are linked together by woWorkOrderID/deWorkOrderID.
I have a form that uses a listbox. I can select multiple items and then update a subform. When I press a button with VBA and use the filter property on the subform for the first time, nothing happens. When I press the button a second time, it does - and it works thereafter with different selections. It only happens that first time, after loading the form and pressing the button for the first time.
I have tested that the list items are accessible via the vba variables and stepping through my code and the problem starts with the filter properties.
is not switched on first time (I have tried a requery and refresh statement after this). When I step through the code the first time, the FilterOn property remains false. A second button press and it is fine.
So I have a search form that I created that has also a subform (a continous form, that has a query as its source) where my records are listed. I started on making a Between dates filter where i would be able to pick dates and get the results of records that are in that range.
I put two text boxes in which I set format to Short Date so that I get the button to select the date from a calendar for each text box. Then I made a button that is only for this and added a Clickon code so that it filters my subform over the recordsource of the form. The problem is is that i get an error and it doesn't filter.
Code:
Private Sub btnDatumUpis_Click() Dim SQL As String SQL = "SELECT qrySearchV.VID, qrySearchV.MarkVoz, qrySearchV.ModelVoz, " _ & "qrySearchV.TipMot, qrySearchV.Regist, qrySearchV.VlaVoz, " _ & "qrySearchV.KorVoz, qrySearchV.KatV, qrySearchV.DatumUVoz, " _
I am a bit of a novice when it comes to Access, but have managed to create a form with a subform embedded and various filters to show different data within the subform, including a date range filter. The code I have used for these filters is as follows:
Private Sub Command40_Click() Dim strCriteria As String
This all works fine, but I'm wondering what I need to add to this code to make it so that if the date boxes are left blank, records from all dates are displayed. At the moment I have to enter dates in order for it to work properly.
I need to be able to use a combo box on a form to filter the records shown. The fields on the table are as follows:
ID No, Ref No, Start Date, Comp date, Price etc.
I have set the form up to run off a query that shows all records with a certain ID No. Each ID No has a number of reference numbers. I would like to use a combo box on the form so the user can select a Ref No and the form would be filtered to show only the details of that specific ref no.
I have a combobox (cbo_Breed) that Holds the Breed_ID, Breed and BreedType_ID.
On the form opening I want to look at the thrird column and if its a "1" then to do something. How do I get the form to look at the third column? This is what I have written (I know its wrong, but needed a starting point)
