Real-time updated list box, query as row source.

[KingBroil]

Hi,

I have a form with a sub-form. The form displays some items, each items have a "category" attribute. One Item can be in more than one category, and I set this up using a many to many relationship. Now, on the sub-form, I have a list box which displays all the categories to choose from, and a button to add a category to an item. Besides is another list box which displays the categories the item is actually in. When the user choose and click the button, I want Access to add the category to the list of categories the item has in the second list box.

So far I have a query based on the "linking table" that updates itself whenever a new item is selected and displays only the categories the item selected is in, which is working great. The second list box has this query as row source, but the list does not change if I change item. I tried many things, including Requery function on the list box row source, as well as a macro with the Refresh function. I couldn't have the list updated.

I'm out of ideas for tonight?

If anyone could point me a direction on this, I would appreciate a lot?

KingBroil

 

[John Big Booty]

The sample here may give you some pointers.

 

[KingBroil]

Thanks John Big Booty, very helpfull link. It's very similar to what I need to do.
However, I still can't get my list box to update. Here's more info:

Row source SQL code for my problematic list box:

SELECT qyEquipement_Categories.EquipementID, qyEquipement_Categories.CategoriesID, qyEquipement_Categories.CatDesc, qyEquipement_Categories.ID
FROM qyEquipement_Categories
ORDER BY qyEquipement_Categories.[CatDesc];

Here's the SQL for the query that the list box is based on:

SELECT tblEquipement_Categories.EquipementID, tblEquipement_Categories.CategoriesID, tblCategories.CatDesc, tblEquipement_Categories.ID
FROM tblCategories INNER JOIN tblEquipement_Categories ON tblCategories.ID = tblEquipement_Categories.CategoriesID
WHERE (((tblEquipement_Categories.EquipementID)=[Forms]![frmEquipement]![ID]));

And heres my On-change event on the equipement ID on my form:

Private Sub ID_Change()

'This one is working, whenever I choose another item from the list, the query is updated after refresh.
Me.qyEquipement_Categories.Requery

'This is the list box that needs to "refresh" when another item choosen, which does not work.
Me.listItemCat.Requery

End Sub

I've attached a couples images in case it could help.

This is what I have now, after trying many different configurations so I understand I might have drifted further away from the solution.

Your time is much appreciated,

KingBroil