Problems with form filters in Access 2007

<One poster said MS had acknowledged it as a bug, but I have no confirmation of that>

Where did you see this?

On this site.  Try this link and look at the comment near the end of the thread.

https://www.experts-exchange.com/questions/22709636/Access-2007-Filter-with-criteria.html

I don't have Access 2007 here, and there is little chance this will work anyway, if it's a known bug.

As someone else suggested in the other question, you can try to remove the filter through code:

    Me.Filter = ""
    Me.FilterOn = False

The Macro equivalent of these lines are

    Action: SetValue
    Item: Form.Filter
    Expression: ""

    Action: SetValue
    Item: Form.FilterOn
    Expression: False

You might want to add a requery:

    Action: Requery
    Control Name: (leave blank)

But again, since ShowAllRecords did not work, there is very little chance this will.

Finally, since closing and reopening seems to work, why not:

    Action: Close
    Object Type: Form
    Object Name: =Form.Name

    Action: OpenForm
    Form Name: =Form.Name

This macro will simply close and reopen the current form. Ugly, but it might be a workaround until MS addresses the problem?

Cheers!
(°v°)

Thanks for your idea, but alas, I've already tried that code solution, with no difference.  As far as being a bug, it's hard for me to believe that something like this would not have been discovered in even the most cursory testing.  Of course, in this era when the public are the unwitting beta testers anything can happen.  On the other hand, it's just as hard to believe that Microsoft would have hijaciked the ApplyFilter utility given that there are probably lots of apps already out there using it like I did.

Have you applied SP1 for 2007? The post you linked to is a couple of months old, wonder if the Service Pack addressed that?

Yes, SP1 is installed, and apparently does not address the issue.  Maybe in SP2?

synergycompany,

For what it's worth...
I am doing basic filtering on a form in Access 2007 with SP1 with no problems at all.

However, like most developers, I am using VBA, not macros.

My scenario is similar to yours:
Open the from.
Enter a search criteria into a textbox. (Yes, I can use Wildcards)
Click a "Search" button.
Filtered records display.
Click a "Remove" Filter button and all Records display

Enter another search Criteria into the textbox.
Click a "Search" button.
New filtered records display.
Click a "Remove" Filter button and all Records display once more.

I can even type criteria after criteria into the search box, click the "Filter" button and the filtered records will display even without me clicking the "Remove Filter" button.

Here is the very basic VBA code I am using:

Sub cmdSearchPlot_Click()
    Me.Filter="PlotSummary Like " & "'" & Me.txtSearchPlotSummary & "'"
    Me.FilterOn=True
End Sub

Sub cmdRemoveFilter_Click
    Me.FilterOn=False
End Sub

Again, with no problems whatsoever.

Can you give your filtering a try with VBA, instaed of the Macros, and see if it works.

You have upgraded your version of Access, perhaps it is time to upgrade your filtering techniques to VBA.
;-)

JeffCoachman

Thanks for the idea.  I gave it a try with the following code:

Private Sub NameFilterButton_Click()
Me.Filter = "(((Contacts.FName) Like IIf(IsNull([Forms]![Contact General]![FNameFilter])=True," * ",[Forms]![Contact General]![FNameFilter])) AND ((Contacts.LName) Like IIf(IsNull([Forms]![Contact General]![LNameFilter])=True," * ",[Forms]![Contact General]![LNameFilter]))))"
Me.FilterOn = True
End Sub

When I run it, I get a "Runtime Error 13, Type Mismatch" on the first line (the Me.Filter...line, which of course is just one line in the procedure).  The string is exactly what appears in the form's property box Filter line after the filter is applied the old way (macro command ApplyFilter with a query name as the Filter Name argument).  

The described macro method always worked fine until 2007.  And yes, I have decided to learn more about VBA and use it in the future, but I don't know yet how to whip the above expression into some form that VBA will digest.  Remember, I'm filtering on the values that exist in two text boxes, either of which may be null.

I appreciate your taking the time to look at this.  If you have any more suggestions, I'll certainly try them

You could try it like this. It also remove the awkward Like with IIf(). Note that the expression "FName Like '*'" does *not* return Null FNames, so it's bad practice anyway.

I use BuildCriteria here, which allows some fancy search criteria. Even things like

    F* or PH*
    Between A and M
    Phillip or Filipo

