How do I insert a sub report using vba

[macca the hacke]

Hi

Having brain fade!

How do I insert an existing subreport onto an existing report using vba?

I cannot just have subreports on report and then hide/unhide them, as there are a lot of them and would make the report very slow to open. Depending on user options there could be between 1 - 10 different sub reports to insert.

I guess I set a control to be a subreport?

Thanks

I should know the answer!!

 

[boblarson]

Put a subreport control on your form and then:

Me.YourSubreportControlName.SourceObject = "YourReportName"

 

[macca the hacke]

Thanks Bob - obvious when you think about it!

 

[macca the hacke]

The problem I have is that there will be more than 1 subreport on the report, and I am using a loop function to obtain source:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("tbl_mappingtable_weekly_subreports", dbOpenDynaset)
Set rs1 = db.OpenRecordset("tbl_weekly_mappingtable_limits", dbOpenDynaset)

Dim strLimit, strSubReport, strFilter As String
strFilter = WeeklyFilterCompany()
Dim RSNo As Long
RSNo = DCount("[typeoflimit]", "tbl_weekly_mappingtable_limits", "[portfolio] = '" & strFilter & "'")
Dim iLoop As Integer

'get names of limits from table for portfolio

rs1.FindFirst "Portfolio = '" & strFilter & "'"
For iLoop = 1 To RSNo
strLimit = rs1!typeoflimit
'now get name of sub report to be added because of limit
rs.FindFirst "Limit = '" & strLimit & "'"
strSubReport = rs!reportname
'now add the subreport to the parent report
Me.SR& i.SourceObject = strSubReport

rs1.FindNext "Portfolio = '" & strFilter & "'"
Next iLoop

******

I have named the controls SR1 - SR10.
How do I reference these within the loop, without specifically using SR1, SR2, etc; as that would defeath the object of the loop?

 

[boblarson]

Me.Controls("SR" & i).SourceObject = strSubReport

 

[macca the hacke]

Hi Bob

Thanks

I was trying

Me.("SR" & i).SourceObject = strSubReport

and it didn't like it! Didn't think of the controls reference!

You're a star!

 

[boblarson]

 

[Arpeggione]

Hi: I am reading the above and am trying to do something similar (I think). I have the following code:

Me.Controls("subrptEventFaultsCtnr").SourceObject = "subrptEventFaults", but I still get the error 2191 (can't set a source object after print preview....)

so...what event of the report is this line going into? I've tried Report.Open and Report.Activate...

thanks for any help anyone can give....I appreciate it!

Karen

 

[Arpeggione]

From Karen: Never mind! I actually got this to work in the Open Event.....somehow, I had to redo the open event. It didn't take the first time. I also had to set the LinkChild and LinkMaster fields in code.

thank you to the writers above!

Karen