Clever Filter

[whenthegeeseinvade]

Dear Forumers

I am after some help writing a database to manage my plumbing business.

When I create an assembly of parts for a job, I select them from a combo box in a subform. There are many hundreds of items available within the combo box and I would like a facility to limit the number of rows shown.

The combo box shows the part description and the category of the part in different columns
eg:
Category: End Feed
Description: Copper Elbow 90 deg 15mm

If I could enter filter criteria in a separate text box, this would get me to the right part much quicker. I would like it to be configured so that the string "el end 15" could be entered to find the part above. It would search all columns and return all records which contain any combination of the criteria as separated by spaces in the text box.

I'm still pretty new to VBA and fear I might have to use an array, so if anyone can start me off in the right direction I would be really grateful.

Thank you all who read this.

 

[evanscamman]

This is easy to do, and you don't need to use an array.

Say for example that you want Text1 to filter results for Combo1.
In the rowsource for Combo1, you probably have already built a query that supplies your data.

In the criteria row for the column that contains your Description add the following:

is like "*" & Forms!YourFormName!Text1 & "*"

The * before and after is a wild card so your search text can match any part of the field.

The only other thing you need to do is add:

Combo1.Requery to the AfterUpdate event of Text1

Evan

 

[whenthegeeseinvade]

Thanks for the reply Evan

I think your suggetion would give me a filter containing the string "el end 15" which would have no matches.

What I really need to do in the case of the example I have given is separate the filter string into it's three constituent parts of "el" "end" "15" and then return the results of all records which contain any of these strings - either one or all in any order. This gives the most flexible filter and means that the user does not need to remember the phrasing conventions, only a few key words.

How could this be done?

 

[evanscamman]

If you set up the filter as I suggested, you could type in "el*end*15"
and it would return the results as you want.

To make it more user friendly you could use the afterupdate event to replace spaces with * so that your users could just type "el end 15".

If you wanted to do this transparently you would need a second hidden textbox.

So, with an invisible Text2, point the Combo1's criteria to Text2 instead of Text1. In the afterupdate event for Text1 do:

Text2 = Replace(Text1," ","*")
Combo1.Requery

This should do it.
Evan

 

[evanscamman]

Actually, just reread your post and saw that you want the phrases in ANY order.
And that you want to search across two different fields: Category and Description.

1st, you need to create a field in the query for your Combo box this is a combination of both text fields - you can name it whatever you want...
CombinedText: CategoryText & " " & Description
The criteria that points to your Search Text boxes will be in this column now.

Then you may need to use 2 or 3 separate text boxes for your criteria. This way each one is evaluated individually regardless of order.

Evan

 

[whenthegeeseinvade]

Top Man Evan.

I understand the replace function - very handy, and that I have to concatenate the fields into a single string.

What I would love is not to have the different text boxes, but just allow the user to enter the string in a single textbox as there could be 5 or 6 filter criteria which would get a little cluttered. Do you have any ideal how i could possibly achieve this?

Thanks again for all your help.

 

[evanscamman]

Yes, very possible - Working on something....

 

[evanscamman]

This should do it.

I left the 6 Phrases visible so you could see how they work, but of course, you can hide them.

Evan

 

[whenthegeeseinvade]

Thanks for all your hard work.

Please see attached pic.

Cheers,

Laurence