Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 23rd, 2005, 11:43 AM
Registered User
 
Join Date: Mar 2005
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default ListBox Filter

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.


 
Old March 23rd, 2005, 08:10 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old March 24th, 2005, 01:57 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old March 30th, 2005, 06:00 PM
Registered User
 
Join Date: Mar 2005
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi mmcdonal,

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
multiple Listbox values in another listbox terryv Excel VBA 0 June 27th, 2007 07:01 AM
Filter Help mcloum Classic ASP Databases 2 November 10th, 2005 05:40 PM
Filter Help mcloum BOOK: Beginning ASP 3.0 1 August 26th, 2005 03:10 PM
How to filter data? coolest_pie SQL Server 2000 7 May 7th, 2004 07:00 PM
I'm back :) Listbox var from listbox MichaelTJ .NET Web Services 2 October 21st, 2003 07:06 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.