asked on

Cascaded filtering textbox to combobox

I have a form with a bound Textbox (txtCat) that I want the the value in that textbox to filter the values in an unbound combobox (lstSubCatItem) I created a query (qryFilterSubcat)

SELECT Subcategory.SUBCAT_ID, Subcategory.SUB_CATEGORY
FROM Category INNER JOIN (Subcategory INNER JOIN CAT_SUBCAT ON Subcategory.SUBCAT_ID = CAT_SUBCAT.SUBCAT_ID) ON Category.CAT_ID = CAT_SUBCAT.CAT_ID
WHERE (((Category.CATEGORY)=[Forms]![frmEnterItems]![txtCat]));

Open in new window

and if I i put the query in the row source of the combobox it displays the filtered records based on the textbox value, however when i move to a new record it still displays the values for the previous record. I tried to use VBA on the forms on current event but it displays nothing in the combobox.

Private Sub Form_Current()
Dim ListFilter As String

    ListFilter = "SELECT Subcategory.SUBCAT_ID, Subcategory.SUB_CATEGORY" & _
"FROM Category INNER JOIN (Subcategory INNER JOIN CAT_SUBCAT ON Subcategory.SUBCAT_ID = CAT_SUBCAT.SUBCAT_ID) ON Category.CAT_ID = CAT_SUBCAT.CAT_ID" & _
"WHERE (((Category.CATEGORY)=" & [Forms]![frmEnterItems]![txtCat] & " ))"
   Me.lstSubCatItem.RowSource = ListFilter
    Me.lstSubCatItem.Requery
End Sub

Open in new window

PatHartman

using the first method, try requering the combo in the Current event
Me.lstSubCatItem.Requery

Fred Webb

ASKER

pat,
thanks that worked, however a new wrinkle, I have 3 additional controls that i am trying to cascade filter. The value in the combobox (lstSubCatItem) to filter listbox (lstAttrib) based on a similar query that filters the lstSubCatItem combobox but it acts as though it cant see the value in the combo box, i know the query works because if i hardcode a value it displays the value.

SELECT Attribute.ATTRIB_ID, Attribute.ATTRIBUTE, Subcategory.SUB_CATEGORY
FROM Subcategory INNER JOIN (Attribute INNER JOIN SUBCAT_ATTRIB ON Attribute.ATTRIB_ID = SUBCAT_ATTRIB.ATTRIB_ID) ON Subcategory.SUBCAT_ID = SUBCAT_ATTRIB.SUBCAT_ID
WHERE (((Subcategory.SUB_CATEGORY)=[Forms]![frmEnterItems]![lstSubCatItem]));

Open in new window

PatHartman

In the AfterUpdate event of each list/combo, you need to requery the next lower combo.

I also clear out any existing value in all the lower combos to avoid saving inconsistent data. So, if combo1 is changed, I clear combo2 and combo3. If combo2 is changed, I clear combo3. You could use If statements but from long experience with creating CICS transactions that have to execute in under 4 seconds, I have naturally adopted the most efficient coding techniques and the machine language for setting a value in a variable is faster than first testing and then setting or not. So, in this case, I just set the lower levels to null. Someone published a comparison for VBA instructions but I can't lay my hands on it at the moment. Suffice it to say, regardless of what programming language you are using -- a = b is the least costly expression.

Fred Webb

ASKER

I added that but it still does not see the value in the lstSubCatItem combobox to populate the (lstAttrib) listbox

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fred Webb

ASKER

that was it pat, i was linking it to the text not the id

PatHartman

Excellent. If it all works now, please close the question.