filtering records on nested subform

[Ted]

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

 

[Ted]

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.

 

[Ted]

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.

 

[Ted]

postscript to my follow-up, allen:

i notice that when i click on the filter cmd button a 2nd time to
de-activate it, that this has the effect of shooting me up to the mainform
corresponding with the first record in the database, i.e. if i'm filtering
the subform corresponding with the 3rd record in the database and click the
cmd button i built on the mainform another time, then i'm whisked to the top
of the mainform and the record is the 1st one in the db. it might take the
user a li'l getting used to.

i'm just wondering out loud whether it wouldn't be possible to get this by
giving the user a 'choice on the mainform between viewing records in the
sub-form which came from the filtered query you proposed vs. viewing subform
records that came from the unfiltered record source?

-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.

 

[Allen Browne]

Ted, I'm not clear what parameter is being requested. Access will ask for a
parameter if it is unable to identify a name with a field, so the name of
the requested parameter will tell you what name it does not understand.

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

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

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

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.

 

[Allen Browne]

This might depend what's in your code. It sounds like Access is unfiltering
the main form, when you were attempting to unfilter the subform.

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

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

postscript to my follow-up, allen:

i notice that when i click on the filter cmd button a 2nd time to
de-activate it, that this has the effect of shooting me up to the mainform
corresponding with the first record in the database, i.e. if i'm filtering
the subform corresponding with the 3rd record in the database and click
the
cmd button i built on the mainform another time, then i'm whisked to the
top
of the mainform and the record is the 1st one in the db. it might take the
user a li'l getting used to.

i'm just wondering out loud whether it wouldn't be possible to get this by
giving the user a 'choice on the mainform between viewing records in the
sub-form which came from the filtered query you proposed vs. viewing
subform
records that came from the unfiltered record source?

-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

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.

 

[Ted]

