Setting Subreport SourceObject Property

[CSDunn]

Hello,
I currently have an Access 2003 ADP Report/Subreport set up in which I have
12 subreports in a single main report that are located in a group header
called 'PermnumHeader' (Permnum would be the same as a numeric 'StudentID').
All subreports initially have their visible property set to 'No'.

I have a macro that evaulates the current value of a field in the detail
section of the main report called 'Grade' and a field in the same section
called 'Spanish', then sets the visible property of a single subreport to
'Yes' given a combination the Grade/Spanish values, and sets the visible
property in the other subreports to 'No'.

'Spanish' holds a value of zero or 1. If Spanish is 1, then a Spanish
report is generated, and if Spanish is zero, and English version is
generated.

The PermnumHeader_Format event of the PermnumHeader group header in the main
report fires the macro.

The report is designed to allow a teacher to preview a group of report cards
all at once, and have different report cards at different grade levels shown
in one of two languages.

I have attempted to change the way this report is generated, by coding a
procedure to run in the PermnumHeader_Format event. The code is as follows:

Private Sub PermnumHeader_Format(Cancel As Integer, FormatCount As Integer)

If Me.GRADE = "00" And Me.Spanish = False Then
Child50.SourceObject = "rptRCK"
ElseIf Me.GRADE = "00" And Me.Spanish = True Then
Child50.SourceObject = "rptRCKsp"

ElseIf Me.GRADE = "01" And Me.Spanish = False Then
Child50.SourceObject = "rptRC1"
ElseIf Me.GRADE = "01" And Me.Spanish = True Then
Child50.SourceObject = "rptRC1sp"

ElseIf Me.GRADE = "02" And Me.Spanish = False Then
Child50.SourceObject = "rptRC2"
ElseIf Me.GRADE = "02" And Me.Spanish = True Then
Child50.SourceObject = "rptRC2sp"

ElseIf Me.GRADE = "03" And Me.Spanish = False Then
Child50.SourceObject = "rptRC3"
ElseIf Me.GRADE = "03" And Me.Spanish = True Then
Child50.SourceObject = "rptRC3sp"

ElseIf Me.GRADE = "04" And Me.Spanish = False Then
Child50.SourceObject = "rptRC4-5"
ElseIf Me.GRADE = "04" And Me.Spanish = True Then
Child50.SourceObject = "rptRCK4-5sp"

ElseIf Me.GRADE = "05" And Me.Spanish = False Then
Child50.SourceObject = "rptRC4-5"
ElseIf Me.GRADE = "05" And Me.Spanish = True Then
Child50.SourceObject = "rptRC4-5sp"
Me.PermnumHeader.OnFormat

End If
End Sub

'Child50' is an 'empty' sub report that only has its link child and master
fields set to 'Permnum'.

The other thing that this code needs to do is set the record source for each
sub report. Normally, where I would just treat the report as a main report I
would handle that in the On Open event of the report as follows:

Private Sub Report_Open (Cancel As Integer, FormatCount As Integer)
Me.RecordSource = "EXEC dbo.RCSingleRCRpt_sp " & Forms!FrmRCMain!Permnum
End Sub

The SQL Server 2000 Stored Procedure, 'RCSingleRCRpt_sp' takes a 'Permnum'
(StudentID)parameter. The On Open event of the report would probably not
work here since as sub reports, they are not 'opening'. Would I use the On
Current event to set the record source?

Going back to my On Format problem, when I attempt to open the report in
print preview, the code on the On Format event runs for PermnumHeader, and I
get the following message:

"Runtime Error '2191':
You can't set the Source Object property in print preview or after printing
has started."

The report never displays in print preview.

How can I set up my reports/subreports so that On Format will work, and so
that the record sources for each sub report will be set correctly?

Thank you for your help!

CSDunn

[david epsom dot com dot au]

If you need to change the source object again for every page, you
will have to re-think your design: you can't do that. You can
probably set the source objects in the open event of the report,
or in the first format event of something. Also, the Format Event
has a FormatCount parameter: only set things when FormatCount=1.

If you need to repeatedly reset the recordsource for some object,
another way to do it is to bind to a table, and reload the table
for each page.
"CSDunn" <cd***@valverde.edu> wrote in message
news:80**************************@posting.google.c om...