I hope the VB solution will work for you.

Cheers!
(°v°)

Private Sub NameFilterButton_Click()
    
    Dim strFilter As String
 
On Error GoTo Failure
 
    If Not IsNull(Me.FNameFilter) Then
        strFilter = strFilter & " And " _
            & BuildCriteria("FName", dbText, Me.FNameFilter)
    End If
    If Not IsNull(Me.LNameFilter) Then
        strFilter = strFilter & " And " _
            & BuildCriteria("LName", dbText, Me.LNameFilter)
    End If
 
    If Len(strFilter) Then
        MsgBox Mid(strFilter, 6), , "Debugging"
        Me.Filter = Mid(strFilter, 6)
        Me.FilterOn = True
    Else
        Me.FilterOn = False
    End If
 
    Exit Sub
    
Failure:
    MsgBox Err.Description, , "Error in filter"
    Err.Clear
    
End Sub

Select allOpen in new window

I meant to add: the error message came from your double quotes inside the filter string. You need to replace them either with double-double-quotes, or use single quotes instead. You also had wrong bracketing. This is a simplified version of your code, just for reference:

Private Sub NameFilterButton_Click()
Me.Filter = "FName Like IIf(IsNull(Forms![Contact General]!FNameFilter),'*',Forms![Contact General]!FNameFilter) AND LName Like IIf(IsNull(Forms![Contact General]!LNameFilter),'*',Forms![Contact General]!LNameFilter)"
Me.FilterOn = True
End Sub

(°v°)

synergycompany,

Try harfang's suggestion first, he is pretty good at figuring things like this out.


If it still is a no-go,  then consider this:
Can you try it using the same "simple" code I used, just to make sure the filtering part works?
(You can add in all the If-Isnull stuff later.)

Again, the way you stated your situation, my filter will do almost the exact same thing.
<user to enter anything (including wild card characters) or nothing in either form control and thereby filter the underlying table based on everything he/she knows about the first and / or last name of the desired contact(s). >

In my sample, a user would enter part of a movie plot, and get all the matches.

In my sample:
*and* yeilds 14 records
* and * yeilds 9 records
*Land* yeilds 2 records

Note: my sample is a Native 2007 format database, it is not a file that was "Converted" to 2007, or simply "Opened" in 2007.

Here it is:
https://filedb.experts-exchange.com/incoming/ee-stuff/6900-Access--EEQ23205606-Access-2007.zip

Please verify if the filtering works as expected first.

We can work from there.

JeffCoachman

Wow.  Thank you Jeff and Harfang.  Here are the results of my tests:

1)  Using Harfang's corrected version of what I had done, as follows:

Private Sub NameFilterButton_Click()
Me.Filter = "FName Like IIf(IsNull(Forms![Contact General]!FNameFilter),'*',Forms![Contact General]!FNameFilter) AND LName Like IIf(IsNull(Forms![Contact General]!LNameFilter),'*',Forms![Contact General]!LNameFilter)"
Me.FilterOn = True
End Sub

This gives the desired results, but the primary problem (in Access 2007 only) remains - if I change criteria in the FNameFilter or LNameFilter text boxes and run the filter again, it repeats the results of the previous criteria, even if I "ShowAll" or "Me.FilterOn=False" in between filterings.  Only by closing the form and re-opening it can I enter new criteria and get new results.

2)  Using Jeff's simple code, adapted for my situation, as follows:

Private Sub NameFilterButton_Click()
    Me.Filter = "LName Like " & "'" & Me.LNameFilter & "'"
    Me.FilterOn = True
End Sub

This returns the desired results, and new criteria can be entered and the filter run again with new results, with or without ShowAll or Me.FilterOn=False in between filterings.  However, this is with the value from just one text box.  Remember that I am using values from two text boxes, either of which may be null or have a value.  This is why I did it the way I did in the first place (with the Like IIf(IsNull...).  Expamples as follows::

FNameFilter=null
LNameFilter=Smith
Returns records with last name Smith and any first name

FNameFilter=Donna
LNameFilter=null
Returns records with first name Donna and any last name

FNameFilter=Don*
LNameFilter=Smith
Returns Donald Smith, Don Smith, Donna Smith, Donahue Smith

3)  My attempt at modifying Jeff's code, as follows:

