Clearing a filter

[Paul Wagner]

On a form I have 2 search unbound fields called keysearchword and guestsearchword. Each has an On Exit event that performs their respective searches.

If IsNull([Keysearchword]) Then
strLinkCriteria = "[Keywords] Like '*'"
Else
strLinkCriteria = "[Keywords] Like '*" & Me!Keysearchword & "*'"
End If

DoCmd.OpenForm strDocName, , , strLinkCriteria

However, after the first find, I see a "(filtered)" next to the correctly displayed records. At this point however, I can't even get the cursor into the second guestsearchword field.

What am I doing wrong and how do I fix it?

 

[RuralGuy]

Are you opening the same form that this code is in? If so, I would close it first although there are easier ways to filter the form that you have open.

 

[Paul Wagner]

You are right, I'd rather not open/close it. Do you have any easier way of filtering the form?

 

[RuralGuy]

Me.Filter = "[Keywords] Like '*'"
Me.FilterOn = True

 

[Paul Wagner]

Great. OK. I like the concept.

And I'm assuming your code replaces the Open clause as follows:

Private Sub Keysearchword_Exit(Cancel As Integer)
On Error GoTo ErrorHandler

If IsNull([Keysearchword]) Then
Me.Filter = "[Keywords] Like '*'"
Me.FilterOn = True
Else
Me.Filter = "[Keywords] Like '*" & Me!Keysearchword & "*'"
Me.FilterOn = True
End If
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number & ": Description: " & Err.Description
Resume ErrorHandlerExit
End Sub


But after I try another word in the box the filter doesn't respond (it simply gives me the same message again "1 (filtered)". Nor do I get access into the second unbound box.

Are these filters accumulative? And why does it lock out further filtering?

I even created a separate command button called clear with this inside:
Me.FilterOn = False

Doesn't help.

Ideas?

 

[RuralGuy]

My test worked just as I have described. Can you post enough of your mdb to demonstrate the problem?

 

[Paul Wagner]

Sure. The file is quite small and attached. Maybe there's something else holding it up. Your advice is certainly appreciated.

Thanks,
Paul

 

[RuralGuy]

I'm sure it has something to do with the fact you are using the Exit event. Move your code to the LostFocus or AfterUpdate event. I tried both with success.

Different subject. I changed your code a bit:

If Len(Me.Keysearchword & "") = 0 Then
    Me.Filter = ""
    Me.FilterOn = False
Else
    Me.Filter = "[Keywords] Like '*" & Me!Keysearchword & "*'"
    Me.FilterOn = True
End If

Just a different way of turning off the filter.

 

[Paul Wagner]

This works, but only once. After that it gives 1 (Filtered) below and refuses to accept the cursor into the Guest Search box. Did that work for you?

 

[RuralGuy]

I created a new db and turned off Tools>Options>General tab - Name Auto-Correct. Then I imported your table and the query I was using. I then created a new form with the wizard and put the search TextBox in the Header rather than the detail section, but I don't think it makes any difference. The method I used eliminates any corruption that might have been present and works as you would expect with the code in the LostFocus or AfterUpdate event.