Filter subform from Main Form

[Anand]

Hi
I am using an A2k application. I have to filter the
records in a subform based on a date range entered by user
in the Main form. I am using the following code:

DoCmd.ApplyFilter, "[frmDailyAttendance].Form![AttDate]
BETWEEN " & StartDate & " AND " & EndDate & ""

It doesnt work. When I run the code it prompts me to
enter the value for "[frmDailyAttendance].Form![AttDate]"

Is it possible to filter a subform based on values in a
Main form?
If so, where am I going wrong?

TIA
Anand

 

[Van T. Dinh]

Assuming:

* The TextBoxes [StartDate] and [Endate] are on the Main Form.
* The Field [AttDate] is included in the RecordSource for the Subform.
* You want to Filter the Subform with code running in the context of the
Main Form.

then try:

Me.SubformControlName.Form.Filter = _
"[AttDate] BETWEEN " & Format(Me.[StartDate], "\#mm/dd/yyyy\#" & _
" AND " & Format(Me.[EndDate], "\#mm/dd/yyyy\#")
Me.SubformControlName.Form.FilterOn = True

You need to find the name of the SubformControl in the DesignView of the
Main Form. Note that the name of the SubformControl may be different from
the name of the Form you used as the Subform (or more accurately, the name
of the Form you used as the SourceObject for the SubformControl).

 

[Mark Phillipson]

Hi Anand,

Try code like this:


'SubFmCtl001 = Sub Form Control name
'YourDate = name of field
'Build the filter
Forms(Me.Name)("SubFmCtl001").Form.Filter = "[YourDate] Between #" _
& Format(Me.txtStartDate, "mm/dd/yyyy") _
& "# And #" _
& Format(Me.txtEndDate, "mm/dd/yyyy") & "#"
'Apply the filter
Forms(Me.Name)("SubFmCtl001").Form.FilterOn = True

--

Cheers
Mark

Free Access/Office Add-Ins at:
http://mphillipson.users.btopenworld.com/