Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Access Report Using Parameter Query


(!)

Wayne0928's Avatar
Wayne0928 Wayne0928 is offline
Member with 36 posts.
THREAD STARTER
 
Join Date: Nov 2010
02-Apr-2012, 09:27 PM #1
Access Report Using Parameter Query
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!
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
03-Apr-2012, 07:37 AM #2
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.
Wayne0928's Avatar
Wayne0928 Wayne0928 is offline
Member with 36 posts.
THREAD STARTER
 
Join Date: Nov 2010
03-Apr-2012, 10:37 AM #3
Correct Record Source
Thanks. I created it in Design View. The Record Source is just the name of the query with nothing else. Any other ideas?
Rockn's Avatar
Computer Specs
Member with 21,189 posts.
 
Join Date: Jul 2001
Location: Somalia of the North, MN
Experience: Disenfranchised American
03-Apr-2012, 10:49 AM #4
You need to define the parameter otherwise the report has no idea what data type the parameter is.
Wayne0928's Avatar
Wayne0928 Wayne0928 is offline
Member with 36 posts.
THREAD STARTER
 
Join Date: Nov 2010
07-Apr-2012, 01:40 AM #5
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.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
07-Apr-2012, 09:27 AM #6
Can you put the tables, query and report in an Access 2003 database?
Wayne0928's Avatar
Wayne0928 Wayne0928 is offline
Member with 36 posts.
THREAD STARTER
 
Join Date: Nov 2010
07-Apr-2012, 01:28 PM #7
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
File Type: zip TestConvention.zip (387.5 KB, 54 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
07-Apr-2012, 01:43 PM #8
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.
__________________
OBP
I do not give up easily
Wayne0928's Avatar
Wayne0928 Wayne0928 is offline
Member with 36 posts.
THREAD STARTER
 
Join Date: Nov 2010
07-Apr-2012, 02:17 PM #9
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?
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
07-Apr-2012, 02:30 PM #10
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.
Wayne0928's Avatar
Wayne0928 Wayne0928 is offline
Member with 36 posts.
THREAD STARTER
 
Join Date: Nov 2010
07-Apr-2012, 05:36 PM #11
Ah! Thanks for your help. I've got some homework to do, but I "get the picture." Thanks again!
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


Tags
access, dialog box, find, parameter query

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