Form Filter

[Rubz]

Hi All

I have a problem that has been driving me mad that I hope someone can help with.

I have a basic form based on a table. In the form I have an unbound combo box that displays a list of activities (taken from a list in a table). When you select an activity and then click on a button it filters the records for all of those about that activity (there are two filter buttons, one for activities that haven't been done and one for those that have which is a simple yes/no tick box in the record). The buttons run a basic macro to ApplyFilter.

This all works fine. The problem comes when you then change the activity showing in the unbound combo box and try to filter for a new activity instead (even when you have clearer the previous filter). Clicking on either button to ApplyFilter applies the filter referencing the first activity that was selected and filtered on rather than the one currently selected. The only way I've found to get round this is to close and reopen the form, but I don't want to have to keep on telling the rest of my team that this is what they need to do.

Any thoughts on this very much appreciated.

Thanks

[June7]

What method applies filter? How do you 'clear' the filter?

Need to see code. Post it or the project for analysis.

[Rubz]

The filter is applied with the standard ApplyFilter macro. The condition for this is:

([Red Book]![Done]=Yes) And ([Red Book]![Activity]=[Forms]![Red Book]![ActivityFilter])

Where:
- [Red Book]![Done] is the check box for each record showing whether the activity has been done
- [Red Book]![Activity] is the name of the activity in each record (selected from a list in a combo box)
- [Forms]![Red Book]![ActivityFilter] is the unbound combo box on the form that is used to show what activity you want to filter for (from the same list as above). This is the value that once you have filtered for one activity then remains the same (as far as the filter is concerned) whatever value you then put in this combo box.

The code to run this macro is:

Private Sub Command39_Click()
On Error GoTo Err_Command39_Click
Dim stDocName As String
stDocName = "Done Filter"
DoCmd.RunMacro stDocName
Exit_Command39_Click:
Exit Sub
Err_Command39_Click:
MsgBox Err.Description
Resume Exit_Command39_Click

End Sub

The 'clear filter' is the standard ShowAllRecords macro.

Let me know if you need any more information on any of this.

Thanks

[June7]

Crud! I don't use macros, only VBA. I can often figure them out but have to work with project if you want to provide it.

Instead of ([Red Book]![Done]=Yes) can try ([Red Book]![Done]=-1)

[Rubz]

I tried changing 'Yes' to '-1' but it still works exactly the same. I don't think it should be anything to do with the [Red Book]![Done] part though, it's the [Forms]![Red Book]![ActivityFilter] part that the macro doesn't recognise the updated value for.

This was written as a database rather than a project, and I'm not sure how to convert it to a project to post for you I'm afraid.

[Rubz]

From reading around it appears that this is a bug in Access 2007 and no one seems to have found an easy way of solving it. I've now got round it by changing the clear filter button so that it closes and reopens the form and adding a note for users to remind them that they need to click on the clear filter button between different filters. Not ideal but it will hopefully serve a purpose for now.

Thanks for your thoughts on this June7.

[June7]

By 'project' I just mean the Access file. It can be attached to post, zip if large.