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

Put it in the Click event of the command button you have for this purpose.

I should have also mentioned that when you change a subform's RecordSource,
Access is likely to reset the subform control's LinkMasterFields and
LInkChildFields to whatever it wishes, so you may need to reset these 2
properties as well.

That's probably as far as I can take you with this one, Ted. Hope you are
able to get into a decent course.

--
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:4678A827-15D3-40BC-802A-50C1ACC3E520@xxxxxxxxxxxxxxxx
> allen, how would i integrate the code you generously give into my VBA's.
> please be specific. sorry 'bout the naivete, but i'm still lobbying for a
> chance to attend a half-decent course in it.
>
> -ted
>
>
> "Allen Browne" wrote:
>
>> 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