Solved

crosstab query asks for parameters when i save it

Posted on 2014-02-06
7
558 Views
Last Modified: 2014-02-13
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?
0
Comment
Question by:developingprogrammer
7 Comments
 
LVL 5

Assisted Solution

by:Lawrence Barnes
Lawrence Barnes earned 125 total points
ID: 39840981
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
 
LVL 36

Assisted Solution

by:PatHartman
PatHartman earned 125 total points
ID: 39841066
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
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
ID: 39841537
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:developingprogrammer
ID: 39843589
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
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
ID: 39843866
@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
 

Accepted Solution

by:
developingprogrammer earned 0 total points
ID: 39844821
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
 

Author Closing Comment

by:developingprogrammer
ID: 39855707
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question