1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access Report Using Parameter Query

Discussion in 'Business Applications' started by Wayne0928, Apr 2, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. Wayne0928

    Wayne0928 Thread Starter

    Joined:
    Nov 11, 2010
    Messages:
    36
    I'm using MS Access 2007. I have a report that pulls data from a query where I have a parameter set so that it selects specific data depending on what I put in the dialog box. When I just pull up the query, everything works fine; I click on the query, a dialog box comes up, I put in the specific parameter value, and voila!...the requested records appear. The report that I built using the same query almost works as well, except I have to enter the parameter value 3-4 times before the requested report comes up. I'm flummoxed! Any help out there? Thanks!
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Did you use the Wizard to create the Report?
    If yes, you need to check the Record Source of the report, if it starts with the word "Select" change it to your Query.
     
  3. Wayne0928

    Wayne0928 Thread Starter

    Joined:
    Nov 11, 2010
    Messages:
    36
    Thanks. I created it in Design View. The Record Source is just the name of the query with nothing else. Any other ideas?
     
  4. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,189
    You need to define the parameter otherwise the report has no idea what data type the parameter is.
     
  5. Wayne0928

    Wayne0928 Thread Starter

    Joined:
    Nov 11, 2010
    Messages:
    36
    Thanks, Rockn. Even if the parameter is set in the query, I need to set it in the report, too? Why does it work right now on the 3rd (sometimes 4th) attempt? I'm not doubting you...just trying to understand. Thanks.
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Can you put the tables, query and report in an Access 2003 database?
     
  7. Wayne0928

    Wayne0928 Thread Starter

    Joined:
    Nov 11, 2010
    Messages:
    36
    Yes, I've attached a copy of my file in 2003 format and tested it to make sure the specific problem I'm having still exists. To protect the private data it contains, I've randomized personal details beyond any real recognition.

    The "offending" objects are 02-QryConfirmationForm-Individual and A-RptConfirmationForm-Individual. Pull up the Query, enter the SchoolName 1 time, and you get what you asked for. (Use "Parkville High School," for example) Pull up the Report, enter the SchoolName, and it asks you again, then again, then the requested report comes up. I'm trying to solve the problem of having to enter the SchoolName 3 times.

    By the way, is there a war for the Parameter dialog to come up as an auto-complete field? Just askin'...

    I appreciate you taking the time to look at this!

    Disclaimer: I'm self-taught, so this is certainly not a model of efficient database design, but it works for what I do. I'm just trying to get it to work better.
     

    Attached Files:

  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    In Access 2003 the parameter only comes up once when running the report.
    I have run it 4 times with the same result each time.
    It would appear to be an Access 2007 problem.

    With regard to your question, I would suggest using a form with a "Schools" Combo box and use that as the parameter in the query.
     
  9. Wayne0928

    Wayne0928 Thread Starter

    Joined:
    Nov 11, 2010
    Messages:
    36
    Thanks, OBP! Wow, that's interesting. At least knowing that it's unique to 2007 is a step in solving the mystery.

    If I understand your suggestion for the other question I added, you're saying to re-create my report as a form and use a combo box to "find" the particular school I need. I could do that (I have that in a couple of the other forms already), but the reason I set it up as a report, is so that I can email a specific report to a particular school upon request. If it's a form, then I run into that problem where individual forms can't be printed (or emailed) without all of them being included. Am I understanding this correctly?
     
  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    No not necessarily replace the report, but add a form, so that you can select a school without a lot of typing.
    But you can also open a report with the same record as a form as well, although the vba does not always work in Access 2007.
     
  11. Wayne0928

    Wayne0928 Thread Starter

    Joined:
    Nov 11, 2010
    Messages:
    36
    Ah! Thanks for your help. I've got some homework to do, but I "get the picture." Thanks again!
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1047706