Setting sub-report source object to a report in a collection

[rkmaitra]

Hello,
I am trying to join a number of reports into one report. I have a generic report which displays a different dataset given the user's choice on a form. I created a collection where I can store multiple instances of this report (called mcolReportInstances) - this works just fine. I was looking to combine all the reports in the collection into one report. To that end, I have created a report with a number of subreport controls but with no sourceobject. In the On_Open event of this blank report, I am trying to set the source object of the subreport to one of reports in my collection:

Me.Controls("Child" & i).SourceObject = mcolReportInstances.Item(strKey)

However, it keeps giving me the error 'Type mismatch'. I am at my wits' end but have not figured out how to resolve this.
Any help would be appreciated.

Many thanks,
Rudra

 

[ChrisO]

The SourceObject property is not actually an Object, nor a pointer to an Object, but the Name of an Object.

It depends on what you have in the Collection as indexed by strKey. It may be that strKey is what you want but that depends on what you have used as the Key.

Chris.

 

[JHB]

Me.Controls("Child" & i).SourceObject = mcolReportInstances.Item(strKey)

However, it keeps giving me the error 'Type mismatch'. I am at my wits' end but have not figured out how to resolve this.
Any help would be appreciated.

Many thanks,
Rudra

As I see your code I suspect that "i" is a number value.
So you try to combine a text and a number, you can't do that, so you must convert "i" to a string value.

Me.Controls("Child" & CStr(i)).SourceObject = mcolReportInstances.Item(strKey)

 

[ChrisO]

JHB.

While it might be better to type cast i to a string in fact we can do it the way it is written.

Chris.

 

[rkmaitra]

Thank you both for your replies. Unfortunately, Cstr(i) does not seem to do the trick. I also tried the following:

Me.Controls("Child" & CStr(i)).SourceObject = Reports(mcolReportInstances.Item(strKey))

but it gives me the same type mismatch error.
In answer to your questions ChrisO, strKey is a string that I construct for each of the reports in the collection to identify them. The parent report is 'Report1' and for each new instance of the report, strKey is a unique combination to identify it from the rest of 'Report1's' instances.
Any other ideas?
Rudra

 

[ChrisO]

Can you show the code you are using to load the mcolReportInstances collection?

Edit:
What do you get with:-
MsgBox mcolReportInstances.Item(strKey)

Chris.

 

[rkmaitra]

Dear Chris,
With MsgBox mcolReportInstances.Item(strKey) I get the error Item mismatch.
The code to create the collection is as follows:


Option Compare Database
Option Explicit

Public mcolFormInstances As New Collection
Public mcolReportInstances As New Collection

Function OpenFormInstance(FormName As String, Optional WhereCondition As String, Optional strKey As String)
'Declare the form name
Dim frm As Form
Dim rpt1 As Report
Dim rpt2 As Report
Dim x As String
Dim rs As Recordset

'Set frm = New Form_Bank

Select Case FormName
Case "frmBank"
Set frm = New Form_frmBank
GoTo Form
Case "frmBank_Subdivisions"
Set frm = New Form_frmBank_Subdivisions
GoTo Form
Case "Report1"
x = ReportRecordSource(gstrReportOpenArgs)
Set rpt2 = New Report_Report1
rpt2.RecordSource = x
GoTo Report
Case Else
Debug.Assert False
End Select

Form:
If WhereCondition <> "" Then
frm.Filter = WhereCondition
frm.FilterOn = True
End If
'make the form visible
frm.Visible = True
' Need to add a reference to the form so that it doesn’t
' immediately close when the form variable goes out of scope.
mcolFormInstances.Add frm
GoTo Continue

Report:
' rpt2.Report.Visible = True
mcolReportInstances.Add rpt2, strKey

If gblnOpenedByAnother = False Then
mcolReportInstances.Item(strKey).Visible = True
End If

Continue:

End Function


Of note, before I try and add the collection report to the subreport control (the one that is giving me the error), if I run the command:

mcolReportInstances.Item(strKey).visible = true

it displays the correct report correctly. Therefore, the report exists in the collection, but I cannot seem to set the subreport control to display this.

Thanks,
Rudra

 

[ChrisO]

The type mismatch is because mcolReportInstances.Item(strKey) contains a 4 byte Pointer to the instance of the Report, not a String.

Try again with:-

MsgBox mcolReportInstances.Item(strKey).Name

and

Me.Controls("Child" & i).SourceObject = mcolReportInstances.Item(strKey).Name

By the way, the SourceObject property in the Controls will all have the same Name.

Chris.

 

[rkmaitra]

Hello Chris,
Thanks for the reply. There is no type mismatch any more but all the subreports display the same data whereas they should display different results depending on strKey. Is there any way for the subreports to display different instances of the same report?
If this is not possible, is there any way to combine the different instances of the same report into one report?
Thanks,
Rudra

 

[ChrisO]

Maybe someone else can but I simply do not know how to do that.

Chris.

 

[rkmaitra]

Dear Chris,
Thanks very much for your help.
Rudra