Reconstruct Filter string on Subform

[DocBrown]

Hello Experts,

I've searched this forum and can't find a solution to my problem. I hope
someone has an idea.

I have a main form that has a command button that opens a subform via the
following code:

strDocName = "TimeLogViewSub"
strLinkCriteria = "[VolunteerID] = Forms![Volunteers]![txtVolunteerID]"
DoCmd.OpenForm strDocName, , , strLinkCriteria

This subform displays the login/logout times for the volunteer selected on
the main form. This works as is should and when the selected record on the
main form changes, the subform is updated to display the data for the new
volunteer.

But I want to add an additional filter condition so that the user can select
the number of days of logging data to be displayed. I have a command button
on the subform that is able to do that. In the subform Open event I'm saving
the original filter string, which was passed via the OpenForm method, and
when I want to apply the date filter, I construct a new filter by combining
the original string with the new date criteria.

This works when the command button is hit, but as soon as I select a new
record on the subform, the date filter is lost. It appears that the subform's
Filter string is reset back to the string originally set when the form was
opened whenever the record changes on the main form. I can't seem to find an
event or place in the code where I can intercept the filter update or
reconstruct the one I want at the time the main record is changed and have it
take effect. I've tried using the OnCurrent, OnOpen, OnFilter, OnApplyFilter,
OnActivate.

When the main record changes only the ApplyFilter and OnCurrent events
appear to be active. When I construct the filter in these places, they don't
take effect.

This is an example of the filter I'm constructing.

