Filter ListBox using Combo box

[rayape]

All -
Please see the attached file. When I make a selection using the Combo box, the Listbox goes blank. I need the Listbox to filter data based on selection made using the Combobox.
What am I doing wrong? Need your help, please.
Thank you.

 

[pbaldy]

You're providing an incomplete SQL statement:

WHERE [StoreID]='123 Blvd'

There is nothing in the Tag property to add the SELECT...FROM part.

 

[Cronk]

Your code is generating invalid SQL as the row source for the list box.

If you check SQL for the list box in design mode, you'll see it is

SELECT Products.ProductID, Products.Product, Products.StoreID FROM Products;

As me.list0.tag is a null string what you are generating as the row source for the sql is

Where StoreID='ABC Road'

 

[Cronk]

Paul, you've done it again. I'll have to train harder

 

[pbaldy]

Fastest fingers in the west.

 

[rayape]

I am a beginner’s beginner. Sorry to bother, but if you don’t mind, would you please tell/show me how to fix the problem to make it work? Thank you.

 

[pbaldy]

Was there no explanation where you got the code? The intent appears to be that the portion of the SQL prior to WHERE is put in the tag property of the listbox.

 

[pbaldy]

In other words:

SELECT Products.ProductID, Products.Product, Products.StoreID FROM Products

 

[rayape]

Was there no explanation where you got the code? The intent appears to be that the portion of the SQL prior to WHERE is put in the tag property of the listbox.

I used the idea from here.

Get a Combo box to filter data in a Listbox

Hi, On FrmTrack I have a listbox called [SearchResults] - the row source is: SELECT [QRYTrack].[TrackID], [QRYTrack].[Status], [QRYTrack].[Percent], [QRYTrack].[System], [QRYTrack].[SystemID], [QRYTrack].[Title], [QRYTrack].[VNumber], [QRYTrack].[Protocol], [QRYTrack].[VersionN]...

www.access-programmers.co.uk

 

[pbaldy]

You missed this in the first reply:

First copy the rowsource string into the tag property of the listbox.

 

[pbaldy]

By the way, the products table should be storing the ID of the store (1) not the text value (ABC Road). That's basically the point of the ID field; it's what you use in other tables.

 

[pbaldy]

I'm sorry, I didn't notice you were using a lookup field, which carries its own baggage:

The Access Web - The Evils of Lookup Fields in Tables

 

[rayape]

By the way, the products table should be storing the ID of the store (1) not the text value (ABC Road). That's basically the point of the ID field; it's what you use in other tables.

Ok. How would I do that? Sorry, these questions are perhaps too fundamental. Thank you.

 

[pbaldy]

It's almost dinner time here, let's just fix what you have for now, look at improving it later. Put this in the tag property of the listbox:

SELECT Products.ProductID, Products.Product, Products.StoreID FROM Products

and change the code to this:

Me.List0.RowSource = Me.List0.Tag & " WHERE [StoreID] = " & Me.Combo2.Column(0)

Note the change in column and the dropping of the single quotes, required because of the lookup field.

 

[arnelgp]

here is another solution.

 

[rayape]

Thank you, cronk, pbaldy and arnelgp.
Appreciate your help.

 

[pbaldy]

We were happy to help.