Modules & VBA :: Filter Subform With Listbox Selections
Feb 18, 2015
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 will have a ListBox on the form with “Multi Select” set to either simple or extended. I have a collection of documents which must be recorded in a database. Some documents may have only one author, but could have more. Same with the recipient or copied-to.
Ideally it would be nice to have the ListBox on the left, selected one or more from the list and then pass them to one of the three fields by a command button, sort of like:
I would like to have all of the names selected from the ListBox as a string, but fully understand the problems associated with normalization. Other posts have suggested subforms. So, the question is how to select from the ListBox and then pass the possible multiple selections to discrete fields on a subform that would display, say three fields and which would get away from the normalization issues:
The secondary issue will be that there will be subsequent names which are not found until actually in the process of document review which will create a need to update the ListBox, which I know is not akin to a ComboBox NotInList function.
If there are any thoughts or coding out there which will help, it’d be appreciated. Lawguy
I have a multi select list box which I am trying to use to open another form and apply a filter based on the user's multiple selections.
List box - lstCatergories Main Form - frmSuppliersSummaryCategories Subform on Main Form - frmSuppliersSummaryCategoriesSubForm
The Subform contains the text box CategoryID
Here's my code which nearly works...
Code: Private Sub cmdFilterSuppliers_Click() On Error GoTo Err_cmdFilterSuppliers_Click
Dim strWhere As String Dim ctl As Control Dim varItem As Variant
[Code] .....
My problem is that the control "CategoryID" is on the subform and I'm having massive problems trying to reference it. This is the part of my code that won't work:
I currently have a combo box that filters form based on specialist (an employee using the system), and this works fine:
Private Sub FilterSpecialist_AfterUpdate() Me.Filter = "SpecialistAssigned = '" & Me.FilterSpecialist & "'" Me.FilterOn = True End Sub
When Specialist Assigned name is selected, it only shows their cases (records of the form that are assigned to them).
However, now I'm trying to also filter based on only the selected Specialists Open cases. I have a combo box at the bottom of the form that saves whether the Case is Open or Closed, (creatively) called CaseOpenClosed:
Private Sub FilterSpecialist_AfterUpdate() Me.Filter = ("SpecialistAssigned = '" & Me.FilterSpecialist & "'" And CaseOpenClosed = "Open") Me.FilterOn = True End Sub
I've tried this based on me searching the forum but it doesnt filter at all anymore, and no error either. I've tried other ways but get errors. What is wrong with this?
I have a training matrix that lists employee names and certifications on various operations. The objective is to choose an operation and run a query to display everyone who is certified on that op. There are additional variables.
Code: Name EMP ID OP1 OP2 OP3 OP4 OP5 ----------------------------------------------------------------------------- John Doe 526261 C C C Bob Doe 555622 C C C Sheila Doe 066600 C C C
Okay that looks about right for the data itself. The listbox has all the ops, you choose an op and hit a button and it goes and finds everyone who has a 'C' in that op column and pulls their record.
I have a listbox which is populated by a query from a table.
I also have a sub which allows users to quickly select multiple items from the listbox (based on a pre-defined criteria) rather than scrolling through and selecting them manually / individually
The sub works fine but when it is finished, the listbox is automatically scrolled to the bottom (or, rather, to the last selected item)
I would like to have the listbox scroll back to the very top once all of the appropriate items have been selected.
(I could loop through the listbox items in reverse - or 'bottom-up' - in the code but that would only have the desired effect if the topmost item was selected. I want it scrolled to the top regardless of whether the topmost item is selected or not).
I'm trying to hash two scripts I've found into 1 functioning filter, however I'm still relatively new to vba and can't figure out how to get this working.
I'm trying to use Allen Browne's Search Criteria:
with another snippete of code I found here:
Code: 'Purpose: This module illustrates how to create a search form, _ where the user can enter as many or few criteria as they wish, _ and results are shown one per line.
[Code]....
It's the date part I'm having trouble with, the rest of the search criteria work fine without the date, but I can't get it working when I try to modify and merge the date sections of each code.
Also I'm using a listbox for the "Yesterday";"Last 4 days";"Last 9 days" and not a combo box.
The main form has textbox & a Listbox with which I filtered a datasheet inside a sub form. Everything worked fine only the listbox [Discipline] is not working !! it cuase Run-time error: 3831 - The multi-valued field "[Category]" cannot be used in a WHERE or HAVING clause.
So how do I filter a multivalued Listbox field [Discipline] ?
Private Sub cmdSearch_Click() 'On erorr GoTo errr Me.tblFLM_subform1.Form.RecordSource = "SELECT * FROM tblFLM " & BuildFilter Me.tblFLM_subform1.Requery Exit Sub errr: MsgBox Err.Description End Sub
I have a listbox where the rowsource is a SQL statement. I didn't know if I should try that or just use a query for what I am doing. The listbox is based on our client table, which is a huge number of people, and is showing 3 out of 5 fields.
I have two text boxes, one for first name and one for last name.
I would like the listbox to filter as I search by either field OR by both. Right now, I have the on change event for each with a SQL statement with a Like '*" & Me.textboxname.text & "*' and then a Me.clientlist.rowsource= at the end. So I can get each text box to filter the listbox individually, but I can't get it to filter with both. I think it is a matter of the focus changing, but I could be wrong.
I have a listbox with about 8 different choices. i want to be able to query what is selected in this list box but I need to be able to select more than one choice so that my employees don't have to create multiple forms for the same item. Is there any way to select more than one option? It would help greatly even if I have to add code in VB I will figure out how to do it, thank you.
This must be pretty common but I cann't figure it out.
There is a listbox that multiple selections are allowed. This listbox is bounded with a table field. All selections must be stored. I don't mind if each selection is stored as a different record.
I made a listbox in which get's it's source from a field in a table named Soft. The field is named Software1.
This field contains a list off software that could be installed on systems. I allready can select multiple selections from the listbox but what I want to do is the following.
I want to press a button, and after I press the button I want that the selected Software is added to a new listbox which show the Software installed. The software in the second listbox would also be stored inside a Table so that I could print them out in Reports.
To simply explain what I'm trying to do:
SoftwareListbox >> Make multple selections>>Press button>> Adds selected software to InstalledListbox which stores in the softwarelist installed in a Table.
I really hope you guys understand what I'm trying to do. And sorry for my bad english, should have paid better attention during English class ;)
I have a simple order entry system working with a subform containing combo box where a client selects various products from a table. These are recorded in separate table which allows for subsequent updating of the orders placed.
Now I want to refine this so that the user may make multiple selections as it is a bit laborious making 'n' single selections via a combo based subform.
Only a list box allows multiple selections but I would like some guidance on how to store and be able to recall and update the selected items.
I have a listbox from where i can make selections and generate a report for the selected values , the problem is the selections are limited to 28 selections, if i try to select even one more after that i get an error:
" Run time error '7769' " " The filter operation was cancelled. The filter would be too long."
Now is there anyway to make this limited selection changed to a higher value or unlimited??? so i can select as much choices as i can , even all of them if i want? Does anyone have an idea , i looked throught the properties of the listbox but no help.
I have a form that has names with a subform with information. The contacts are in a list box and the subform (in datasheet view) shows the phone number/email/etc.. of the selected contact person. I am using the form as a quick look up of a persons information. Before changing it to a list box, it was previously a combo box, and everything worked fine. i was able to requery the subform and the cooresponding info for the person would come up. However, its now a list box and i am having problems with the code to make this happen. I have a different button sending the names selected from the list box to a report...and that works fine, but i am obvisouly missing something to make it work with the subfrm requery. Below is the code i got so far. Any help would greatly be appreciated. its driving me nuts!
Private Sub QuickLookup_Click() Dim varItem As Variant Dim strWhere As String strWhere = "[memberID] = " For Each varItem In Me.MemberID.ItemsSelected strWhere = strWhere & Me.MemberID.ItemData(varItem) & " OR [memberID] = " Next varItem strWhere = Left(strWhere, Len(strWhere) - 17) DoCmd.Requery "subfrmqryindividual"
I have two unbound multi-select listboxes that saves selection to their respective table. It works just dandy. However, whenever I go back to the same record, the listbox is blank and apparently I have to instruct it to "retrieve" the previous selection from the table using the PK as criteria.This is far as I can get, though this code causes several errors and I'm not sure what I need to put in as an array for "For Each" statement. If anyone can guide me how to retrieve the old selection from the table and displaying on listbox, that would be great.If IsNull(DLookup("[ClientID]", "[tblClientAccommodations]", "[ClientID]=" & Me.ClientID)) = False Then Dim RecCount As Integer Dim Index As Variant RecCount = DCount("[ClientID]", "[tblClientAccommodations]", "[ClientID]=" & Me.ClientID) For Each Index In Me.lstReasAcc Me.lstReasAcc.Selected = (DLookup("CommunicationID", "[tblClientAccommodations]", "[ClientID]=" & Me.ClientID)) Next iThanks in advance.
My problem I am having is that in my database you can click around to different customers info on forms and from there you can click on a button to get to another form that lists all of the customers vehicles. From there I have a billing report that I created and I am trying to filter the report to get what I want on there.
I have been searching around and have found codes to filter reports so that right now I have it when I click on Billing Report it filters by the customers form i am on. But now where i am having some issues is that I am also looking to filter by a yes/no field so that anything clicked yes for that customer will be filtered to show on this report.
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 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
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!
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.
