How do I pass a value from a Main report to its subreport?

Posted on 2014-08-25
Last Modified: 2014-09-08
I have a Main report which generates 20 different reports. Within Private Sub Report_OnOpen, a BuildTempTable sub is called with the type of report passed as an input parameter. (See code below). The record source for the Main report is the Temp table. The Main Report is grouped by ReportType. Within the footer section of ReportType, I need a subreport. This subreport's recordsource is set based on the ReportType being processed.

My question: How do I pass the ReportType to the subreport. I have set the Parent/Child connection between the Main/sub report to be ReportType. The subreport has processing within its Report_OnOpen event, dependent on the ReportType.

private Sub BuildTempTableForMainReport(Type as String)
if Type = "A" then
   db.execute "INSERT INTO TempTable (ReportType,......) VALUES ('" & Type & ......
elseif Type = "B" then
  db.execute "INSERT INTO Temp....
elseif .....

End if

Private Sub MainReport_OnOpen()

  call BuildTempTableForMainReport("A")
  call BuildTempTableForMainReport("B")

End sub

'''''''''''' Within subreport
private Sub SubReport_OnOpen()
if ReportType="A" then
  me.recordsource = .....
elseif ReportType = "B" then
  me.recordsource = ......
end if
Question by:newbie46
    1 Comment
    LVL 61

    Accepted Solution

    The trouble you are running into likely has to do with Access's order of events.

    Subreports (and subforms) load *before* their parent objects.  See this for details:

    How are you informing your main report of the ReportType?

    You will need to make that information available to your subreport *before* the main report loads.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now