Conditional Subreport Record Source

[Balfour211]

I am using Access 2003. I have a main report page that has a couple of
Subreports on it. I want one of the subreports to open up using a different
Record Source depending on Who opened it up. I am using an If...Then
statement to determine who is opening the report, and using different SQL
strings in code to provide the different Record Sources.

I have put the If...Then statement in the OnOpen Event of the subreport.
When I open the subreoprt all by itself, everything works. When I open the
Main Report with the subreport in it, it gives me an error and the Debug
command takes me back to my If...Then statement.

I am thinking my problem is which Event (OnOpen) I am using to decide the
Record Source. If not, I will start looking at my SQL strings and my verbage
in the If...Then statement.

If Forms![frmDate_Range]![ckAll].Value = True Then
Reports!Training_Sniffs_Obedience.RecordSource = sqlControl_All
Else
Reports!Training_Sniffs_Obedience.RecordSource = sqlControl_1
End If

sqlControl_All and sqlControl_1 are both declared as Strings at the
beginning and the SQL strings are after the declaration and before the
If...Then statement.

Any Help would be appreciated.
Balfour211

 

[Duane Hookom]

I would use a saved query as the record source of the subreport. Prior to
opening the report with the subreport, use a little DAO code to change the
SQL of the query:

Dim strSQL As String
If Me.chkAll = True then
strSQL = sqlControl_All
Else
strSQL = sqlControl_1
End If
CurrentDb.QueryDefs("qryForSubreport").SQL = strSQL

 

[Balfour211]

Nicely Done. Works Great.
Thanks for the quick response
balfour211

I would use a saved query as the record source of the subreport. Prior to
opening the report with the subreport, use a little DAO code to change the
SQL of the query:

Dim strSQL As String
If Me.chkAll = True then
strSQL = sqlControl_All
Else
strSQL = sqlControl_1
End If
CurrentDb.QueryDefs("qryForSubreport").SQL = strSQL


--
Duane Hookom
Microsoft Access MVP

I am using Access 2003. I have a main report page that has a couple of
Subreports on it. I want one of the subreports to open up using a different
Record Source depending on Who opened it up. I am using an If...Then
statement to determine who is opening the report, and using different SQL
strings in code to provide the different Record Sources.

I have put the If...Then statement in the OnOpen Event of the subreport.
When I open the subreoprt all by itself, everything works. When I open the
Main Report with the subreport in it, it gives me an error and the Debug
command takes me back to my If...Then statement.

I am thinking my problem is which Event (OnOpen) I am using to decide the
Record Source. If not, I will start looking at my SQL strings and my verbage
in the If...Then statement.

If Forms![frmDate_Range]![ckAll].Value = True Then
Reports!Training_Sniffs_Obedience.RecordSource = sqlControl_All
Else
Reports!Training_Sniffs_Obedience.RecordSource = sqlControl_1
End If

sqlControl_All and sqlControl_1 are both declared as Strings at the
beginning and the SQL strings are after the declaration and before the
If...Then statement.

Any Help would be appreciated.
Balfour211