Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2007
    Posts
    14

    Unanswered: Need one report based on any one of many queries

    I'm using Access 2007. I have a large report that I need to let the user select which test subject to base the report on. Then based on that test subject, I need the report to pull data from the specific query based on that subject. I need to know how to code the fields in the report to accept the data based on the subject requested. I know how to create an if statement that changes a field in the report to display certain data. I have six different subject choices and each subject choice as 25 different fields associated with that one subject. I have queried each subject. Then I thought I could combine all the subject queries into one query and create a pop-up box that ask for which subject. My question is how to do I place the query name in the field source on the report?

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    From inside the report: Me.RecordSource = "QueryName"

    From outside the report: Reports("ReportName").RecordSource = "QueryName"
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Sep 2007
    Posts
    14
    Quote Originally Posted by StarTrekker
    From inside the report: Me.RecordSource = "QueryName"

    From outside the report: Reports("ReportName").RecordSource = "QueryName"
    When you say "from inside the report", exactly where inside the report as well as where exactly "from outside the report"?

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    where inside the report
    Anywhere in the module for the report.

    where exactly "from outside the report"?
    In any code module.

    Typically, you have that kind of code in the On Open event procedure for the report (inside the report) or in the On Click event procedure for the "proceed" button on the reporting dialog box (outside the report).
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Sep 2007
    Posts
    14
    How would I identify the source in the individual field on the report? Right now I have the field names for the Science Test fields in the report. Each Subject has its own field names - 25 fields each. Example ScienceAccom1, ScienceAcomm2... for each subject Math, Social Studies etc... What field identity do I put in the layout of the report itself?

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    How would I identify the source in the individual field on the report?
    Ah... You can specify a Control Source with syntax such as QueryName.FieldName.

    You can also do this with code, so you could re-define each control's Control Source with code such as:

    Me.ControlName.ControlSource = "QueryName.Fieldname"
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •