Unfiltering the Searchbox - Search As You Type in Microsoft Access


	Back to Search As You Type Comments List

Upload Images Link Email

Unfiltering the Searchbox

Kathy Henry

12 months ago

My Form has records of school bus routes by name, number, driver, etc. called EnterRouteF That form has a subform with sections of the route like AM Secondary, Am Elementary, PM Secondary, etc. (called EnterRunF). It's 1 route to many runs. The Route Form has the search textbox. I can find a route to see if I need to enter it or to view it's run and I can successfully enter new routes and new runs to a route. But once I do, I have filtered records and don't know how to unfilter them. Ideally, I'd like to just unfilter but keep the record selector at the current record that I found or edited. Playing around with it the best I could do was get to the first record. How would you apply an unfilter?

	Kevin Robertson 12 months ago
Can you show us what you have? Code? Images? There are multiple ways to do everything in Access, so how are you applying the filter? This video will help you stay on the same record: Requery in Place

	Donald Lader 12 months ago
I have a button on my form that appears with "Clear Filters" (in red) whenever a filter is set on the record I am on. This only appears if the filter is on. When I click the button, the filter is released and I stay on the current record.

Kathy Henry OP

12 months ago

I misspoke when I called my records filtered.  Technically, they are because out of my 19 test routes, I start searching and the total number of records start to lessen.  The message at the bottom still says "unfiltered" even if I have only 2 records.  Clicking on it just toggles from 2 records to 1 (I think a new record because it's blank), then back to 2.  I'll try to send pictures, but here is the code for search box:
Private Sub SearchBox_Change()

    Me.RecordSource = "SELECT * FROM RouteT " & _
        "WHERE " & _
            "RouteName LIKE ""*" & SearchBox.Text & "*"""

    SearchBox.SetFocus
    SearchBox.SelStart = Len(SearchBox.Text)

End Sub

	Kathy Henry OP 12 months ago

	Kathy Henry OP 12 months ago
Donald, Did you make the clear button yourself or is this within the program like the "unfiltered" at the bottom of navigation button? Did you have a situation like mine where it says unfiltered? I wonder if I used your code, my records would fix themselves so to speak. Would you mind sharing?

	Kevin Robertson 12 months ago
You appear to be filtering a subform. You need to change the notation. Me would refer to the parent form. To reference the subform try one of these: Forms!ParentFormName!SubformName.Form.RecordSource Parent!SubformName.Form.RecordSource SubformName.Form.RecordSource Value From a Form Value From Subform

	Kathy Henry OP 12 months ago

	Kathy Henry OP 12 months ago
I want to filter on the form (Routes) and not the subform. There are 19 Routes and in the above example Route Martian, we see 2 runs. In the picture I uploaded previously, Route Flamingo we see 6 runs which is typical of 1 route.

Kathy Henry OP

12 months ago

After going over many videos, I found the answer.  It was all in the Clear Button's code.  I had typed in an empty string instead of null.  Here is the correct code.
Private Sub ClearBtn_Click()

    SearchBox = Null
    Me.RecordSource = "SELECT * FROM RouteT"

End Sub

I think my confusion lay in the fact that this event was fired off by the SearchBox Change event and not any kind of filtering.
That is why Records still said "unfiltered" in the nav bar at the bottom.
Thank you to all who took the time to help me out.  I appreciate it.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Search As You Type.