Filtering List Boxes in sync with Forms

[Dumferling]

I want to understand if I am missing anything. My form is filtered in VBA with setting FilterOn and defining the filter (there are three variables that have to be filtered on - not all of them are present at the time of filtering). I have a list box on the form but to get it to reflect the same filter as the form, I have to set Rowsource which I am doing by using a SQL type statement (I copy the SQL statement from the listbox filter along with the WHERE statement as the criteria). It all works but I am wondering whether I am not missing a trick with the listbox and whether there is not a better way to do the filter than by using SQL statements? Can I,for example, link it somehow to the filter of the form? I am self taught in VBA and not particularly sophisticated so I am wondering if there is something that I am not understanding.

 

[theDBguy]

Hi. Not with a Listbox, I don't think so. As an alternative, you could try using a Subform and try to make it look like a Listbox. With a Subform, you can automatically filter the content by using the Linked Master/Child Fields properties.

 

[Dumferling]

Thank you. Always hopeful for a better option but at least I have it working!

 

[theDBguy]

Thank you. Always hopeful for a better option but at least I have it working!

I still hope someone else might tell us something we're not aware of. Cheers!

 

[cheekybuddha]

Hi,

I don't know whether this would work, but you could try setting the RowSource of your listbox to:
SELECT [ ... Fields ...] FROM YourTable WHERE IIf([Form].FilterOn, [Form].Filter, True)

Scratch all that - it won't work as it is.

hth,

d

 

[MajP]

I still hope someone else might tell us something we're not aware of. Cheers!

I listbox has a DAO recordset. You can apply a filter to a DAO recordset. So the answer is technically yes. If you want to see an application of this see here.

Developing Custom Classes to Simulate User Controls

Access OOP examples wanted Some people have shown interest in custom classes lately, and was asked for some more examples. I use custom classes extensively, but the most important use in Access is the ability to extend control functionality and basically simulate building a user control. Once...

www.access-programmers.co.uk

Is this easier than modifying the sql of a single listbox? Likely No. Is this easier to make a generic solution? Yes. This is how I am able to make a find as you type listbox / combobox with only a single line of code. I do not need any knowledge of the potentially complex query. In your case I doubt the bang is worth the buck. If you wanted a class that would link any combobox to the the form filter, then this would be how I would do it.