By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,830 Members | 2,182 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,830 IT Pros & Developers. It's quick & easy.

Repeated Prompts for Parameters when Running Reports in Access

P: 3
Let me start off by stating that I use Access, but have limited knowledge in SQL... I do have a good understanding of programming (C++) though, so I can follow the logic behind it. But I use dirty but quick methods of doing things in Access that work but probably isn't the best or simiplist methods because I don't know how to do them.

Okay, the problem is that I use a lot of reports in Access that runs off of a query (Query2) that runs off of a query (Query1) that asks for parameters. When I run Query2, it only asks for the parameters from Query1 once. However, when I run the report, I get repeated prompts to enter the parameters-- often 3 or 4 times. Also, it takes a long time to run compared to other reports that use the same data set or ones that don't ask for parameters. The reports work, but it's just annoying and time consuming to keep entering the parameters. It's not just one report, but multiple ones in different databases that do this and use the same method. The reports calculate sums and counts.

Does anyone know what is causing the repeated prompts?
Or what additional information do you need to know?

Thanks so much for your help!
Nov 13 '08 #1
Share this Question
Share on Google+
3 Replies


atsukoarai86
P: 6
I may be able to help you.. I had a similar problem with a few of my queries when I first started out with SQL, but I'd have to see the code you've written to be able to offer any sort of direction. Could you post the code so we could have a look at it?
Nov 13 '08 #2

P: 3
The repeated prompts don't occur when I just run the queries though. It only happens when I run the reports that use the queries.
I don't know how to post the code from the report... but here is the queries that run the report... although like I said, the problem only happens when I run the report and not just the queries.

Here is the query that the report calls....

PARAMETERS [Enter YearTerm] Text ( 255 );
TRANSFORM Count([EnrRpt Table05 Part1].txtStudentID) AS CountOftxtStudentID
SELECT [EnrRpt Table05 Part1].txtBigLevel1Sort, [EnrRpt Table05 Part1].txtBigLevel1Name AS [Level], [EnrRpt Table05 Part1].txtFixLevelName2 AS Program, [EnrRpt Table05 Part1].txtFixLevelSort, [EnrRpt Table05 Part1].txtYear, [EnrRpt Table05 Part1].txtTermName
FROM [EnrRpt Table05 Part1]
GROUP BY [EnrRpt Table05 Part1].txtBigLevel1Sort, [EnrRpt Table05 Part1].txtBigLevel1Name, [EnrRpt Table05 Part1].txtFixLevelName2, [EnrRpt Table05 Part1].txtFixLevelSort, [EnrRpt Table05 Part1].txtYear, [EnrRpt Table05 Part1].txtTermName
ORDER BY [EnrRpt Table05 Part1].txtBigLevel1Sort, [EnrRpt Table05 Part1].txtFixLevelSort
PIVOT [EnrRpt Table05 Part1].txtSexDescription;


And then here is the query that the query above calls (named EnrRpt Table05 Part1).

PARAMETERS [Enter YearTerm] Text ( 255 );
SELECT [ALL STUDENT DATA].txtYearTerm, vtblBigLevel1.txtBigLevel1Sort, vtblBigLevel1.txtBigLevel1Name, vtblFixLevel.txtFixLevelSort, vtblFixLevel.txtFixLevelName2, [ALL STUDENT DATA].txtStudentID, vtblSex.txtSexDescription, [ALL STUDENT DATA].txtYear, vtblTerm.txtTermName
FROM (([ALL STUDENT DATA] INNER JOIN ((vtblFixLevel INNER JOIN vtblClass ON vtblFixLevel.txtFixLevel = vtblClass.txtFixLevel) INNER JOIN vtblBigLevel1 ON vtblFixLevel.txtBigLevel1 = vtblBigLevel1.txtBigLevel1) ON ([ALL STUDENT DATA].txtCollege = vtblClass.txtCollege) AND ([ALL STUDENT DATA].txtLevel = vtblClass.txtLevel) AND ([ALL STUDENT DATA].txtClass = vtblClass.txtClass)) INNER JOIN vtblSex ON [ALL STUDENT DATA].txtSex = vtblSex.txtSex) INNER JOIN vtblTerm ON [ALL STUDENT DATA].txtTerm = vtblTerm.txtTerm
WHERE ((([ALL STUDENT DATA].txtYearTerm)=[Enter YearTerm]))
ORDER BY vtblBigLevel1.txtBigLevel1Sort, vtblFixLevel.txtFixLevelSort;
Nov 14 '08 #3

P: 3
Help? Anyone?
This problem is driving me crazy!
Nov 19 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.