Search filter text box

[randle]

Hi

Looking at the "Access 2007 Assets Database template" I'd like to encorportate the search/filter box included in this into my own asset database.

I've had a look at the properties for this text box to see what needs to be added to my form but so far haven't been able to add anything that works to my own form. There doesn't appear to be any code or macro assigned to this and so am a little stumped.

The form is tabular with many fields but want this search box to search all fields and then filter all rows in turn.

Anyone care to enlighten me?

Many thanks.

 

[John Big Booty]

Perhaps the sample posted here will give you some pointers.

 

[randle]

Many thanks, I'll take a look at see how I get on.

 

[randle]

This does look good although I'm more after an active search on the main form that will filter the rows as I type. The template that I mentioned in the first post does this but is on a tabular view rather than form. Is this even possible on a form?

 

[John Big Booty]

This does look good although I'm more after an active search on the main form that will filter the rows as I type.
...

The sample I linked to does just that

Here's another approach, this one filters a sub form rather than a list box. Once again it filters as you type.

 

[randle]

Yes sorry you're right. I posted that before I actually tried however my form's already in place and so need something that can be simply added rather than a redesign which a listbox or a subform would require.

On another note I have followed your instructions in the other thread and have setup the form for testing purposes but upon typing in the box nothing gets filtered! I'm pretty sure I haven't missed a step as have checked this over a couple of times!!

 

[randle]

Can I not just add a text box and attach a macro to this which will apply the filter in real time? This appears to be how the Quicksearch box is constructed on the demo template I initially posted.

 

[John Big Booty]

Have you actually had a look at the second sample I posted

The code behind the On Change event of the Text Box is very simple and straight forward, and does just what you have suggested.

 

[randle]

Yes I have and looks to be what I want but the form I want to add this to doesn't have a subform with the relevant data displayed in datasheet format. I have all my specified fields layed out in the detail section on a Continuous Form rather than a subform. Is it a case that I can't do what I want without using something like a subform or listbox? If not I'll have to maybe look at rethinking the database layout.

 

[John Big Booty]

The thing with the samples are they are designed to present a method of achieving a goal. It is up to you to then pull that sample apart and see how it works, and then see if you can apply that method to a slightly different scenario.

Now in the case of my second sample you can apply that method to a continuous form with your search box in the header of said form. You will of course have to change the form reference in the filter, it would now look something like;

    Dim strFilter As String
    
    Me.Refresh
    
    strFilter = "locality like '*" & Me.Text2 & "*'"

    Forms!YourFormName.Form.Filter = strFilter
    Forms!form1.Form.FilterOn = True
    
    Me.Text2.SelStart = Nz(Len(Me.Text2), 0)

Check Form1 in the attached sample.

 

[randle]

Thanks. Initially I was trying to pull the quick search apart and apply it to my database but couldn't see what the search box was linked to as didn't seem to reference anything. The only thing that seemed relevant was the macro but this had multiple references to the filter box and couldn't seem to make head or tail of it.

When applying your recent method to a test version of my database on the most part it did appear to work however couldn't get the filter to actually filter anything and just kept showing blank results.

When I get Two seconds I'll have another play and see if I can get any further and get back to you.

Many thanks again.

 

[loydwho]

...
Now in the case of my second sample you can apply that method to a continuous form with your search box in the header of said form. You will of course have to change the form reference in the filter, it would now look something....
.

Thank you for the tip John. I used your code to try to filter a continuous form.
I'm having trouble though. If I search for text which is not found in the recordset for the form, I get a runtime error 2185.
Perhaps I'm missing something in my code, or some property of a form.
Here is my code. NonConformity is the column name in the recordset and control name on the form. and txtSearch is the unbound text box where the search text is entered.

    Dim strSearch As String, _
        strFilter As String
    Me.Refresh

    If IsNull(Me.txtSearch) Then
            Me.txtSearch = ""
    End If
    strSearch = Me.txtSearch

    strFilter = "NonConformity like '*" & strSearch & "*'"
    Me.Form.Filter = strFilter
    Me.Form.FilterOn = True
      
    If Not IsNull(Len(strSearch)) Then
        Me.txtSearch.SelStart = Len(strSearch)
    End If

Any help would be appreciated.
:banghead:

 

[John Big Booty]

Can you post a copy of your DB?

 

[cybormike]

Thanks John, I used this approach in a project I was recently building. Simple yet effective, the way I like it.

 

[paramesium]

thanks John, but how do I maintain the search after a spacebar?

 

[jlb4350]

The sample I linked to does just that

Here's another approach, this one filters a sub form rather than a list box. Once again it filters as you type.

I just happened across this while searching for the same thing and just wanted to say thanks for sharing this. It's exactly what I needed! Could I ask one question though? Is it possible to allow spaces in the search query? When I type in the text box, spaces are not allowed.

Thanks again for sharing this!