Filtering a list box using a variable?

[Lobius]

Hi, To put it simply I need to filter information in a list box according to a particular field in a table.

I have tables "Table1" and "Table2". Table1 contains primary key "Key1", this is a foreign key in Table2.

At the moment I have used the following code to show all the records in Table2 in a list box (name List_Box1):

List_Box1.rowsource = "SELECT [Table2].[ID],[Table2].[Information],[Table2].[Date],[Table2].[Key1] FROM [Table2] ORDER BY [Date];"

In a previous form the user has selected a record in Table1, the value of Key1 (an identifying integer) is stored in a global variable called "Selected_ID"

I want List_Box1 to be filtered so that the only records from Table2 that appear have the same Key1 as Selected_ID. Thanks

[Stewart Ross]

All you need is a WHERE clause on your existing row source code:

List_Box1.rowsource = "SELECT [Table2].[ID],[Table2].[Information],[Table2].[Date],[Table2].[Key1] FROM [Table2] WHERE [Table2].[Key1] = " & Selected_ID & " ORDER BY [Date];"

-Stewart

[Lobius]

Excellent! It works thanks a lot, i've been playing around with applyfilter and things for hours with no progress.

[NeoPa]

You will need to ensure that the SQL for the ListBox is updated any time the global variable changes of course.

[NeoPa]

We get so many questions in here from people trying to do things completely the wrong way around. It's nice to get one from someone who's having trouble because they are thinking the RIGHT way, but Access decided to do things differently (It doesn't supprt filtering for form controls - just for forms & reports).