Combo Box to filter List Box

[Mike]

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.

 

[Jeff Boyce]

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.

 

[Mike]

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.

 

[Jeff Boyce]

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".

 

[Mike]

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.