Re: filtering records in sub-form has problems in a2k

Ted, I've just re-read this thread several times.

Your original code explicitly removes the filter of the subform, so it
should not affect the current record of the main form. However, if the main
form is filtered as well, Access can easily get confused between the main
form's filter and the subform's filter. Details in:
Incorrect filtering
at:
http://allenbrowne.com/bug-02.html

A way to work around that issue is to change the RecordSource of the subform
instead of applying a filter.

Example:
Const strcUnfiltered = "SELECT DaysView.* FROM DaysView;"
Const strcFiltered = "SELECT DaysView.* FROM DaysView WHERE
[DateOfVisit] >= Date();"
With Me.[DaysView].Form
If .RecordSource <> strcUnfiltered Then
.RecordSource = strcUnfiltered
Else
.RecordSource = strcFiltered
End If
End With

You can still set the colors and other properties. And you can still find
the current recrod again in the subform after you "unfilter" it. This
approach should not change the current record of the main form.

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ted" <Ted@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2F18B30F-570E-4866-8EAB-DB551C043D1D@xxxxxxxxxxxxxxxx
> the code filters just the subform's records and clicking the cmdbutton
> will
> disable the filtration.
>
> -ted
>
> "Allen Browne" wrote:
>
>> So your code is removing the main form's filter, whereas you want it to
>> remove the subform's filter?
>>
>>
>> "Ted" <Ted@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:82C4EACA-8229-4B4E-AC33-AECD19F95520@xxxxxxxxxxxxxxxx
>> > the core issue revolves 'round what happens on REMOVING the filter
>> > alone,
>> > allen. it (the code i described early in this thread) seems to work
>> > successfully in filtering the subform's records. it's when you click on
>> > it
>> > a
>> > 2nd time to remove the filter that it scoots the user to the mainform
>> > and
>> > the
>> > first recdord in its underlying control source.


.

Loading