Unbound Subreport

[S Jackson]

I am trying to use Visual Basic to set the Source Object property for a
subreport in my main report. The subreport is an unbound control named
"rsubReport." Based on the value of a control on the main form named
"Actn," I would like the Source Object for rsubReport to be either
"rsubCaseInfoSheet" or "rsubCaseInfoSheet2." I put the event procedure
under the On Format property of the main report's Detail section. The error
VB gives me with the following code is "Run-time error '2191': You can't
set the Source Object property after printing has started." Where can I
put this event procedure so that it will work?

FYI:

Dim strReport As String

Select Case Me.Actn = Value
Case "CNA", "NFA", "CMA"
strReport = "rsubCaseInfoSheet2"
Case Else
strReport = "rsubCaseInfoSheet"
End Select
Me.rsubReport.SourceObject = "strReport"

 

[strive4peace]

you can put it in the Report Open event -- of course, you
will need to change "me." to "forms!formname."


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[S Jackson]

I realized, belatedly, that I was using the wrong control on the main form
to base which subreport appeared. So, I changed the name of the control in
the code, and moved the code to the Report Open event and now I get this
message:

Run-time error '2427': You entered an expression that has no value

FYI
Select Case Me.Program.Value
Case "CNA", "NFA", "CMA"
strReport = "rsubCaseInfoSheet2"
Case Else
strReport = "rsubCaseInfoSheet"
End Select
Me.rsubReport.SourceObject = "strReport"

 

[strive4peace]

try this:

Select case nz(Me.Program,"")

if you are in the code behind your report, you will need to
use the absolute reference to your control:

Select case nz(forms!reportmenu_formname.Program,"")


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[S Jackson]

I am having all kinds of trouble with this. The report is launched from a
form called frmMaster. Both the report and the form contain the field
called "Program" which is the field I want the code to look to and then give
me the correct subreport within the report. Here is the code I have so far
based on Crystal's response:

Dim strReport As String

Select Case Nz(Forms!frmMaster.Program, "")

Case "CNA", "NFA", "CMA"
strReport = "rsubCaseInfoSheet2"
Case Else
strReport = "rsubCaseInfoSheet"
End Select
Me.rsubReport.SourceObject = "strReport"

Now its giving me an error saying: Run-time error 2121; The setting you
entered isn't valid for this property.

The Debug is highlighting the last line of the code:
"Me.rsubReport.SourceOpbject = "strReport"

I am about ready to give up here! Why doesn't the coding work the same way
for reports as it does for forms??!!

 

[strive4peace]

Me.rsubReport.SourceObject = strReport

remove the quotes -- otherwise Access will look for a report
with that name


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[S Jackson]

Oops! Yes, thanks. That worked.

Okay, I have a slightly different challenge. It is the same report, but
this report includes more than one record. In the prior scenario, I was
using a form to launch a single report. Now, I want the same report to
print for each record within a query and I am using a different form to
print the report that does is only a dialog form.

When I attempt this code:

Dim strReport As String
Select Case Nz(Me.Program, "")
Case "CNA", "NFA", "CMA"
strReport = "rsubCaseInfoSheet2"
Case Else
strReport = "rsubCaseInfoSheet"
End Select
Me.rsubReport.SourceObject = strReport

I get the message that I've "entered an expression that has no value."
Run-time error '-2147352567 (800200009)':
I understand this since the code is behind the Open Event of the Report.
However, when I attempt to place the code in the Format Event of the detail
section, I get the following error:

"You can't set the Source Object property after printing has started."
Run-time error '2191.'

How do I address this situation?
S. Jackson

 

[strive4peace]

you're welcome glad you got it

how about the Format event of the Report Header section?

If it balks at that too, you can modify this code to replace
the subreport SourceObject before the report is opened for
viewing...

'~~~~~~~~~~~~~~~
Sub SetReportFilter( _
ByVal pReportName As String, _
ByVal pFilter As String)

' written by Crystal
' Strive4peace2007 at yahoo dot com

' PARAMETERS:
' pReportName is the name of your report
' pFilter is a valid filter string

' USEAGE:
' SetReportFilter "MyReportname","someID=1000"
' SetReportFilter "MyAppointments", _
"City='Denver' AND dt_appt=#9/18/05#"

On Error Goto Proc_Err

'---------- declare variables
Dim rpt As Report

'---------- open design view of report
' --- and set the report object variable

DoCmd.OpenReport pReportName, acViewDesign
Set rpt = Reports(pReportName)

'---------- set report filter and turn it on
rpt.Filter = pFilter
rpt.FilterOn = IIf(Len(pFilter) > 0, True, False)

'---------- save and close the changed report
DoCmd.Save acReport, pReportName
DoCmd.Close acReport, pReportName

'---------- Release object variable
Set rpt = Nothing

Proc_Exit:
Exit Sub

Proc_Err:
msgbox err.description,, _
"ERROR " & err.number & " SetReportFilter"

'press F8 to step thru code and fix problem
'comment next line after debugged
Stop: Resume
'next line will be the one with the error

resume Proc_Exit:

End Sub
'~~~~~~~~~~~~~~~


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*