If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Combo Box to filter List Box
Hello.
I have a list box "lstMembers" Row Source: SELECT DISTINCTROW [qryContacts].[CONTACTID], [qryContacts].[LAST_NAME], [qryContacts].[FIRST_NAME], [qryContacts].[State], [qryContacts].[TYPE] FROM qryContacts ORDER BY [qryContacts].[LAST_NAME]; And it returns the following: Last_Name First_Name State Type ------------------------------------ Doe John MD AA Does Dan FL CC Smith Mike CA AA I have a Combo box that contains all the "TYPES". Its Row Source is as follows: SELECT[LIST_TYPES].[TYPE_ID],[LIST_TYPES].[TYPE] FROM LIST_TYPES; How can I filter this List Box using the criteria in the Combo Box? In other words, if I choose 'AA' from the combo box, only the records for John Doe and Mike Smith show up. Any help would be appreciated. Thanks, m. |
#2
|
|||
|
|||
Combo Box to filter List Box
Mike
So you're saying that you want the source of the lstMembers to include the criterion from the combo box selection? Notice that the combo box's first column is the ID, and the second is the Type. In your listbox query, does qryContacts hold the TypeID or the Type? Once you get that sorted out, either use the query design window or modify the SQL statement directly to include a "WHERE" clause that points to the value in the appropriate column of your combo box. -- Good luck Jeff Boyce Access MVP |
#3
|
|||
|
|||
Combo Box to filter List Box
I almost have it working. I manged to use some existing
code that I wrote that performs another type of filtering process. It looks as follows: If Not (IsNull(cboType.Value)) Then If blnMultiple = True Then strcriteria = strcriteria + " AND [Type] like'" & cboType.Value & "*'" Else strcriteria = strcriteria + "[Type] like'" & cboType.Value & "*'" End If End If The only issue I'm having is that with this code, instead of putting the actual text of the combo box in the SQL line, its putting its numberic value. I assume this has something to do with the "cboType.Value" How do I write it so that it feeds in the text and not the numeric value into the SQL statement? Thanks for your help. m. -----Original Message----- Mike So you're saying that you want the source of the lstMembers to include the criterion from the combo box selection? Notice that the combo box's first column is the ID, and the second is the Type. In your listbox query, does qryContacts hold the TypeID or the Type? Once you get that sorted out, either use the query design window or modify the SQL statement directly to include a "WHERE" clause that points to the value in the appropriate column of your combo box. -- Good luck Jeff Boyce Access MVP . |
#4
|
|||
|
|||
Combo Box to filter List Box
Mike
Sorry if my first response wasn't clear. You need to remember that combo boxes typically have the ID in the first (but using 0-width ... non-display) column, and some other field (?Type) in the next column. The value you want to use is in the cboType's second column. NOTE: this is only true if, as my response discusses, you have stored the Type in your first table, instead of the TypeID. The more typical design is to store TypeID in your first table, and use a join/query to "see" the Type. If you don't change your table column, you could still resolve this. Use the .Column() method on your combo box to do this, something like: = Me!cboType.Column(1) The .Column() method is zero-based, so you'll need to count the cbo's query's columns starting with "0". -- Good luck Jeff Boyce Access MVP |
#5
|
|||
|
|||
Combo Box to filter List Box
Thanks for the reply. Your initial response sent me on
the right track and I got it working. All I had to do was change cboType.value to cboType.text and it returned the correct value (i.e. the actual text). Thanks for all your help. m. -----Original Message----- Mike Sorry if my first response wasn't clear. You need to remember that combo boxes typically have the ID in the first (but using 0- width ... non-display) column, and some other field (?Type) in the next column. The value you want to use is in the cboType's second column. NOTE: this is only true if, as my response discusses, you have stored the Type in your first table, instead of the TypeID. The more typical design is to store TypeID in your first table, and use a join/query to "see" the Type. If you don't change your table column, you could still resolve this. Use the .Column() method on your combo box to do this, something like: = Me!cboType.Column(1) The .Column() method is zero-based, so you'll need to count the cbo's query's columns starting with "0". -- Good luck Jeff Boyce Access MVP . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
combo box filter | suzy | Using Forms | 0 | June 18th, 2004 06:30 AM |
Formulas employing cells linked to a combo box drop list | Frank Kabel | Worksheet Functions | 3 | May 27th, 2004 04:44 PM |
Use Combo Box in forms to filter Records in a subform | Rod Burchby | Using Forms | 1 | May 25th, 2004 08:19 PM |
Advanced Filter List Range from within a Macro | Debra Dalgleish | Worksheet Functions | 2 | May 7th, 2004 12:26 AM |
How do you create a list in a Combo or List Box? | Arthur | Worksheet Functions | 1 | February 16th, 2004 08:32 PM |