Filter:
[VolunteerID] = Forms![Volunteers]![txtVolunteerID] AND ([LogDate] >=
#08/03/2008#) AND ([LogDate] < #08/11/2008#)

The particular dates included are obtained via another dialog box.

Is there another way I should approach this?

Thank you so much for your help.
John

 

[Dale Fye]

I would get rid of the dialog box for entering the dates, and make them
unbound text boxes on either the main form, or in the header of what you are
calling a subform.

Then, in the main forms Current event (I assume that is where you are
running the code that opens the detail form), I would check to see whether
the form is already loaded. If it is, I would just requery the form, rather
than reopening it. Something like:

Private Sub Form_Current

if currentproject.Allforms("TimeLogViewSub").isloaded Then
Forms("TimeLogViewSub").Requery
else
strDocName = ...
strLinkCriteria = ....
Docmd.Openform strDocName, , , strLinkCriteria
endif

End sub

Actually, the more I think about this, the more likely it is that you have a
command button that opens the TimeLogViewSub form, in which case you can drop
the Else and all the code that is between there and the EndIF statement.

This way, the form would be opened when you click the command button, but
when you go to a new record on the main form, it would just requery the
TimeLogViewSub form.

BTW, a subform is a form embedded within another form. What you are doing
is opening up what I would call a Details form, based on a selection in the
main form.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hello Experts,

I've searched this forum and can't find a solution to my problem. I hope
someone has an idea.

I have a main form that has a command button that opens a subform via the
following code:

strDocName = "TimeLogViewSub"
strLinkCriteria = "[VolunteerID] = Forms![Volunteers]![txtVolunteerID]"
DoCmd.OpenForm strDocName, , , strLinkCriteria

This subform displays the login/logout times for the volunteer selected on
the main form. This works as is should and when the selected record on the
main form changes, the subform is updated to display the data for the new
volunteer.

But I want to add an additional filter condition so that the user can select
the number of days of logging data to be displayed. I have a command button
on the subform that is able to do that. In the subform Open event I'm saving
the original filter string, which was passed via the OpenForm method, and
when I want to apply the date filter, I construct a new filter by combining
the original string with the new date criteria.

This works when the command button is hit, but as soon as I select a new
record on the subform, the date filter is lost. It appears that the subform's
Filter string is reset back to the string originally set when the form was
opened whenever the record changes on the main form. I can't seem to find an
event or place in the code where I can intercept the filter update or
reconstruct the one I want at the time the main record is changed and have it
take effect. I've tried using the OnCurrent, OnOpen, OnFilter, OnApplyFilter,
OnActivate.

When the main record changes only the ApplyFilter and OnCurrent events
appear to be active. When I construct the filter in these places, they don't
take effect.

This is an example of the filter I'm constructing.

Filter:
[VolunteerID] = Forms![Volunteers]![txtVolunteerID] AND ([LogDate] >=
#08/03/2008#) AND ([LogDate] < #08/11/2008#)

The particular dates included are obtained via another dialog box.

Is there another way I should approach this?

Thank you so much for your help.
John

 

[DocBrown]

Hi Dale,

Well I'll be gosh darned. Putting the requery in the Main form WORKED!

You are correct, this is actually a seperate Detail Form. And your idea
about using unbound text boxes on the form is a good idea.

I had tried putting a Me.Requery in the routines of the Detail form which
didn't work. So I was skepical that this would work. *grin* That requery must
cause the Detail form to reapply all data in the form after it was done with
all it's processing. If that's not it, I don't know why it works.

Thanks!!
John

I would get rid of the dialog box for entering the dates, and make them
unbound text boxes on either the main form, or in the header of what you are
calling a subform.

Then, in the main forms Current event (I assume that is where you are
running the code that opens the detail form), I would check to see whether
the form is already loaded. If it is, I would just requery the form, rather
than reopening it. Something like:

Private Sub Form_Current

if currentproject.Allforms("TimeLogViewSub").isloaded Then
Forms("TimeLogViewSub").Requery
else
strDocName = ...
strLinkCriteria = ....
Docmd.Openform strDocName, , , strLinkCriteria
endif

End sub

Actually, the more I think about this, the more likely it is that you have a
command button that opens the TimeLogViewSub form, in which case you can drop
the Else and all the code that is between there and the EndIF statement.

This way, the form would be opened when you click the command button, but
when you go to a new record on the main form, it would just requery the
TimeLogViewSub form.

BTW, a subform is a form embedded within another form. What you are doing
is opening up what I would call a Details form, based on a selection in the
main form.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hello Experts,

I've searched this forum and can't find a solution to my problem. I hope
someone has an idea.

I have a main form that has a command button that opens a subform via the
following code:

strDocName = "TimeLogViewSub"
strLinkCriteria = "[VolunteerID] = Forms![Volunteers]![txtVolunteerID]"
DoCmd.OpenForm strDocName, , , strLinkCriteria

This subform displays the login/logout times for the volunteer selected on
the main form. This works as is should and when the selected record on the
main form changes, the subform is updated to display the data for the new
volunteer.

But I want to add an additional filter condition so that the user can select
the number of days of logging data to be displayed. I have a command button
on the subform that is able to do that. In the subform Open event I'm saving
the original filter string, which was passed via the OpenForm method, and
when I want to apply the date filter, I construct a new filter by combining
the original string with the new date criteria.

This works when the command button is hit, but as soon as I select a new
record on the subform, the date filter is lost. It appears that the subform's
Filter string is reset back to the string originally set when the form was
opened whenever the record changes on the main form. I can't seem to find an
event or place in the code where I can intercept the filter update or
reconstruct the one I want at the time the main record is changed and have it
take effect. I've tried using the OnCurrent, OnOpen, OnFilter, OnApplyFilter,
OnActivate.

When the main record changes only the ApplyFilter and OnCurrent events
appear to be active. When I construct the filter in these places, they don't
take effect.

This is an example of the filter I'm constructing.

Filter:
[VolunteerID] = Forms![Volunteers]![txtVolunteerID] AND ([LogDate] >=
#08/03/2008#) AND ([LogDate] < #08/11/2008#)

The particular dates included are obtained via another dialog box.

Is there another way I should approach this?

Thank you so much for your help.
John