crosstab query asks for parameters when i save it

hey guys, just a quick question about a peculiar behaviour of Access. when i edit my already working cross tab query and then i press ctrl + s, it asks me for the parameters again and runs itself, but doesn't display any results.

Question: why does a cross tab query run when i save it?
developingprogrammerAsked:
Who is Participating?
 
developingprogrammerConnect With a Mentor Author Commented:
hey Dale, i managed to find this article and also i faced the problem you mentioned about the crosstab parameters

http://support.microsoft.com/kb/209778

the strange thing is that i have 2 crosstab queries,

- one i didn't define the parameters and it works fine
- one i didn't define the parameters and i doesn't work (it works when i defined 1 particular parameter. this crosstab query used 2 of its sub queries to calculate the result of the 3rd sub query (and then all the results are joined into a union then the crosstab)

my conclusion is that - if it's just a normal select parameterised query, don't need to define parameters

if it's a sibling dependent query then we may need to define the parameter - depending on what Access says it needs (from the error dialog).

the last thing is that, if i define the Column Headings in the properties pane, i don't need to define the parameters anymore.

HOWEVER i MUST define ALL the column headers that i want showing. if there is a value for Column X but i didn't put column X in the "Column Headings" property, it won't show.

here's the link to Allen Browne's article that i read to understand crosstab queries better = ) http://allenbrowne.com/ser-67.html

thanks Dale!

addition: the query that i didn't define parameters for, i also didn't define column headings and it works fine. it's just a normal select parameterised query, doesn't use 2 sub queries to calculate a 3rd sub query
0
 
Lawrence BarnesConnect With a Mentor Commented:
Hello,
The field names in a cross tab query are dynamic (when they create the column heading)...so it runs each time you open, save, etc. to verify/write/display the field names.
LVBarnes
0
 
PatHartmanConnect With a Mentor Commented:
The best way to stop queries/reports from prompting for parameters is to use form field references rather than prompts.  As long as the form remains open, the pesky query will get the values from the form.

Don't forget that Crosstabs also require that parameters be specifically defined.  Other query types aren't so rigid.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Dale FyeConnect With a Mentor Commented:
No points please,

To piggyback on Pats comment about declaring crosstab parameters. Even if your crosstab query uses another query, which has parameters, those parameters must be defined in the crosstab as well as in the base query, upon which the crosstab is base.
0
 
developingprogrammerAuthor Commented:
Hi Lawrence, thanks for that! i was thinking that after i defined Column Headers it won't prompt anymore but still wasn't the case ha.

Hi Pat, whao i didn't know that the crosstab queries need to have the parameters defined. thanks! = )

Hi Dale! Dale my crosstab query is based on other sub queries and sub-sub queries etc. in those sub / sub-sub queries there are parameters and they are already defined. however in my crosstab query i don't define them again. they seem to work fine.

why would i need to define the parameters in the cross tab query again even though i'm not using them in the cross tab query itself? (not talking about the sub / sub-sub queries)

thanks in advance! = )
0
 
Dale FyeConnect With a Mentor Commented:
@dp,

In all honesty, I have not tested that lately (in 2007, or 2010).  In earlier versions of Access, I remember that declaring the parameter in the sub-query was not sufficient, you had to declare it in the cross-tab query as well.  That may no longer be the case.
0
 
developingprogrammerAuthor Commented:
i joined Pat, Lawrence and Dale's answer together with Allen Browne's article and specific testing for Access 2007 to arrive at this conclusion at the moment
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.