Hello,
I currently have an Access 2003 ADP Report/Subreport set up in which I have 12 subreports in a single main report that are located in a group header
called 'PermnumHeader' (Permnum would be the same as a numeric 'StudentID'). All subreports initially have their visible property set to 'No'.

I have a macro that evaulates the current value of a field in the detail
section of the main report called 'Grade' and a field in the same section
called 'Spanish', then sets the visible property of a single subreport to
'Yes' given a combination the Grade/Spanish values, and sets the visible
property in the other subreports to 'No'.

'Spanish' holds a value of zero or 1. If Spanish is 1, then a Spanish
report is generated, and if Spanish is zero, and English version is
generated.

The PermnumHeader_Format event of the PermnumHeader group header in the main report fires the macro.

The report is designed to allow a teacher to preview a group of report cards all at once, and have different report cards at different grade levels shown in one of two languages.

I have attempted to change the way this report is generated, by coding a
procedure to run in the PermnumHeader_Format event. The code is as follows:
Private Sub PermnumHeader_Format(Cancel As Integer, FormatCount As Integer)
If Me.GRADE = "00" And Me.Spanish = False Then
Child50.SourceObject = "rptRCK"
ElseIf Me.GRADE = "00" And Me.Spanish = True Then
Child50.SourceObject = "rptRCKsp"

ElseIf Me.GRADE = "01" And Me.Spanish = False Then
Child50.SourceObject = "rptRC1"
ElseIf Me.GRADE = "01" And Me.Spanish = True Then
Child50.SourceObject = "rptRC1sp"

ElseIf Me.GRADE = "02" And Me.Spanish = False Then
Child50.SourceObject = "rptRC2"
ElseIf Me.GRADE = "02" And Me.Spanish = True Then
Child50.SourceObject = "rptRC2sp"

ElseIf Me.GRADE = "03" And Me.Spanish = False Then
Child50.SourceObject = "rptRC3"
ElseIf Me.GRADE = "03" And Me.Spanish = True Then
Child50.SourceObject = "rptRC3sp"

ElseIf Me.GRADE = "04" And Me.Spanish = False Then
Child50.SourceObject = "rptRC4-5"
ElseIf Me.GRADE = "04" And Me.Spanish = True Then
Child50.SourceObject = "rptRCK4-5sp"

ElseIf Me.GRADE = "05" And Me.Spanish = False Then
Child50.SourceObject = "rptRC4-5"
ElseIf Me.GRADE = "05" And Me.Spanish = True Then
Child50.SourceObject = "rptRC4-5sp"
Me.PermnumHeader.OnFormat

End If
End Sub

'Child50' is an 'empty' sub report that only has its link child and master
fields set to 'Permnum'.

The other thing that this code needs to do is set the record source for each sub report. Normally, where I would just treat the report as a main report I would handle that in the On Open event of the report as follows:

Private Sub Report_Open (Cancel As Integer, FormatCount As Integer)
Me.RecordSource = "EXEC dbo.RCSingleRCRpt_sp " & Forms!FrmRCMain!Permnum
End Sub

The SQL Server 2000 Stored Procedure, 'RCSingleRCRpt_sp' takes a 'Permnum'
(StudentID)parameter. The On Open event of the report would probably not
work here since as sub reports, they are not 'opening'. Would I use the On
Current event to set the record source?

Going back to my On Format problem, when I attempt to open the report in
print preview, the code on the On Format event runs for PermnumHeader, and I get the following message:

"Runtime Error '2191':
You can't set the Source Object property in print preview or after printing has started."

The report never displays in print preview.

How can I set up my reports/subreports so that On Format will work, and so
that the record sources for each sub report will be set correctly?

Thank you for your help!

CSDunn

[CSDunn]

David,
Thanks for your help. The record source can be made the same for each
report, I would just need to reconfigure how the reports are used when
they are viewed individually.

Maybe I'll have to go back to having the reports 'stacked' again as
sub reports in a main report.

CSDunn

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message news:<40***********************@news.syd.swiftdsl. com.au>...

If you need to change the source object again for every page, you
will have to re-think your design: you can't do that. You can
probably set the source objects in the open event of the report,
or in the first format event of something. Also, the Format Event
has a FormatCount parameter: only set things when FormatCount=1.

If you need to repeatedly reset the recordsource for some object,
another way to do it is to bind to a table, and reload the table
for each page.
"CSDunn" <cd***@valverde.edu> wrote in message
news:80**************************@posting.google.c om...

