Possible to filter Subform from Textbox? 2

[boxboxbox]

I have a subform that shows all the records. Can I place a textbox on the main form that will filter the records in the subform?

I'd really like it to query the filter with every keystroke, and not have to hit a "search/filter" button, but...

Is this even possible in Access 2K?

 

[Flo79]

This is absolutely possible. There is even a name, ¡§query by form¡¨, which is commonly used to refer to what you are attempting to do.

In order to filter a subform from a form you can use the following two methods:

Method 1 (Easiest method)

Base the subform on a select query, which filters records based on the specified value of an unbound control on your main form. For example, let¡¦s assume you have a table with the following five columns: Social Security #, First Name, Last Name, Gender, and Age. You now want to be able to filter all records in this table by entering a value in one or more control(s) on a form and then displaying the results in a subform on that same form.

Step A:

Create a separate query for each of the fields that you want to filter. For example if you want to be able to filter records by a person¡¦s first name, one query would have to include, a person¡¦s soc sec # (used as primary identifier) and his or her first name. In query design view, you would have to include the reference to the field on your form, which you use to search for a person¡¦s first name, in the criteria part of the design grid under the first name eg. [Forms]![SearchForm]![FirstNameFilter] or [Forms]![SearchForm]![FirstNameFilter] is null

Make sure to use the exact same code as above, substituting for the control names which you¡¦ve used on your form.
You need to repeat this step for each of the fields in your data you want to be able to search. Then link the original table with your data with the queries for all of your searching criteria based on a unique primary key (social security number in this example) to show only those fields for which the records in all tables are equal. This is the query on which to base your subform.
Once you have done this, you should test whether you have designed you query correctly, by opening it, specifying the criteria and checking if the correct results are returned. If you don¡¦t intend to filter by a certain criteria but are still ask to specify it, leave it blank. Leaving all criteria blank, will return all records.

Step B:

Now the best way to proceed is to create a button on your form, which you press in order to filter the records in the subform after you¡¦ve specified your search criteria on the main form.

To do this, create a macro called ¡§Filter_Records¡¨, which includes only one action: Requery.
After you¡¦ve created this macro, create a bottom on your main form, which triggers this macro.

Once you¡¦ve specified your criteria and pressed the button, the correct results should appear in the subform.

The second method will take much longer to explain, and is only truly relevant if you have to filter massive amounts of records based on numerous criteria, in which case performance becomes critical. If you should need this method let me know, and I¡¦ll gladly help.

 

[boxboxbox]

"Then link the original table with your data with the queries for all of your searching criteria based on a unique primary key (social security number in this example) to show only those fields for which the records in all tables are equal. This is the query on which to base your subform. "

So I make a "combined" query that has fields from each of the little queries? Which fields do I put in so they don't all repeat? My relationships/links keep getting messed up, so I'm going to start from scratch in a new test database...I'll get back to you. Thanks

 

[boxboxbox]

So cool! Thanks! I got it to work so when I hit enter it will refresh the query and thus filter my subform.

Instead of creating a button, would it be possible to refresh the query with every keystroke in that textbox? For instance, if I have: "May", "Maybe", and "Maybe Not" in my subform, then if I type "May", they will all show up, but as soon as I type "Mayb", then only the last two show up...and if I backspace the "b" then they all show up again...

I tried doing a "requery" on the Keypress event for one of the search boxes, but that wouldn't let me type anything, and the whole list was returned. The button method worked fine.


As for Method 2:
This is going to be in an Inventory database, for books. The search boxes are going to be for title, author, book ID, publisher. Usually we only sort by one or two of these criteria. However, we do have 20,000 books and growing--and I would like it to be accurate and fairly quick. I would say that I would indeed like to know method 2, so if you're willing to type it out, I'm definitely willing to listen.

In any case, Method 1 will also be quite useful.

Thanks again for your knowledge!

 

[boxboxbox]

Also, if I can use the wildcards like *, that would be great. I'm seeing that you have to enter the whole word for it to be returned with the filter. Hmm...

 

[Flo79]

If you want to use wildcard characters do this:

