MS Access 2007, apply filter code (1 Viewer)

[TJBernard]

I have a database that allows the users to filter the records based on criteria entered into an input box.

The code applies filters, stored in queries, that allow for the users to enter text into an Input Box and the filter applies to the form.

This has worked great in MS Access 1997, 2000, and 2003. Now in MS Access 2007, it no longer works. I have done some searching, found some work arounds, none of which worked for me, and also found it is a known issue that has not been fixed by MS. Wonderful.

If I open up the .mde (a 2003 file, not even a 2007 file) with MS Access 2007, and click to apply the filter, the first time the filter applies correctly, limiting my results.

But after this first time, the filter application no longer works at all. The pop-up input boxes appear for the other filters, but the results do not return correctly.

I just want to clear the previous filter value, go back to all the records, and then apply the new filter value.

I have tried...

me.requery
me.refresh
me.filteron = false
me.filteron = true
docmd.showallrecords

The only thing that works is closing the form with the filters, and then re-opening it.

Has anyone run into this issue with MS Access 2007, and if so, is there a work around for it? I am about to pull my hair out fighting with this thing for hours on end, something that should and has worked with no problems at all.

Thank you,

T.J.

 

[TJBernard]

Huh, I figured out the code playing with something else.

This worked for a work around...

Dim FilterValue As String

FilterValue = InputBox("Please enter the Case Name (Blank for all)", "Case Name")

Me.Filter = ""
Me.Filter = "(((TblBilling.CASE_NAME) Like '*" & FilterValue & "*'))"
Me.FilterOn = True

If IsNull(FilterValue) Or FilterValue = "" Then
MsgBox "Filter will be removed", vbExclamation, "Filter Action Canceled"
End If

 

[Callahan]

Hello TJ,

I am having a very similar problem going from Access 2003 to Access 2010. Have a form used to display data, allows user to click on data to generate a filter, then click a button to print a report based on the filter. Worked great with Access 2003, cannot get it to work (yet) with Access 2011.

Glad you found a fix for yours, leads me to believe that I may find one for mine.