Conditional Subreport Record Source

  • Thread starter Thread starter Balfour211
  • Start date Start date
B

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
 
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
 
Nicely Done. Works Great.
Thanks for the quick response
balfour211

Duane Hookom said:
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


Balfour211 said:
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
Click to expand...
Click to expand...
 
Back
Top