my primal inclination is to forget about the calligraphy and have the user
live w/ it. however, my superego's acting up a bit and would like to know
what the diagnostic strategy to handle the lacuna in your mention is. are you
suggesting an inspection of the VBA code for the mainform's module? i took a
quick look and (disclaimer: i'm a relative vba newbie) found nothing
suspicious (please have a gander below).

Private Sub Form_Current()
ToggleColor
End Sub

Private Sub Form_Open(Cancel As Integer)
LAS_EnableSecurity Me
DoCmd.Maximize
End Sub

Private Sub Frame1_AfterUpdate()
Select Case Frame1.Value
Case 1
Me.Onlistdate.SetFocus
Case 2
Me.RegisteredDate.SetFocus
Case 3
Me.OnStudyDate.SetFocus
Case 4
Me.TXEndedDate.SetFocus
Case 5
Me.OffStudyDate.SetFocus
Case 6
Me.LTFUDate.SetFocus
Case 7
Me.DateDth.SetFocus
Case Else
End Select
End Sub

Private Sub Frame1_BeforeUpdate(Cancel As Integer)
ToggleColor
End Sub
Private Sub IRB_Number_AfterUpdate()
Me.IRB_Number.Requery
' Me.IRB_Number.Locked = True
Me.SponsorIDNumber.SetFocus
Me.SponsorIDNumber = "Enter Sponsor"
Me.SponsorIDNumber.Requery
Me.Study_Title = "Enter Title"
Me.Study_Title.Requery
End Sub

Private Sub Last_Name_AfterUpdate()
Me.Last_Name.Requery
Me.First_Name.SetFocus
Me.First_Name = " "
Me.First_Name.Requery
Me.MI = " "
Me.MI.Requery
Me.MR_Number = 0
Me.MR_Number.Requery
Me.Screening_ID = 0
Me.Screening_ID.Requery
End Sub

Private Sub MI_AfterUpdate()
Me.MI.Requery
Me.MR_Number.SetFocus
Me.MR_Number = 0
Me.MR_Number.Requery
Me.Screening_ID = 0
Me.Screening_ID.Requery
End Sub

Private Sub MR_Number_AfterUpdate()
Me.MR_Number.Requery
Me.Screening_ID.SetFocus
Me.Screening_ID = 0
Me.Screening_ID.Requery
End Sub

Private Sub Patient_ID_AfterUpdate()
Me.Screening_ID.Requery
End Sub
Private Sub Close_Click()
On Error GoTo Err_Close_Click


DoCmd.Close acForm, "Screening Log (Edit Only)", acSaveYes

Exit_Close_Click:
Exit Sub

Err_Close_Click:
MsgBox Err.description
Resume Exit_Close_Click

End Sub

Private Sub Outcome_BeforeUpdate(Cancel As Integer)
Dim Response
'If Me.Outcome = "Off Study (PD)" Or Me.Outcome = "Off Study (Toxicity)" _
Or Me.Outcome = "Off Study (Completed)" Or Me.Outcome = "Off Study (DEATH)" _
Or Me.Outcome = "Off Study (Withdraw)" Then
'Response = MsgBox("You are not permitted to use this outcome in this
screen", 16, "ILLEGAL ACTION")
'End If
End Sub

Private Sub PrimaryID_AfterUpdate()
Me.PrimaryID.Requery
' Me.PrimaryID.Locked = True
Me.IRB_Number.SetFocus
Me.IRB_Number = 0
Me.IRB_Number.Requery
Me.SponsorIDNumber = "Enter Sponsor"
Me.SponsorIDNumber.Requery
Me.Study_Title = "Enter Title"
Me.Study_Title.Requery
End Sub
Private Sub SponsorIDNumber_AfterUpdate()
Me.SponsorIDNumber.Requery
' Me.SponsorIDNumber.Locked = True
Me.Study_Title.SetFocus
Me.Study_Title = "Enter Title"
Me.Study_Title.Requery
End Sub
Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click


DoCmd.GoToRecord , , acNewRec

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.description
Resume Exit_Add_Record_Click

End Sub

Private Sub FilterDates_Click()
On Error GoTo Err_FilterDates_Click


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


Exit_FilterDates_Click:
Exit Sub

Err_FilterDates_Click:
MsgBox Err.description
Resume Exit_FilterDates_Click

End Sub

This might depend what's in your code. It sounds like Access is unfiltering
the main form, when you were attempting to unfilter the subform.

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

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

postscript to my follow-up, allen:

i notice that when i click on the filter cmd button a 2nd time to
de-activate it, that this has the effect of shooting me up to the mainform
corresponding with the first record in the database, i.e. if i'm filtering
the subform corresponding with the 3rd record in the database and click
the
cmd button i built on the mainform another time, then i'm whisked to the
top
of the mainform and the record is the 1st one in the db. it might take the
user a li'l getting used to.

i'm just wondering out loud whether it wouldn't be possible to get this by
giving the user a 'choice on the mainform between viewing records in the
sub-form which came from the filtered query you proposed vs. viewing
subform
records that came from the unfiltered record source?

-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


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.

 

[Allen Browne]

Fair enough. My primary inclination is to not start on working through all
that code, now.

 

[Ted]

hi allen,

you know what they say about automation having 'feet of clay', well those
feet are now working! at least in response to my clicking on the cmd button
we put on the main form this today; i had noticed that regardless of which
mainform record i was on, when i clicked the cmd btn to filter the subformers
that the status bars of both the main and subforms read each was being
filtered! i played around with the properties of each' and noticed a stray
left '[' in the 'Filter' property of the main form which i excised. trying
this out again i find that only the subform's records are (on the status bar)
labeled as filtered when i click the cmd button and clicking it again does
not shoot the user up to the first record of the mainform's recordsource. so
that's good.

where than leaves us though is having to tell the user that they need to
unfilter the subform before a) closing the form OR b) going to another
mainform record. am i correct?

best,

-ted

 

[Allen Browne]

A) is not relevant.

B) is up to you, if you need the filter removed.

 

[Ted]

allen,

just as i thought it was safe to go back in the water, i now find that
things have reverted -- meaning that when i filter the subform (although it
no longer displays a 'Filtered' status on the mainform as well as the
subform) and proceed to click the cmd button a second time, the next thing
that happens is the application takes the user to the mainform for the first
record in the mainform's recordsource :-( ugly and weird stuff.

i sort of read your weblink's stuff on the bugs inherent in all this and am
getting the feeling that until ms gets its house in order there's not that
much that can be done in the context of what's befalling my app'n.

in terms of a strategy to 'workaround' all of that, a) could the closeform
btn on the mainform include something that would handle the removing of the
filter just prior to closing the form and b) could maybe two navigation
buttons be added to the mainform to handle the moving forward/reverse on the
main table which negated the filter when it was on? my guess is that 'yes' it
would be, but what would the missing command(s) be like if my hunch's right?

best,

-ted

 

[Allen Browne]

Time to leave the issue with you, now Ted.