Filter Listbox

Hello all.

Don't know if I'm doing this correctly, but I previously started a thread that went dead before I could get a solution:

http://access-programmers.co.uk/forums/showthread.php?t=139980

Click to expand...

Basically, I am trying to create a subform that allows a user to select criteria to filter records by. When the user hits the "go" button on that subform, I want the results to appear in a listbox on another subform. Both subforms appear on a main form.

I've attached an example db to try and explain what I'm trying to accomplish.

Any help would be greatly appreciated.

brsawvel said:

Basically, I am trying to create a subform that allows a user to select criteria to filter records by. When the user hits the "go" button on that subform, I want the results to appear in a listbox on another subform.

Click to expand...

Sawvel,

Here is an example of what you need to do with something like this...

(Enter your criteria in the first line of the top subform and push the button.)

If you want more criteria added for the lines other than the first, you will have to add a filter, a requery action for the rest of the controls, or simply another complementing operator in the SQL statement.

Thanks for the help.

I got the first part you presented to work.

I saw you said to either add a filter, requery action, or complementing operator to allow the additional lines to do the same.

When you say add a requery action, do you mean repeat the code for each and add a .....Forms!frm1.Form.requery..... type command after each repeat - or do you mean something else.

How does the complementing operator work?

Thanks again for all the help.

brsawvel said:

How does the complementing operator work?

Click to expand...

The point I am trying to make here is that you need another operator to "complement" whatever process you want to go through. I had originally assumed that you wanted to use all 5 rows of your controls to filter the other subform's listbox. Is that right?

If that's what your intention is, you have to decide what process you want to go through. I can only give you an example of one. Say, you want to use the first 2 rows of the controls to filter the listbox, but you want those separate filters to complement each other. In other words, you want every record that satisfies the first row of criteria, AND the second row. So, another way to say it is "all-inclusive". Here is the code you would use for that: For the record, the OR operator is closely tied with the phrase "all-inclusive", and the AND operator is more closely tied with the phrase "levels of filtering".

Does this example help you figure out what you need?

I added the code as you recommended for a second filter, but it doesn't seem to recognize the second filter I put in. The first filter still works just fine.

I tried using the "AND" operator instead of "OR" but the listbox came up blank.

Could you possibly add to your posted db so I can see an example of what you are recommending I do?

Thanks again for everything.

brsawvel said:

Could you possibly add to your posted db so I can see an example of what you are recommending I do?

Click to expand...

Sawvel,

Attached is an example of what I think you should do. Unfortunately for you, the road you've chosen for this filtering process will make an end-user very happy, but it will cause you great headaches along the way!

I'm not even going to try and explain the complexity of the code that has to be used for this. When you have "digested" it, post back.