Private Sub NameFilterButton_Click()
Dim FNameFilter As String
Dim LNameFilter As String
If IsNull(Me.FNameFilter) Then
    FNameFilter = "*"
Else: FNameFilter = Me.FNameFilter
End If
If IsNull(Me.LNameFilter) Then
    LNameFilter = "*"
Else: LNameFilter = Me.LNameFilter
End If
Me.Filter = "FName Like " & "'" & FNameFilter & "'" And "LName Like " & "'" & LNameFilter & "'"
Me.FilterOn = True
End Sub

This compiles OK but when run retuirns a "Runtime Error 13 Type Mismatch" on third to last line (Me.Filter = ).  As I said, I'm just learning VBA :)

4)  Using Harfang's code, as shown in his post above:
Compiles OK, but when run returns no records, regardless of what is entered in FNameFilter or LNameFilter.

I hope this helps.  It seems that the issue is in dealing with two filter values, but it always worked before Access 2007.  Any further thoughts?

Thanks again for you efforts.  I'm learning a lot along the way.

John

synergycompany,

I will try adding another filter tomorrow

JeffCoachman

Well, that's promising. It would appear that the bug is related to the reference to Forms in the filter (or something like that), but that it doesn't occur with simple standard filters.

In your code, try this line:

Me.Filter = "FName Like '" & FNameFilter & "' And LName Like '" & LNameFilter & "'"

I'm sorry my code did not work as intended (it did on my sample database). Does it display the filter before applying it in a message box? If you enter "Smith" in LNameFilter, does it not show: LName = "Smith" ?

(°v°)

synergycompany,

Ok, here is the same DB with now TWO filers on two separate fields.

It filters both criteria fine for me.
https://filedb.experts-exchange.com/incoming/ee-stuff/6906-Access--EEQ-23205606-Access2007.zip
(again, Office 2007 Full Install, SP1)

Here are some combinations to try:
PlotSummary= *The*,  Featuring= *er*
...Yields 17 Records

PlotSummary= *The*,  Featuring= *ar*
...Yields 4 Records

PlotSummary= *nd*,  Featuring= *ar*
...Yields 2 Records

PlotSummary= *nd*,  Featuring= *er*
...Yields 12 Records

Please verify
:-O

JeffCoachman

Sorry about the delay in responding, but it has definitely been a Monday at work.

Responding to Harfang's question about what happens when I try the code offered in post 21025138 using the BuildCriteria method.  After entering values in FNameFilter and LNameFilter clicking on the NameFilterButton produces a message box as follows:

Title Bar:  Debugging
Text: [value entered] and [value entered]
Button:  OK

Clicking OK produces a"Enter Parameter Value" box, but it prompts not for the value of the control but for the value itself.  In other words, it wants to know the value of "John", not FNameFilter.  Entering the values and clicking OK runs the filter (as indicated by the toggle on the bottom of the form), but no records are actually filtered.  Clicking Cancel in the parameter box produces another message box:

Title Bar:  Error in Filter
Text:  The Search Key was not found in any record
Button:  OK

Sometimes these messages will not appear, but in any case records do not get filtered.

Testing Harfangs code&

Private Sub NameFilterButton_Click()
    Me.Filter = "FName Like '" & FNameFilter & "' And LName Like '" & LNameFilter & "'"
    Me.FilterOn = True
End Sub

And Jeffs code&

Private Sub NameFilterButton_Click()
    Me.Filter = "FName Like " & "'" & Me.FNameFilter & "'" & " AND " & "LName Like " & "'" & Me.LNameFilter & "'"
    Me.FilterOn = True
End Sub

Returns records only if values are entered in both FNameFilter and LNameFilter.  If either of those are null, no records are returned.  Remember, in my pre-2007 setup, a null was treated as "*", and returned all records rather than none (see examples in my post 21026886 above).  Im starting to think that the solution is either 1) Microsoft restores the functionality of ApplyFilter that existed in pre-2007 versions or 2) I simply train users to enter an * in the FNameFilter or LNameFilter text boxes if they dont know a specific value.  On the other hand, if either of you have an idea for making it work, Im all ears.