In the criteria box of your query design grid under the field which you want to filter, enter the following:
Like "*" & [Forms]![FormName]![FieldName] & "*" or [Forms]![FormName]![FieldName] is null

This will do the job.

 

[boxboxbox]

I love technology. Gracias again, friend.

 

[boxboxbox]

Oooh! and I got it to work with the KeyUp event, so as soon as I type it filters the queries. Yes! And with the SetFocus and SelStart events it will even go to the end of the text box, and not another textbox. Whoohoo!

Now that I have the wildcards in there, however, it seems to use them for everything. Say again that I have May, Maybe, and MaybeNot. If I type in "N" the Maybe Not shows up, even though I don't want anything to show, since it doesn't start with N.

Any way to only search for records that Start with the text I enter, and then use the wildcard when I want to find them anywhere?

Also, it doesn't seem to accept spaces as a valid piece of text--it deletes the space as soon as I type it. But I have spaces between words...If I go back with the mouse, click, and add a space, then it will find it. But it won't let me type...



(Also also, It doesn't seem to even need the wildcard "Like *" etc, with the KeyUp event...)

 

[boxboxbox]

(Okay, it does need the wildcards--I was saving the query without, but not closing/reopening the form)

 

[Flo79]

If you want to filter for a text box starting with a particular word or letter, try this instaed:

Like [Forms]![FormName]![FieldName] & "*" or [Forms]![FormName]![FieldName] is null

This will do the Job.

PS: If you are intrested, I do have a database designed for managing book inventories that I am willing to share. It might give you some good ideas and direction to build upon.

 

[boxboxbox]

Flo79,

I would love to be the recipient of sharing--not that you haven't shared a lot already!

Since this email gets so much junk mail already, I don't mind so much posting it. Why not send it to me at adivine1athotmaildotcom. Put something relevant in the subject though, so I don't delete it accidentally.

Will this include your Method 2?

Are you a bookstore? Or do you design these things for other people? Just curious...

THANK YOU!

 

[Ciralia]

Hi, I know this thread is a couple months old, but I have the same issue (slightly different) and have tried the solutions in the thread, but must be doing something wrong.

Here is my situation:

I have a separate form for filtering and a separate form that is the "filtered" form. I want to put my "filtered" form onto a main form as a subform. At this point in time my filter works when the forms are all separate but when I try to put my "filtered" form as a subform, the filter no longer works. Also, my filter uses combo boxes, and the user selects which option(s) they want to filter by. My filter is coded as follows for each control:

***********************************************************
If Not IsNull(cmbGender.Value) And (cmbGender.Value) <> &quot;Any&quot; Then

If temp = &quot;&quot; Then
temp = &quot;Gender=&quot;&quot;&quot; + cmbGender.Value + &quot;&quot;&quot;&quot;

Else
temp = temp + &quot;and Gender=&quot;&quot;&quot; + cmbGender.Value + &quot;&quot;&quot;&quot;

End If
End If

DoCmd.OpenForm &quot;frmSearchMain&quot;, acNormal, , temp 'Open main form and save global variable

***********************************************************
Now, since I am using combo boxes instead of text boxes, would I still need to build a separate query in the same way? If so, how to code it (be specific if you can). Here is a list of the controls I am using on my separate filter form:

cmbGender, cmbAge, cmbHairColor, cmbHairType, cmbEyeColor, cmbEthnicity

The OK button on my filter page is where the filter actually starts working.

My main form is called frmSearchMain, and my &quot;filtered&quot; page when it is a subform is called frmSubSearch.

Thank you for any help!

 

[Ciralia]

*bump*

 

[boxboxbox]

Flo79

Just going through my database trying to make it faster. You mentioned Method 2 - is this included in the database you sent me? I'm not sure really what I'm looking for, if so.

Appreciate all your help.

Ciralia,
I am using Access 2000 and have had problems filtering subforms. Basically it freezes the program (Access thinks it is running something, but won't stop) and gives an error message for docmd. procedures. I have gone around this by requerying the underlying query, or making my subform part of the main form and using a filter anyway. The speed was a bit better using the filter on one form, but I moved some tables to a different computer and that seems to have slowed it terribly...