filtering records on nested subform

[Guest]

hi all,

i have a nested subform (in ds view) called 'DaysView' sitting in my main
form called 'Screening Log (Edit Only)'. the linking via Parent/Child fields
is a little convoluted and may or may not bear upon the solution we can
conjure up (and ftb i'll defer going into them); suffice it to say that
there's a field on 'DaysView' called 'Date of Visit' (this is its subform
name, its name on the underlying table is 'DateOfVisit'). all this having
been said, what i'm aiming for when the user is looking at the form/subform
is for the user to be shown only those records on the subform that come on or
after the 'Date' that the user is using the application (which btw is an
A2K-er). so, i have tried a couple of permutations of this and that idea, but
not with a lot of 'luck'. i'm debating w/ myself whether we should allow any
user input over the filtering action once the technique's sussed out or
whether there ought not to be (i.e. the user has no choice but to see only
those subform records that have 'Date of Visit' entries falling on or later
than the same 'Date' that they're being viewed.

any ideas'd be greatly app'd!

-ted

 

[Allen Browne]

If you always want to exclude any dates before today from the subform,
create a query to act as its RecordSource, and in the Criteria row of query
design, under the DateOfVisit field, enter:

= Date()

If you want to provide a button so the user can turn this filter on or off,
you could toggle the subform's filter by placing a button on the main form,
and using this kind of code in its Click event:

With Me.[NameOfYourSubformHere].Form
If .FilterOn Then
.FilterOn = False 'Turn the filter off.
Else
.Filter = "[DateOfVisit] >= Date()"
.FilterOn = True
End If

Ted, if you do this, and you are also filtering the main form, Access is
likely to get confused. More info on this problem:
Incorrect filtering of forms and reports
at:
http://allenbrowne.com/bug-02.html

 

[Guest]

thanks lots for all the good insider-isms, allen. i will definitely
experiment with the scenarios you postulate and see what seems to fly in this
environment (i.e. w/ my user(s)).

all the best,

-ted

If you always want to exclude any dates before today from the subform,
create a query to act as its RecordSource, and in the Criteria row of query
design, under the DateOfVisit field, enter:

= Date()

If you want to provide a button so the user can turn this filter on or off,
you could toggle the subform's filter by placing a button on the main form,
and using this kind of code in its Click event:

With Me.[NameOfYourSubformHere].Form
If .FilterOn Then
.FilterOn = False 'Turn the filter off.
Else
.Filter = "[DateOfVisit] >= Date()"
.FilterOn = True
End If

Ted, if you do this, and you are also filtering the main form, Access is
likely to get confused. More info on this problem:
Incorrect filtering of forms and reports
at:
http://allenbrowne.com/bug-02.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

i have a nested subform (in ds view) called 'DaysView' sitting in my main
form called 'Screening Log (Edit Only)'. the linking via Parent/Child
fields
is a little convoluted and may or may not bear upon the solution we can
conjure up (and ftb i'll defer going into them); suffice it to say that
there's a field on 'DaysView' called 'Date of Visit' (this is its subform
name, its name on the underlying table is 'DateOfVisit'). all this having
been said, what i'm aiming for when the user is looking at the
form/subform
is for the user to be shown only those records on the subform that come on
or
after the 'Date' that the user is using the application (which btw is an
A2K-er). so, i have tried a couple of permutations of this and that idea,
but
not with a lot of 'luck'. i'm debating w/ myself whether we should allow
any
user input over the filtering action once the technique's sussed out or
whether there ought not to be (i.e. the user has no choice but to see only
those subform records that have 'Date of Visit' entries falling on or
later
than the same 'Date' that they're being viewed.

 

[Guest]

just following up, allen.

in testing out the behavior, what i find is that if i navigate to another
main form record while the subform filter is activated (because i pressed the
mainform cmd button i created just once) OR i press another mainform cmd
button that was already created in this app'n which serves to close the form,
then i get an 'Enter Paramater' type prompt. IF on the other hand, i attempt
to either navigate to another mainform record OR close the mainform after i
have de-activated the subform filter by clicking a second time, then no
worries. this is undoubtedly simplistic, but would it be possible to
de-activate the filter prior to attempting to do either of those actions? it
feels as if that's what missing in all this.

-ted

If you always want to exclude any dates before today from the subform,
create a query to act as its RecordSource, and in the Criteria row of query
design, under the DateOfVisit field, enter:

= Date()

If you want to provide a button so the user can turn this filter on or off,
you could toggle the subform's filter by placing a button on the main form,
and using this kind of code in its Click event:

With Me.[NameOfYourSubformHere].Form
If .FilterOn Then
.FilterOn = False 'Turn the filter off.
Else
.Filter = "[DateOfVisit] >= Date()"
.FilterOn = True
End If

Ted, if you do this, and you are also filtering the main form, Access is
likely to get confused. More info on this problem:
Incorrect filtering of forms and reports
at:
http://allenbrowne.com/bug-02.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

i have a nested subform (in ds view) called 'DaysView' sitting in my main
form called 'Screening Log (Edit Only)'. the linking via Parent/Child
fields
is a little convoluted and may or may not bear upon the solution we can
conjure up (and ftb i'll defer going into them); suffice it to say that
there's a field on 'DaysView' called 'Date of Visit' (this is its subform
name, its name on the underlying table is 'DateOfVisit'). all this having
been said, what i'm aiming for when the user is looking at the
form/subform
is for the user to be shown only those records on the subform that come on
or
after the 'Date' that the user is using the application (which btw is an
A2K-er). so, i have tried a couple of permutations of this and that idea,
but
not with a lot of 'luck'. i'm debating w/ myself whether we should allow
any
user input over the filtering action once the technique's sussed out or
whether there ought not to be (i.e. the user has no choice but to see only
those subform records that have 'Date of Visit' entries falling on or
later
than the same 'Date' that they're being viewed.