Dynamically Change Recordsource of a Subreport (2 Viewers)

[TheSearcher]

If txt_Sex on Main Report = "M" then I want the recordsource of Subreport1 to be q_Male else q_Female.

If Parent!txt_Sex = "M" Then
    Me.RecordSource = q_Male
Else
    Me.RecordSource = q_Female
End If

Is this the correct code?
Do I place it in the subreport? If so, on which event?
Any help would be appreciated.

 

[theDBguy]

No guarantees but the using the Detail Format event to manipulate the subreport.

 

[TheSearcher]

I succeeded by placing the code in the "On Activate" event of the parent report.

 

[theDBguy]

I succeeded by placing the code in the "On Activate" event of the parent report.

Congratulations!

 

[DHookom]

This question raises a red flag to me. If the two queries, q_Male and q_Female, have the same fields and source data (other than the gender), I would consider adding a gender column to one query with either an M or F value. Then use the Link Master/Child properties of the report and subreport to filter the subreport data by gender. This would require no code and only a single record source query.

 

[TheSearcher]

They have the same fields but not the same data.
The common field is called "Options" but the data for male and female options differ.

 

[tvanstiphout]

They have the same fields but not the same data.
The common field is called "Options" but the data for male and female options differ.

Options??
What is the ID field that ties the child records to the parent record?
Can you show a screenshot of the relevant tables laid out in the Relationships diagram?

 

[TheSearcher]

It is a field called Note_Id.
I should also mention that "male" and "female" along with the names of the recordsource queries are not the actual names. I made them up so that my question could be more easily understood. To describe what I was encountering using the actual names would have been far more complicated. Anyway, my problem is solved and I thank everyone for their input!