clear filter on close

[Crash1hd]

I figured this would work but it does not

Private Sub Form_Close()
Me.Filter = ""
End Sub

as for some reason every so often the filter box contains data and screws up my form! and I have to go into edit and manually remove the filter and then its fine so how can I go about doing the above also in my search I found this

DoCmd.RunCommand acCmdRemoveFilterSort

what does it do / what is it?

 

[ghudson]

Wrong event...

Private Sub Form_[COLOR=Blue]Open[/COLOR](Cancel As Integer)
On Error GoTo Form_Open_Err

[COLOR=Blue]    Me.Filter = ""
    Me.FilterOn = False[/COLOR]

Form_Open_Exit:
    Exit Sub

Form_Open_Err:
    MsgBox Err.Number & " - " & Err.Description
    Resume Form_Open_Exit

End Sub

 

[Crash1hd]

OK thats great if I wanted to clear it on open but I want to clear it on close

so i tried

Private Sub Form_Close(Cancel As Integer)
On Error GoTo Form_Close_Err

Me.Filter = ""
Me.FilterOn = False

Form_Close_Exit:
Exit Sub

Form_Close_Err:
MsgBox Err.Number & " - " & Err.Description
Resume Form_Close_Exit

End Sub

but it doesnt seem to work

 

[ghudson]

Odd but I was not able to actually clear the value in the Filter property of the form either. But when I use the code I gave you it does work so that the form is not filtered by the value that was in the forms filter property. Also, turning the filter option on [=True] or off [=False] works as well.

Why doesn't the code do what you "need" in the forms OnOpen event? The forms record source will not be filtered if you use the code I gave you above in your forms OnOpen event. Why do you care if the records are filtered when the form is closed?

 

[Crash1hd]

the form is a calander and I am useing the filter so that when the form is opened if the filter has data it goes to the current date if not then it goes to todays date for example

If Not IsNull(Me.OpenArgs) Then
If Me.Filter <> "" Then
Me.ocxCalendar.Value = Me.Filter
Else
Me.ocxCalendar.Value = Date
End If
Else
DoCmd.Close
MsgBox "Parent window is not open!", vbCritical
End If

This is in the form that opens (which is the calander) and when I dont try anything for some reason when the calander is closed it keeps the last used filter in the box so the next problem is when I go to open it on an empty box it goes to the filter date so if the filter date is january 1 2001 then that is what shows up even though there is nothing in current txt box

This is the calander form code all the form contains is a calander

Option Compare Database
Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
    If Me.Filter <> "" Then
        Me.ocxCalendar.Value = Me.Filter
    Else
        Me.ocxCalendar.Value = Date
    End If
Else
    DoCmd.Close
    MsgBox "Parent window is not open!", vbCritical
End If
End Sub
Private Sub ocxCalendar_Click()
myarray = Split(Me.OpenArgs)
'myarray(0) = Txtbox name or txtDate.Name
'myarray(1) = Form name or Me.Name

    Forms(myarray(1)).Controls(myarray(0)).Value = Me.ocxCalendar.Value
    DoCmd.Close
End Sub

the other form is a txtbox that has this code

Private Sub txtDate_Click()
    DoCmd.OpenForm "Calendar", , , txtDate.Value, , , (txtDate.Name & " " & Me.Name)
End Sub

 

[DB7]

CrashIhdm, maybe you & ghudson have considered this already, but, could it be in the chain of events? Would Unload be more appropriate?
I know when opening, Load happens before open, I assume the reverse when closing. But, just in case, or something along these lines (changing the event)?

Either way, good luck!

 

[Crash1hd]

well as per this post it says that open happens before load http://www.access-programmers.co.uk/forums/showthread.php?t=73628

as well I tried it and doesnt work my solution was to stop useing the filter altogether and just use the Me.OpenArgs and use a myarray = Split(Me.OpenArgs)
into 3 instead of 2 and vola problem solved

 

[ghudson]

Direct from the Access help files...

If your application can have more than one form loaded at a time, use the Activate and Deactivate events instead of the Open event to display and hide custom toolbars when the focus moves to a different form.

The Open event occurs before the Load event, which is triggered when a form is opened and its records are displayed.

When you first open a form, the following events occur in this order:
Open > Load > Resize > Activate > Current

The Close event occurs after the Unload event, which is triggered after the form is closed but before it is removed from the screen.

When you close a form, the following events occur in this order:
Unload > Deactivate > Close

When the Close event occurs, you can open another window or request the user's name to make a log entry indicating who used the form or report.

If you're trying to decide whether to use the Open or Load event for your macro or event procedure, one significant difference is that the Open event can be canceled, but the Load event can't. For example, if you're dynamically building a record source for a form in an event procedure for the form's Open event, you can cancel opening the form if there are no records to display. Similarly, the Unload event can be canceled, but the Close event can't.

 

[DB7]

Sorry for the error there, guys.
Thank-you ghudson, that was very clear & informative!

 

[WmRJohnson]

Thank you ghudson for the correct code. I was over an hour getting nowhere until I realized I was using the OnLoad event. It works just great on the OnOpen or OnClose event.

I also noticed that the Filter in Properties continues to be populated with the last filter used (but doesn't appear to have an affect any longer).

B Johnson