Hello,
I currently have an Access 2003 ADP Report/Subreport set up in which I

have

12 subreports in a single main report that are located in a group header
called 'PermnumHeader' (Permnum would be the same as a numeric

'StudentID').

All subreports initially have their visible property set to 'No'.

I have a macro that evaulates the current value of a field in the detail
section of the main report called 'Grade' and a field in the same section
called 'Spanish', then sets the visible property of a single subreport to
'Yes' given a combination the Grade/Spanish values, and sets the visible
property in the other subreports to 'No'.

'Spanish' holds a value of zero or 1. If Spanish is 1, then a Spanish
report is generated, and if Spanish is zero, and English version is
generated.

The PermnumHeader_Format event of the PermnumHeader group header in the

main

report fires the macro.

The report is designed to allow a teacher to preview a group of report

cards

all at once, and have different report cards at different grade levels

shown

in one of two languages.

I have attempted to change the way this report is generated, by coding a
procedure to run in the PermnumHeader_Format event. The code is as

follows:

Private Sub PermnumHeader_Format(Cancel As Integer, FormatCount As

Integer)

If Me.GRADE = "00" And Me.Spanish = False Then
Child50.SourceObject = "rptRCK"
ElseIf Me.GRADE = "00" And Me.Spanish = True Then
Child50.SourceObject = "rptRCKsp"

ElseIf Me.GRADE = "01" And Me.Spanish = False Then
Child50.SourceObject = "rptRC1"
ElseIf Me.GRADE = "01" And Me.Spanish = True Then
Child50.SourceObject = "rptRC1sp"

ElseIf Me.GRADE = "02" And Me.Spanish = False Then
Child50.SourceObject = "rptRC2"
ElseIf Me.GRADE = "02" And Me.Spanish = True Then
Child50.SourceObject = "rptRC2sp"

ElseIf Me.GRADE = "03" And Me.Spanish = False Then
Child50.SourceObject = "rptRC3"
ElseIf Me.GRADE = "03" And Me.Spanish = True Then
Child50.SourceObject = "rptRC3sp"

ElseIf Me.GRADE = "04" And Me.Spanish = False Then
Child50.SourceObject = "rptRC4-5"
ElseIf Me.GRADE = "04" And Me.Spanish = True Then
Child50.SourceObject = "rptRCK4-5sp"

ElseIf Me.GRADE = "05" And Me.Spanish = False Then
Child50.SourceObject = "rptRC4-5"
ElseIf Me.GRADE = "05" And Me.Spanish = True Then
Child50.SourceObject = "rptRC4-5sp"
Me.PermnumHeader.OnFormat

End If
End Sub

'Child50' is an 'empty' sub report that only has its link child and master
fields set to 'Permnum'.

The other thing that this code needs to do is set the record source for

each

sub report. Normally, where I would just treat the report as a main report

I

would handle that in the On Open event of the report as follows:

Private Sub Report_Open (Cancel As Integer, FormatCount As Integer)
Me.RecordSource = "EXEC dbo.RCSingleRCRpt_sp " & Forms!FrmRCMain!Permnum
End Sub

The SQL Server 2000 Stored Procedure, 'RCSingleRCRpt_sp' takes a 'Permnum'
(StudentID)parameter. The On Open event of the report would probably not
work here since as sub reports, they are not 'opening'. Would I use the On
Current event to set the record source?

Going back to my On Format problem, when I attempt to open the report in
print preview, the code on the On Format event runs for PermnumHeader, and

I

get the following message:

"Runtime Error '2191':
You can't set the Source Object property in print preview or after

printing

has started."

The report never displays in print preview.

How can I set up my reports/subreports so that On Format will work, and so
that the record sources for each sub report will be set correctly?

Thank you for your help!

CSDunn

[david epsom dot com dot au]

Even just setting a sub report to not visible may force it to not
calculate at all - there may not be any real overhead to using
stacked sub-reports. You may also try to use the "link child fields/
link master fields" properties - it may speed up your report because
the sub-reports won't calculate if they are empty. Another option
might be to dynamically reconfigure a single sub-report: you can
shift controls and make them visible/invisible in the format event
for a section.

"CSDunn" <cd***@valverde.edu> wrote in message
news:80**************************@posting.google.c om...

David,
Thanks for your help. The record source can be made the same for each
report, I would just need to reconfigure how the reports are used when
they are viewed individually.