In any case, I really appreciate the time youve taken to find a solution.  Nothing Ive seen yet matches EE for responsiveness and expertise.  Thanks again.

John

Title Bar:  Debugging
Text: [value entered] and [value entered]

This is not what I'm getting. Are you certain you entered my sample code as it is, without modifications? In my test, I get for example:

LNameFilter: Fischer
MsgBox text: LName = "Fischer"

FNameFilter: M*
MsgBox text: FName Like "M*"

Both like above
MsgBox text: LName = "Fischer" And FName Like "M*"

You could also step through the code (place a breakpoint and watch how strFilter evolves at each step).

Cheers!
(°v°)

harfang, synergycompany,

FWIW,

I am not at my 2007 machine today.
synergycompany, you could try adding your "If-IsNull" validation to my sample and see if it still works.

If harfang gets you going, great.
;-)

I will try to post a new sample with the If-Isnull validation tomorrow.

JeffCoachman

harfang,

I tried the code again (copied directly from window above into VB editor) and got the same results.  If I enter "John" in FNameFilter and "Smith" in LNameFilter and click the button, a window pops up, as follows:
Title Bar:  Debugging
Text:  [John] and [Smith]
Button:  OK
Clicking OK produces a"Enter Parameter Value" box, but it prompts not for the value of the control but for the value itself.  In other words, it wants to know the value of "John" and "Smith", not FNameFilter and LNameFilter.  Entering the values and clicking OK runs the filter (as indicated by the toggle on the bottom of the form), but no records are actually filtered and the record count shown by the navigation buttons is the number for the entire underlying table.  If I click Cancel Cancel in the parameter box I get another message box:
Title Bar:  Error in Filter
Text:  The Search Key was not found in any record
Button:  OK
The parameter box part only happens on the first attempt after the form is opened or after I have manually cleared the filter (erasing the string in the Properties box).  The other part (indication of filtering but no actual filtering) happens every time.

On another note, I have found a way to make my filter work largely like it did before 2007, using adaptations of code you and Jeff have provided.  I realized that the key was to find another way to make a non-entry in FNameFilter or LNameFilter be interpreted as an * (which I formerly did with my IIf(IsNull... routine in a query).  Now I simply make sure that an * is always in both boxes until the user types in a value.  That way, a value entered just in LNameFilter with no entry in FNameFilter will return all Smiths regardless of first name.  I did it as follows:

1)  Set the Default Value property for both text boxes fo "*".
2)  After entering search values in either or both boxes, clicking on NameFilterButton runs...
Private Sub NameFilterButton_Click()
Me.Filter = "FName Like " & "'" & Me.FNameFilter & "'" & " AND " & "LName Like " & "'" & Me.LNameFilter & "'"
Me.FilterOn = True
End Sub
3)  Clicking the ShowAllButton runs...
Private Sub ShowAllButton_Click()
Me.FilterOn = False
FNameFilter.Value = "*"
LNameFilter.Value = "*"
End Sub

The only slight disadvantage of this compared to my pre-2007 method is that with several consecutive filterings the user has to be mindful of keeping the * in any text box that hasn't got a value typed in, either by using the ShowAllButton in between filterings or by typing the * in when removing the value from a previous filtering.  Not a big deal.

What do you think?  Is it likely that can be improved, or should I call it good?  If so, how should the points be awarded?  Both you and Jeff showed me a lot and should get some credit for the solution, if in fact this is to be considered the solution.  Let me know, and as always thank you for the time you've taken.

John

harfang and jeff,
It's late and my brain is tired, but I will test these ideas and report tomorrow.
John

Harfang and Jeff,

I'm very pleased to say that I tested the solutions you both offered and they both worked flawlessly, no matter what I entered (or didnt' enter) in the text boxes.  As much as I'm glad to have a solution to my problem, I also appreciate what I learned in the process.  

I'd like to evenly split the points (I assume that is what "Accept Multiple Solutions" means).  Does that sound OK to both of you?

Thank you so much for your efforts,              John

Thank you for you feedback, and of course a point split would be acceptable: when using accept multiple solutions, you are asked to distribute the points in any way you like to all answers that you feel are part of the solution.

Cheers!
(°v°)

I would be honored to share the points with you Marcus!
;-)

JeffCoachman

Thanks again guys!  Perhaps we shall meet again.