 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / November 2008Filter by form using a multiselect listbox
Hi All, I have been able to build a query that is filtered by a selection in a Listbox, but now i want to use a multi select list box so that two or more items can be included in the query. I constructed the query pointing to the Listbox but all I get is an empty dataset. How can this be accomplished? As always your help is appreciated. Regards, John Dumay Hi John, For a multiselect listbox to work as a filter you need to create another query based on the listbox. Put this code for execution between the selection of the listbox and running your main query ****Start of code***** strSQL = "SELECT * FROM YourTable" 'this is the table that the listbox is built from. For i = 0 To List1.ListCount - 1 If List1.Selected(i) Then strIN = strIN & "'" & List1.Column(0, i) & "'," End If Next i strwhere = " WHERE Yourfield in (" & Left(strIN, Len(strIN) - 1) & ")" strSQL = strSQL & strwhere MyDB.QueryDefs.Delete TempQuery Set qdf = MyDB.CreateQueryDef(TempQuery, strSQL) *****End of Code***** This will create a query named TempQuery which will contain the value selected in the multiselect listbox. Just link this into your existing query for it to act as the filter. HTH Regards, Nick > Hi All, > [quoted text clipped - 8 lines] > > John Dumay Forgot to put the declarations at the start of the code!!! ****Start of code***** Dim MyDB As Database Dim qdf As QueryDef Dim i As Integer, strSQL As String Dim strwhere As String, strIN As String Set MyDB = CurrentDb() strSQL = "SELECT * FROM YourTable" 'this is the table that the listbox is built from. For i = 0 To List1.ListCount - 1 If List1.Selected(i) Then strIN = strIN & "'" & List1.Column(0, i) & "'," End If Next i strwhere = " WHERE Yourfield in (" & Left(strIN, Len(strIN) - 1) & ")" strSQL = strSQL & strwhere MyDB.QueryDefs.Delete TempQuery Set qdf = MyDB.CreateQueryDef(TempQuery, strSQL) *****End of Code***** > Hi John, > [quoted text clipped - 39 lines] > > > > John Dumay Hi, Thanks for the help. The making of a query really helped. I did have a couple of problems with the code in that it was suited for a single selection list box not a multi selection list box. In this case you need to cycle through the array of selected items as shown in the code below. I alsos added an On Error Resume Next when deleting the Query just in case it didn't exist because it kept throwing an error when i first ran it. Hope this adds to your solution. *************************************************** Private Sub lstDisplayRows1_AfterUpdate() Dim MyDB As Database Dim qdf As QueryDef Dim i As Integer, strSQL As String Dim strwhere As String, strIN As String Dim ctl As Control Dim frm As Form Dim varItm As Variant sDocName = "qryTempForDataFilters" Set MyDB = CurrentDb() Set frm = Forms!frmBuildDataTables Set ctl = frm!lstDisplayRows1 strSQL = "SELECT * FROM tblBO_Filter1_Row_Items" 'this is the table that the listbox is built from. For Each varItm In ctl.ItemsSelected strIN = strIN & "'" & ctl.ItemData(varItm) & "'," Next varItm strwhere = " WHERE Row_Category in (" & Left(strIN, Len(strIN) - 1) & ")" strSQL = strSQL & strwhere On Error Resume Next MyDB.QueryDefs.Delete sDocName Set qdf = MyDB.CreateQueryDef(sDocName, strSQL) End Sub ********************************************************* John Dumay > Forgot to put the declarations at the start of the code!!! > ****Start of code***** [quoted text clipped - 62 lines] > > > > > > John Dumay Hi John, Glad to have been of some use. I actually use the code I posted (albeit a little more elaborate) for a report filter, so it does actually work with multiselect listboxes. If you add a union 'All' to the listbox source e.g. "SELECT * FROM tbl UNION SELECT 'All' FROM Tbl;" then it is possible to state If me.listbox = 'All' then strSQL = "SELECT * FROM tbl;" Else 'Run though looping statement to build strSQL End if This would negate the need to select everything in the listbox (if that circumstance arose). As I use 6 multiselect on one form to filter my reports the "All" entry is very useful. Regards, Nick > Hi, > [quoted text clipped - 111 lines] > > > > > > > > John Dumay Biz Enhancer, Thanx for the code, it was very useful. I'm going to be a slight pain but basically I want to use two multi-list boxes and the 'AND' logical operator to filter a query. I've got some other code that allows me to filter using one multi-list box but I can't seem to get two to work. Can you please help me out?! Rana > Forgot to put the declarations at the start of the code!!! > ****Start of code***** [quoted text clipped - 62 lines] > > > > > > John Dumay Hi Rana, It is actually really simple using the code below. ****Start Code**** 'Create a new tempquery for each listbox "Don't forget the declarations strSQL = "SELECT * FROM YourTable1" 'this is the table that the listbox is built from. For i = 0 To List1.ListCount - 1 If List1.Selected(i) Then strIN = strIN & "'" & List1.Column(0, i) & "'," End If Next i strwhere = " WHERE Yourfield in (" & Left(strIN, Len(strIN) - 1) & ")" strSQL = strSQL & strwhere MyDB.QueryDefs.Delete TempQuery1 Set qdf = MyDB.CreateQueryDef(TempQuery1, strSQL) strSQL = "SELECT * FROM YourTable2" 'this is the table that the listbox is built from. For i = 0 To List2.ListCount - 1 If List2.Selected(i) Then strIN = strIN & "'" & List2.Column(0, i) & "'," End If Next i strwhere = " WHERE Yourfield in (" & Left(strIN, Len(strIN) - 1) & ")" strSQL = strSQL & strwhere MyDB.QueryDefs.Delete TempQuery2 Set qdf = MyDB.CreateQueryDef(TempQuery2, strSQL) **** End Code**** As long as you are creating a different tempquery for each listbox you can use them to filter another query. eg SELECT tbl.fld1, tbl.fld2 FROM tbl INNER JOIN tempquery1 ON tbl.fld1 = tempquery.fld1INNER JOIN tempquery2 ON tbl.fld2 = tempquery.fld2; (yes I know the syntax is not right but it is a concept rather than example) That should get you on the way. Don't overlook the simple solutions however, sometimes it is more expedient to filter one listbox based on the selections of another listbox on the same form to achieve similar results and a combination of that approach and the code above can be very powerful. Regards, Nick. > Biz Enhancer, > [quoted text clipped - 75 lines] > > > > > > > > John Dumay Hey Nick, Thanx for your prompt response and the code however, I didn't quite get the join section. SELECT tbl.fld1, tbl.fld2 FROM tbl INNER JOIN tempquery1 ON tbl.fld1 = tempquery.fld1INNER JOIN tempquery2 ON tbl.fld2 = tempquery.fld2; is tbl.fld1 = the table name and field name of the temp query or the original table? also, do where do I include the field that initially joins the two fields together? Basically, I've filtered "skills" and "months" tables using the multi-list boxes which are linked together (using relationships) by a "ID no". I want to join the two temp queries (created when filtering) so that only the "ID no.", "skills" AND "months" I've filtered come up together in a 'final query'. Can you please help?! Thanx Rana > Hi Rana, > [quoted text clipped - 131 lines] > > > > > > > > > > John Dumay > Hey Nick, > [quoted text clipped - 152 lines] > > > > > > > > > > > > John DumayOkay, the way it works is this. In the query 'tbl' is the table you wish to filter. The temp queries are your filters. When you run the code after making the selections in the (multiselect) list boxes, the main query, which provides your results, will filtered based on the selection from your listboxes. E.g. Tbl1 fields: ClientID Clientname HairColourID ShoeSizeID SuburbID HairColourTbl fields: HairColourID HairColour ShoeSizeTbl fields: ShoeSizeID Shoesize Listbox1 Rowsource = "SELECT * FROM HairColour" Listbox2 Rowsource = "SELECT * FROM ShoeSize" When the code runs, it creates a tempquery from Listbox1 selections and a tempquery from Listbox2 selections. Tempquery1 would have: 2,Green 5,Black 7,Purple Tempquery2 would have: 3,US12 8,US5 The query that provides for results would be: SELECT Client.ClientID, Client.ClientName, Tempquery1.HairColour, Tempquery2.ShoeSize FROM Client (INNER JOIN HairColour ON Client.HairColourID = HairColour.HairColourID) INNER JOIN ShoeSize ON Client.ShoeSizeID = ShoeSize.ShoeSizeID; Therefore, the result will be all clientIDs, ClientNames, haircolour, and shoesize WHERE (haircolour is Green, Black, Or Purple) And (ShoeSize is US5 Or US12). Changing the Join Structure to LEFT or RIGHT joins will allow some looser filtering. Hopefully all that makes more sense. Regards, Nick. Nick, Thanx that cleared up alot! But now I've got another problem. I can't seem to be able to actually test the code at all! I keep getting an error box saying [Compile error" Expected: Case] and the table.fielname that's comes straight after the SELECT gets highlighted. Why does it do that? I've tried to get help using Microsoft Help and I've gathered that I should use a Case statement after the SELECT. Regards, Rana. Please bare with me, I'm new at programming in Access. > > Hey Nick, > > [quoted text clipped - 208 lines] > Regards, > Nick. Hi Rana, No excuses or apologies, we all have to start learning from somewhere. This forum has been a huge help to me in the past. There are some very clever programmers contributing here just to help people like us. The error and help are actually correct when using Case statements instead of IF statements. However, the problem here is that the SELECT statement is actually an SQL statement and so we need to tell the program that by creating a query definition. If this is going to be reused often for the same report, then it is probably better to create a query using the SQL SELECT statement. This means that the SELECT statement is not actually in the code and your last action in the code can be something like - DoCmd.OpenReport "MyReport" where the report is based on that query. Regards, Nick. > Nick, > [quoted text clipped - 223 lines] > > Regards, > > Nick. Nick, It works! I finally got it to work! Now I've got to brave adding another multiselct box but I'm assuming that shouldn't be too difficult if I use the same concept. Again, thank a bunch for all your help and patience. Rana > Hi Rana, > [quoted text clipped - 246 lines] > > > Regards, > > > Nick. Hi guys, Another NOOB here! please help. I've got the code (used Nick's above) creating the TempQuery but cannot run it. I get the "Data type mismatch" error. In design view the data from my list box is where it should be. Looks like this "In ('9','14','10','11','12','13')" without the end quotes in the criteria row. What am I missing? Any help appreciated. Sean > Hi guys, > [quoted text clipped - 7 lines] > > What am I missing? Any help appreciated. If the field you are filtering is numeric then lose the single quotes around the numbers. If it is text you need to replace them with double-quotes.  SignatureRick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com Rick, Too easy, works great, thanks mate. Cheers, Sean Anyone, I have 2 queries that I use to sum data that is filtered by the temp query. I then join these queries in the record source of a subform which lies in the main form where my listbox resides. If I open subform the on it's own it's fine, info is updated and correct, but I can't get the subform to work using .Requery on after update of the listbox or using a button to execute. Look as if the .Requery is working but the data isn't updating. Any suggestions? Any help/suggestions appreciated Sean |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.