Repeated Prompts for Parameters when Running Reports in Access

[bockyweez]

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!

[atsukoarai86]

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?

[bockyweez]

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 CountOftxtStude ntID
SELECT [EnrRpt Table05 Part1].txtBigLevel1So rt, [EnrRpt Table05 Part1].txtBigLevel1Na me AS [Level], [EnrRpt Table05 Part1].txtFixLevelNam e2 AS Program, [EnrRpt Table05 Part1].txtFixLevelSor t, [EnrRpt Table05 Part1].txtYear, [EnrRpt Table05 Part1].txtTermName
FROM [EnrRpt Table05 Part1]
GROUP BY [EnrRpt Table05 Part1].txtBigLevel1So rt, [EnrRpt Table05 Part1].txtBigLevel1Na me, [EnrRpt Table05 Part1].txtFixLevelNam e2, [EnrRpt Table05 Part1].txtFixLevelSor t, [EnrRpt Table05 Part1].txtYear, [EnrRpt Table05 Part1].txtTermName
ORDER BY [EnrRpt Table05 Part1].txtBigLevel1So rt, [EnrRpt Table05 Part1].txtFixLevelSor t
PIVOT [EnrRpt Table05 Part1].txtSexDescript ion;


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.t xtBigLevel1Sort , vtblBigLevel1.t xtBigLevel1Name , vtblFixLevel.tx tFixLevelSort, vtblFixLevel.tx tFixLevelName2, [ALL STUDENT DATA].txtStudentID, vtblSex.txtSexD escription, [ALL STUDENT DATA].txtYear, vtblTerm.txtTer mName
FROM (([ALL STUDENT DATA] INNER JOIN ((vtblFixLevel INNER JOIN vtblClass ON vtblFixLevel.tx tFixLevel = vtblClass.txtFi xLevel) INNER JOIN vtblBigLevel1 ON vtblFixLevel.tx tBigLevel1 = vtblBigLevel1.t xtBigLevel1) ON ([ALL STUDENT DATA].txtCollege = vtblClass.txtCo llege) AND ([ALL STUDENT DATA].txtLevel = vtblClass.txtLe vel) AND ([ALL STUDENT DATA].txtClass = vtblClass.txtCl ass)) INNER JOIN vtblSex ON [ALL STUDENT DATA].txtSex = vtblSex.txtSex) INNER JOIN vtblTerm ON [ALL STUDENT DATA].txtTerm = vtblTerm.txtTer m
WHERE ((([ALL STUDENT DATA].txtYearTerm)=[Enter YearTerm]))
ORDER BY vtblBigLevel1.t xtBigLevel1Sort , vtblFixLevel.tx tFixLevelSort;

[bockyweez]

Help? Anyone?
This problem is driving me crazy!