filtering Sub Forms VBA

S

Sangee

Hai
Could Some one help me with this.
I am trying to Generate a report.
I have a main table called tSurveySchedule (ID, Date
CompanyID,SurveyTypeID, CompanyContactID,Notes)
My Parent Report Displays a set of Questions based on the SurveyTypeID
and CompanyID.
These Questions for the Survey have sub questions stored in a different
Table . And i have a subreport generated, which displays all the
answers based on the Qustion number. Till here it is fine.
My problem is , i want to display these records based on a date range.
This range in chosen by the user. But i wasent successful till now.
I tried
1)
SELECT tSurveyQuestionMain.QuestionNumber,
tSurveyResultSub.SurveyScheduleID,
tSurveyResultSub.SurveyQuestionSubID, tSurveyResultSub.Notes,
tSurveyQuestionSub.Question FROM tSurveyResultSub INNER JOIN
tSurveyQuestionSub ON tSurveyResultSub.SurveyQuestionSubID =
tSurveyQuestionSub.ID INNER JOIN tSurveySchedule ON
tSurveyResultSub.SurveyScheduleID = tSurveySchedule.ID INNER JOIN
tSurveyQuestionMain on
tSurveyQuestionSub.SurveyQuestionMainID=tSurveyQuestionMain.ID where
tSurveySchedule.[date]>= Forms!fSurveyAnalysisCriteria.Form.Controls(
"txtStartDate" ) and tSurveySchedule.[date]<
Forms!fSurveyAnalysisCriteria.Form.Controls( "txtEndDate")
(I tried it with ## too -No luck)
--But this says it cannot find the form or control . The query is fine
if i dont use the where condition.

2) I tried using a filter
Me.rSurveyAnalysisSubReport1.Report.Filter = "Date< '"
&tSurveySchedule.[date]< Forms!fSurveyAnalysisCriteria.Form.Controls(
"txtEndDate")
Me.rSurveyAnalysisSubReport1.Report.FilterOn = True
Me.RecordSource = "Select * from tSurveyQuestionMain where
SurveyTypeID=1 "
--It said i made and illegal reference to the form

3) I tried setting a query string on the parent form as the sub forms
record source
--
It would say the form is not yet open

Is there some way i can refer to this control -
Forms!fSurveyAnalysisCriteria.Form.Controls( "txtEndDate") and pass it
to the record source of my subform from the Query Designer of VBA or is
there some method on the main form that will help me requery the child
form.
Any help appreciated!
 
Top