Can't clear filter or orderby perminantly on form

[bignose2]

Access 2007 .mbd
Hi

I have a tabular form that is based on a select statement (Query) in the source property. It filters & sorts from this select statement.

I have just added a
Me.Filter = "[Form1].[InDate] = #" & MyValue & "# "
Me.OrderBy = "Indate DESC"

This all works fine, filters & sorts on the existing results.

However this new filter & sort seems to remain no matter what I do

I have used every variation one me.filter ="" with Me.FilterOn = True, False & every combination.

Me.Filter="" & Me.FilterOn = False does initially seem to clear the lsit to show all records but as soon as type anything that filters the Form on the original, existing underlying select statement the extra Filter Property & Sort properties are occupied with the last filter/sort.

I have manually deleted any remaining Filter Or Sort properties direct from design view, saved, compacted & saved again.

Once I use the new me.filter it will always returned as soon an any filter on the form is applied.
I definately am not setting it again anywhere.
Is there something better than me.filter ="", seen clearallfilters mentioned but not sure if this would apply just to the current form but reluctant to use a blanket clear as I do have quite a lot of other things going on.


Not sure when but many months (If not years) ago my form source stopped being a query and started using the query (sql) direct from the source property.
I have left it this way as not sure if any consequence of moving back but don't remember how or how it happened. What are the pros cons of using a separate query.


Thanks I/A

 

[billmeye]

Why don't you try adding the Criteria [Forms]![Form1]![MyValue] to your InDate field in your record source and use Me.requery in the AfterUpdate event for MyValue and avoid using the filter. I don't like using filters since the user can remove or change them which takes away control. Also, Access saves your latest filter as the default. You can also set sorting in the record source if it is always descending or ascending.

I only use separate queries if it is something I will use more globally, otherwise I just adjust the form or reports record source as needed.

 

[bignose2]

Hi,

Many thanks for your reply

All very good ideas & suggestions.

I assume you mean by
"adding the Criteria [Forms]![Form1]![MyValue] to your InDate field in your record source"
that I change the whole select statement in the source to the new filter requirements & change it back afterwards.

The form is mostly used & based on quite a complex query/select statement so whilst don't think too hard to do it would not be pretty in the code.

Seems much cleaner looking to just add a small Me.Filter = "x" rather than a 5 line select statement with everything in it.

I assume there is no way to add or remove parts of the select statement.

I have managed to get it working with the clearallfilters & clearAllSorts docmd statement so might stick with it for the moment although you points are valid.

On searching for a solution I notice many,many people have the same problems the filter not being cleared, they are completely confused why the filter would keep returning so it is not just me. I still can't understand why when you have set it to "" it should come back as the last working filter even if it is then the Default, surely "" should be the default.

Thanks again.

 

[billmeye]

You would not need to change your SELECT statement for each change to MyValue, you would add a Me.requery statement to your MyValue After Update event. Forgive me if you are already familiar, but the Criteria can be a single value, range of values, etc. and you can have a Criteria for multiple fields at the same time. My only concern with Filters is they are temporary and can be undone by the user very easily, they lack control.

Your SELECT statement, SQL, is dynamic and can be changed at any time. If this form is not for data entry, but only to retrieve info, anything can be done. Sometimes things can get overly complex. If you would like to share your SELECT statement I might see something useful.

 

[Galaxiom]

The Filter property is retained exactly as you say but it does nothing when FilterOn is False so it really doesn't matter. As such, there is no problem to be solved.

With regards to the alternative techniques, "cleaner looking" should not be the priority.

Best performance is achieved by returning only the records you need so including the Where clause in the RecordSource query is better than returning everything and filtering.