0

I'm trying to find a way to pass a parameter from a report to a subreport without resorting to any SQL code or macros (my officemates are non-technical, but still have to use the Access database to run reports, occasionally making tweaks to them.)

I'm working in Microsoft Access 2013. I have a table that contains a list of investments as well as which state those investments are based in. I have a query that pulls data on Investments based on a user-entered State parameter. I then run two reports: one that simply lists the investments grouped on different categories, then a second report that summarizes the investment categories into a table. I've put the summary report at the top of the detailed report as a subreport, but I want to pass the State parameter through from the main report to the subreport so the user doesn't have to enter it twice. Is that possible without resorting to writing any SQL code or macros?

Thanks!

Chris Nemarich
  • 78
  • 6

1 Answers1

1

I think I understand what you're trying to do, but please add details if my answer doesn't make sense.

You can pass user entered information by referencing by [DatabaseObjectType]![ObjectName]![FieldName].

If the user is entering the State value from a Form, you'd reference: [Forms]![FormName]![State] where FormName is the name of your form, and State is actually the name of the Form control containing the State value.

If the user is entering the State value in a prompt from a query, you'd reference: [Queries]![Query1]![State] where Query1 is the name of your initial query that gets the state info from the user, and 'State' is the name of that field.

You put these references in to your secondary query or report:

  1. for example, in a second query, you can set the State field Criteria (in query design view) to be = [Queries]![Query1]![State] so the second query will pull the State value from the first query

  2. on a report, similar idea - you can set the Control Source of the State control (in Properties) to be = [Queries]![Query1]![State]

Stidgeon
  • 2,673
  • 8
  • 20
  • 28
  • For some reason, it keeps having me enter [Queries] and [Query1] as parameters, not recognizing them as actual objects. So, I enter in a text box, for instance: =[Queries]![Query1]![State]! and then it will prompt me to enter values for [Queries] and [Query 1]. What gives? – Chris Nemarich Jan 28 '16 at 21:17
  • Nevermind, I figured it out. I made a simple spelling error I wasn't catching. Thanks for your help! – Chris Nemarich Jan 28 '16 at 21:29