Hi All,
   I'm hoping someone can help me out.
I am using Access 2000. I am trying to filter
contents of a ListBox. I have a table with,
at present, 24 items called Catagories. I have a second
table with 7000 items that relate to the catagories and
are in groups listed as 100, 200, 300... etc.
I have two forms that relate to the tables. When I
select on a catagory in the first form and open the second
form I want to see only the items in a particular group
in the ListBox. At present I get the whole table starting at
item 1. Any help would be appreciated.

Thanks.

dgold,

are your 2 forms linked together?
does the catagory table have a group?
there are many things to do here, but need to know more. The listbox on the second form, currently it doesn't sound like you have any prerequisites.
right now, if the forms are linked on category, the second form is going to show you all the records regardless of group. If you want to limit the items listbox to only those within a particular group, then add a group to your 1st form, and link on it also.

Kevin



dartcoach

Hi,

   I am not sure why you have a grouping on the items using 100, 200, 300 etc. You should have the items as belonging to a category. If the items are also part of a number grouping, is this within a category, or across all items. I would need to see a schema of this database.

   But basically here is how you do this:

I am assuming you have a table called tblCategory and one called tblItems that look like this:

tblCategory
CatID - PK
CatName - text
etc

tblItem
ItemID - PK
ItenName - text
CatID - FK
etc

On ONE unbound form (myForm), you have your combo box to select the Category (cboCat), and your listbox with Items (lstItems).

The cboCat is just a wizard that looks up the CatName and binds on the CatID.

The row source for lstItems is a query of the items that looks like this:

SELECT tblItem.ItemID, tblItem.ItemName, tblItem.CatID
FROM tblItem
WHERE (((tblItem.CatID)=[Forms]![myForm]![cboCat]))
ORDER BY tblItem.ItemName;

The AfterUpdate event for cboCat is:
'=====
Private Sub cboCat_AfterUpdate()
Me.lstItem = Null
Me.lstItem.Requery
Me.lstItem = Me.lstItem.ItemData(0)
End Sub
'=====

The On Current event for the form is:
'=====
Private Sub myForm_Current()
Me.lstItem.Requery
End Sub
'=====

The On Load event for the form is:
'=====
Private Sub myForm_Load()
Me.cboCat = Me.cboCat.ItemData(0)
Call cboCat_AfterUpdate
End Sub
'=====

This should work. I really need to see more of the schema.

HTH



mmcdonal

Hi mmcdonal,

    I tried your code and it worked great. Thanks
for the help.