If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional Subreport Record Source
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 |
#2
|
|||
|
|||
Conditional Subreport Record Source
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" wrote: 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 |
#3
|
|||
|
|||
Conditional Subreport Record Source
Nicely Done. Works Great.
Thanks for the quick response balfour211 "Duane Hookom" wrote: 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" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|