A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Conditional Subreport Record Source



 
 
Thread Tools Display Modes
  #1  
Old December 21st, 2009, 08:01 PM posted to microsoft.public.access.reports
Balfour211
external usenet poster
 
Posts: 25
Default 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  
Old December 21st, 2009, 08:11 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default 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  
Old December 21st, 2009, 09:56 PM posted to microsoft.public.access.reports
Balfour211
external usenet poster
 
Posts: 25
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:17 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.