Solved Requery and clear cache in a Multifield search routine

[AnilBagga]

I have a table of items with Internal code, specifications Groups, Subgroups etc. To build a query wherein we can search either OR all the search fields (Internal part no, Group, Sub group1,2 etc), I built a form with text boxes and a query to read these text inputs and search for records fulfilling the search text in the Table. All fields are Text.

The enclosed file shows the screen shots of all the 3 - form, query and table. The problem is that the result of the query is not accurate. Every search result is used for the next query limiting the result.

e.g the table has over 1900 records with Group of Fabric whereas the search result is showing 300. I think there is a need to clear the cache of the previous Query every time we make a fresh Query? How do we do this?

The Criteria if not clear in the screen shots is reproduced below

Like "*" & [Forms]![GroupSearchForm]![ItemGroup] & "*"

 

[theDBguy]

Hi. You might find it easier to use the form's Filter property than applying criteria to a query. What I usually do is simply use a query that has no criteria, so the form will display all the records. Then, from the selections made by the user, I build a criteria string and apply it to the form's Filter property. This works well for me, most of the times.

 

[arnelgp]

regarding the form design, you may try to use comboboxes instead of textboxes for your search form.
you are using And in the query (criteria in one straight line), maybe you should consider using Or.
in SQL view, replace the "And" with "Or".