Code to clear all filters and memory items (1 Viewer)

[raziel3]

Hello all,
I am having a little issue in my database. When I close the database after a session and I open it back, when I go to table or form design I see filters that were previously applied in the last session still stored in the filter property value. The same thing is happening to the order property and I think (don't know if it is possible) the recordset sql I am using in a public function is not being "unloaded" (if that's the correct term to use).

Is there any way that when I open my database I can run some code to clear all "cached" items from the previous session. Maybe put it in the Dashboard on load event.

 

[moke123]

Your not by chance using acSaveYes as an argument in the docmd.close code of your forms, are you?

What is the public function your using?

 

[arnelgp]

you can have a code run (on autoexec macro).
the code will inspect all forms and set their filter to null string
call the function using RunCode of the macro:

Public Function fnResetFormFilter(Optional ByVal FormName As String)

    Dim frm As Access.AccessObject
    Dim o As Form
    If Len(FormName) <> 0 Then
        DoCmd.OpenForm FormName, acDesign, , , , acHidden
        Set o = Forms(FormName)
        o.Filter = ""
        DoCmd.Close acForm, FormName, acSaveYes
        Set o = Nothing
   
    Else
        For Each frm In CurrentProject.AllForms
            DoCmd.OpenForm frm.Name, acDesign, , , , acHidden
            Set o = Forms(frm.Name)
            o.Filter = ""
            DoCmd.Close acForm, frm.Name, acSaveYes
            Set o = Nothing
        Next
    End If
End Function

not tested on .accde file.

 

[raziel3]

Your not by chance using acSaveYes as an argument in the docmd.close code of your forms, are you?

Yes I am, could that be the problem?

and this is the function

Public Function fncVAT(ByVal tDate As Variant) As Variant

Dim strTDate As String
Dim strVAT As String

strTDate = "#" & Format(tDate, "mm/dd/yyyy") & "#"
strVAT = "SELECT TOP 1 Vat FROM Tax WHERE [EffectiveDate] <= " & strTDate & _
                " ORDER BY [EffectiveDate] DESC;"

    With CurrentDb.OpenRecordset(strVAT)
        If Not (.BOF And .EOF) Then
            fncVAT = .Fields(0)
        End If
    End With

End Function

 

[theDBguy]

Hi. I know those extra information stored in the design view are annoying, but they shouldn't really matter much at all as long as you have Order On Load and Filter On Load set to No, or just clear them when the form opens. Just a thought...

 

[Pat Hartman]

Yes I am, could that be the problem?

It's only a problem if you don't want to save the changes you made to filters, etc.

If you are building this for others, you absolutely do not want to save their changes. For development, ensure that you don't have warnings turned off. That will allow Access to prompt every time you change an object you modified. If you turn warnings off, you will almost certainly loose updates you make to objects.