Microsoft Access: Find all Access forms with a 'stuck' ServerFilter property.

Platform:Microsoft Access
Task:Find all Access forms with a 'stuck' ServerFilter property.
Discussion:
Anyone who has built an Access data project (.adp) soon finds that their forms are not filtering properly. Most of the time the problem is that Access is saving the form's ServerFilter property with the form. There is (to my knowledge) no way to fix this other than to open the form in design view and clear the property before compiling the database for distribution. All my attempts to change this property using VBA code in the form's Open or Close events fail to alter the property. The only solution I've come to is to write a script to tell me which forms have a stuck ServerFilter property. I then fix the forms in design view. As a side note, you can prevent this problem during development by closing a filtered form before opening it in Design View. If you open a form in Form view and then switch to Design View without closing it you are virtually guaranteed to lock the form's filter into the ServerFilter property. Closing the form before opening in Design View seems to clear the ServerFilter property. Regardless, event being vigilant about this workaround I always seem to have a form or two that gets 'stuck'. Hence the ShowFormsWithStuckServerFilterProperty() function shown below.
Example:
' many times a form gets saved with the filter property 'stuck' with a filter
' this sub will list those forms that have stuck server filter properties.
Public Sub ShowFormsWithStuckServerFilterProperty()
    Dim obj As AccessObject, dbs As Object
    Dim Str As String
    Set dbs = Application.CurrentProject
    For Each obj In dbs.AllForms
    Str = Left$(" ", (30 - Len(obj.Name)))
    DoCmd.OpenForm obj.Name, acDesign
    If Len(Forms(0).ServerFilter) > 0 Then
        Debug.Print obj.Name; Str; Forms(0).ServerFilter
    End If
    DoCmd.Close
    Next obj
End Sub
Back to Index