Form Search

[Susan Allen Wyoming]

I have been reading previous topics relating to creating a form search and I'm still not clear.

I want to create a search/filter for my main form "Products" based on a value and/or values that I enter for CatID, Name, Other Name.

I would like to be able to enter one or all of my values to help narrow my search.

I understand that if I go to the criteria for, let's say CatID and enter [Enter CatID] I will get a "Enter Parameter..." message box. But, what happens if I want to open my form without asking for that parameter?

And do I have to enter criteria for Name and OtherName as well.

Is there an easier way to create this search/filter?

Any help would be greatly appreciated!!!

Susan Allen

 

[Chris RR]

Here's the general idea.

Drop the criteria from your query and save it.

Create your form, based on your query, to display all your fields. Make sure that the "Allow Filters" property is set to Yes.

Now, create a text field that will let you enter your criteria. Let's say that it is called txtCatID. You won't have a Control Source for it.

In the "after update" of txtCatID, you want to put some code like this:

dim strFilter as String
strFilter = "[CatID] = '" & Me.txtCatID & "'"
DoCmd.ApplyFilter , strFilter

You won't need the quotes for a numeric field.

You can use multiple criteria, too. The simplest way is to put all the text boxes on the form, then put the code behind a separate button that will build the strFilter with all the fields in it. You can also use combo boxes instead of the text fields.

Good luck.

 

[Susan Allen Wyoming]

Thanks Chris....I'll try it out!

Susan

 

[Susan Allen Wyoming]

Chris - -

It works great, but I would also like to be able to put in "fer" and still get "Fertilizer" Is there a way to put a "Like" operator in the code so the user doesn't have to type "*"?

Also, how do I get out of the filter? A button that has code with something like DoCmd.RemoveFilter?

Thanks for all your help.

Susan Allen