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

skull52Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
using the first method, try requering the combo in the Current event
Me.lstSubCatItem.Requery
0
skull52Author Commented:
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

0
PatHartmanCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

skull52Author Commented:
I added that but it still does not see the value in the lstSubCatItem combobox to populate the  (lstAttrib) listbox
0
PatHartmanCommented:
Make sure the combos are linked on the correct fields.  Remember, the bound column is usually the first column and it is usually numeric but the visible column is usually text.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
skull52Author Commented:
that was it pat, i was linking it to the text not the id
0
PatHartmanCommented:
Excellent.  If it all works now, please close the question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.