Set Sourceobject of Subreport programmatically

[grgimpy]

I'm trying to set the Source Object of a Subreport programmatically.

I've created the subreport named "fsubIndividualChart" and for some reason

Me.fsubIndividualChart.SourceObject = strIndChrtSource

does not work (strIndChrtSource is a string expression).

I get the error 3011: MS Jet database engine can't find the object.

What is going on here? When I type the "Me." I am given the option of choosing the "fsubIndividualChart" as my next piece of code, but then "SourceObject" is not an object after that.

I've changed subform source objects many times before. Is there something I need to do differently for subreports?

Thanks

 

[dhookom]

You could try move the code to the section of the report containing the subreport. I'm not sure this would work. If you have a couple subreport objects that you want to switch between, you might have better luck including them both and making one visible at a time.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[grgimpy]

What I am trying to do is use a Report with two unbound subreports, and then send the source object string value from another form to this Report. This way I will be able to use this one Report to display any kind of different Pivotcharts I want in these two unbound subreports. This is much better than creating a hundred reports based on each set of charts.

Here's the code I'm trying to make work:

The report code (I excluded the code that doesn't work. I'm trying to insert the code in my previous post into this event of the Report):

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Report_Open_Err

    Me.lblUCLValue.Caption = strSPCIndUCL
    Me.lblTargetValue.Caption = strSPCIndTarget
    Me.lblLCLValue.Caption = strSPCIndLCL
    Me.lblMRUCLValue.Caption = strSPCRangeUCL
    Me.lblTitle.Caption = strReportTitle
    
    
Report_Open_Exit:
    Exit Sub
Report_Open_Err:
    MsgBox Error$
    Resume Report_Open_Exit
End Sub

The form code:

Call SPCChartsReport(Me.lblUCLValue.Caption, _
     Me.lblTargetValue.Caption, _
     Me.lblLCLValue.Caption, _
     Me.lblMRUCLValue.Caption, _
     "ESA #1: Etchant pH Charts", _
     "ESA1-Chart Etch pH", _
     "ESA1-Chart Etch pH MR")

The Function code I'm calling from the form:

Global strSPCIndUCL As String, _
    strSPCIndTarget As String, _
    strSPCIndLCL As String, _
    strSPCRangeUCL As String, _
    strReportTitle As String, _
    strIndChrtSource As String, _
    strRngChrtSource As String

Public Function SPCChartsReport _
    (Individual_UCL As String, _
    Individual_Target As String, _
    Individual_LCL As String, _
    Range_UCL As String, _
    Report_Title As String, _
    Individual_Chart_SourceObject As String, _
    Range_Chart_SourceObject As String)
    
On Error GoTo Err_Handler
    strSPCIndUCL = Individual_UCL
    strSPCIndTarget = Individual_Target
    strSPCIndLCL = Individual_LCL
    strSPCRangeUCL = Range_UCL
    strReportTitle = Report_Title
    strIndChrtSource = Individual_Chart_SourceObject
    strRngChrtSource = Range_Chart_SourceObject
    

'Open Report in Preview Mode
    DoCmd.OpenReport "SPC Charts Report", acViewPreview
    
Exit_Handler:
    Exit Function
Err_Handler:
    Resume Exit_Handler
End Function

 

[dhookom]

Your code is still in the On Open event of the report. Did you even try this suggestion: "You could try move the code to the section of the report containing the subreport"



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[grgimpy]

When say move it to the section of the report containing the subreport, do you mean put the code in the load event of the subreport?

If that is the case, then I cannot b/c the subreport object is unbound.

Maybe I'm not understanding exactly what you are talking about.

 

[dhookom]

Your "fsubIndividualChart" is located in a specific section of your report (possibly the detail section). You should move the code from the On Open event of the report to the On Format event of the section of the report containing fsubIndividualChart.

Again, I'm not sure if this will work.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[grgimpy]

unfortunately, that did not work.

This problem just doesn't make sense to me. I created a subreport, I can change the Source Object in Design mode in the subreport properties. Why is the subreport not recognized in VBA?

 

[dhookom]

What makes you so certain the subreport is not recognized? Try add code to the On Format event of the section containing the object like:
MsgBox "Source Object: " & Me.fsubIndividualChart.SourceObject
This might be an issue of either the name of the object or possibly the inability to change the source object property once your report has started printing.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[grgimpy]

Alright, first of all, thanks dhookom for trying to help me our here. Of course the solution to this problem was extremely simple.

The formatting of a subreports sourceobject is a little different than a subform. In a subreport, the Source Object must be in the format "Form.[Form Name]" or "Query.[Query Name]".

So to fix the problem, I took the source object of my subform and just attached the "Form." string to the front of it and it works perfectly now.

I should have played around a little bit before jumping to conclusions based on horrible Access error messages, but it's not the first time I've made this mistake.

Anyways, thanks again to dhookom for his attempt at helping me through my ignorance of Access Reports!