Maybe I'll have to go back to having the reports 'stacked' again as
sub reports in a main report.

CSDunn

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message

news:<40***********************@news.syd.swiftdsl. com.au>...

If you need to change the source object again for every page, you
will have to re-think your design: you can't do that. You can
probably set the source objects in the open event of the report,
or in the first format event of something. Also, the Format Event
has a FormatCount parameter: only set things when FormatCount=1.

If you need to repeatedly reset the recordsource for some object,
another way to do it is to bind to a table, and reload the table
for each page.
"CSDunn" <cd***@valverde.edu> wrote in message
news:80**************************@posting.google.c om...

Hello,
I currently have an Access 2003 ADP Report/Subreport set up in which I

have

12 subreports in a single main report that are located in a group header called 'PermnumHeader' (Permnum would be the same as a numeric

'StudentID').

All subreports initially have their visible property set to 'No'.

I have a macro that evaulates the current value of a field in the detail section of the main report called 'Grade' and a field in the same section called 'Spanish', then sets the visible property of a single subreport to 'Yes' given a combination the Grade/Spanish values, and sets the visible property in the other subreports to 'No'.

'Spanish' holds a value of zero or 1. If Spanish is 1, then a Spanish
report is generated, and if Spanish is zero, and English version is
generated.

The PermnumHeader_Format event of the PermnumHeader group header in the

main

report fires the macro.

The report is designed to allow a teacher to preview a group of report

cards

all at once, and have different report cards at different grade levels

shown

in one of two languages.

I have attempted to change the way this report is generated, by coding

a procedure to run in the PermnumHeader_Format event. The code is as

follows:

Private Sub PermnumHeader_Format(Cancel As Integer, FormatCount As

Integer)

If Me.GRADE = "00" And Me.Spanish = False Then
Child50.SourceObject = "rptRCK"
ElseIf Me.GRADE = "00" And Me.Spanish = True Then
Child50.SourceObject = "rptRCKsp"

ElseIf Me.GRADE = "01" And Me.Spanish = False Then
Child50.SourceObject = "rptRC1"
ElseIf Me.GRADE = "01" And Me.Spanish = True Then
Child50.SourceObject = "rptRC1sp"

ElseIf Me.GRADE = "02" And Me.Spanish = False Then
Child50.SourceObject = "rptRC2"
ElseIf Me.GRADE = "02" And Me.Spanish = True Then
Child50.SourceObject = "rptRC2sp"

ElseIf Me.GRADE = "03" And Me.Spanish = False Then
Child50.SourceObject = "rptRC3"
ElseIf Me.GRADE = "03" And Me.Spanish = True Then
Child50.SourceObject = "rptRC3sp"

ElseIf Me.GRADE = "04" And Me.Spanish = False Then
Child50.SourceObject = "rptRC4-5"
ElseIf Me.GRADE = "04" And Me.Spanish = True Then
Child50.SourceObject = "rptRCK4-5sp"

ElseIf Me.GRADE = "05" And Me.Spanish = False Then
Child50.SourceObject = "rptRC4-5"
ElseIf Me.GRADE = "05" And Me.Spanish = True Then
Child50.SourceObject = "rptRC4-5sp"
Me.PermnumHeader.OnFormat

End If
End Sub

'Child50' is an 'empty' sub report that only has its link child and master fields set to 'Permnum'.

The other thing that this code needs to do is set the record source for each

sub report. Normally, where I would just treat the report as a main

report I

would handle that in the On Open event of the report as follows:

Private Sub Report_Open (Cancel As Integer, FormatCount As Integer)
Me.RecordSource = "EXEC dbo.RCSingleRCRpt_sp " &

Forms!FrmRCMain!Permnum End Sub

The SQL Server 2000 Stored Procedure, 'RCSingleRCRpt_sp' takes a 'Permnum' (StudentID)parameter. The On Open event of the report would probably not work here since as sub reports, they are not 'opening'. Would I use the On Current event to set the record source?

Going back to my On Format problem, when I attempt to open the report in print preview, the code on the On Format event runs for PermnumHeader, and I

get the following message:

"Runtime Error '2191':
You can't set the Source Object property in print preview or after

printing

has started."

The report never displays in print preview.

How can I set up my reports/subreports so that On Format will work,

and so that the record sources for each sub report will be set correctly?

Thank you for your help!

CSDunn