Crosstab Query and Parameters

[JamesMcS]

OK, clarifying again, are you just running the query or executing it from the open form?

 

[boblarson]

If i put [Forms]![frmLookupStrNumber].[form]![txtLookupStrNumber] in as a parameter which i read somewhere online to do, then the whole process works with the one exception of it always asks me for the parameter first.

If it is asking you for that then you didn't provide the correct answer to it in the parameters area. Your

[Forms]![frmLookupStrNumber].[form]![txtLookupStrNumber]

is not correct at all. If it is a subform reference then you have not provided one of the form names.

If frmLookupStrNumber is the subform then you would need:

[Forms]![YourMainFormNameHere].[frmLookupStrNumber].[form].[txtLookupStrNumber]

If frmLookupStrNumber is NOT a subform then you would need

[Forms]![frmLookupStrNumber].[txtLookupStrNumber]


And if it is a subform you must be referring to the subform control (control on the main form which houses the subform) and NOT the subform name itself UNLESS it and the subform control share the exact same name.

 

[chaostheory]

I want to run it from the open form without the parameter box popping up before the form opens.

I want to include screenshots but my company is ridiculously worried about corporate espionage and all the image hosting sites are blocked.

This works perfect on standard queries (non crosstab ones) like i said. I did them all the same way including the crosstab.

The crosstab is the only one that does not work. I can guarentee all field names are named correctly, as it has been rewritten 6-10 times. And i used the "build" function on the criteria right click options to pick the text box

 

[JamesMcS]

I've never seen an expression builder for criteria in query design. Are we talking about the same thing here?

 

[chaostheory]

Ok that makes sense, i will try to add the main form in the criteria and parameter box under query.

I was building the expressions using the access build function and it never put the main form in the criteria box...so i never thought to use it. This is my first attempt at making this whole.. "menu" stuff in access.

 

[JamesMcS]

I'm getting confused, expresison builder and criteria box? I'll leave you to it Bob.

 

[chaostheory]

I've never seen an expression builder for criteria in query design. Are we talking about the same thing here?

When im in my query design view. I right click on the box where you type in the criteria and you get a bunch of options. The last 3 i have are "Build, Zoom, Properties". Build takes me into an expression builder. I navigate to forms, where i have frmMain, frmLookupStrNumber, frmOthers, i click on frmLookupStrNumber, then click on the textbox and it puts the criteria in for me.

By subform i mean i have frmMain which is a form with buttons that open up all the other forms individually. I refer to them as subForms only because they aren't the main. If that is not the correct terminology i apologize

Ok screw it, screenshots coming, i will upload them to my home pc and post them from there...there is nothing sensitive in the shots...This should help ALOT

Gimme 5 minutes to take and upload them

 

[JamesMcS]

Oh right, never used that before.

[Forms]![frmLookupStrNumber].[form]![txtLookupStrNumber]

is not correct at all. If it is a subform reference then you have not provided one of the form names.

Referring to objects in this way works for me, the syntax came out of a link on this forum. And I thought we were referring to a box on the parent form.

 

[boblarson]

Oh right, never used that before.
Referring to objects in this way works for me, the syntax came out of a link on this forum. And I thought we were referring to a box on the parent form.

If it is on the parent form then the .form. part is extraneous.

 

[JamesMcS]

Still works though but point taken I'll leave it out in future.

 

[chaostheory]

Ok here goes!

Here is the queries and forms

The query is setup like this

With or without the second "[form]" appears to be irrelevant as your stated.

This is copy and paste what is written in the criteria minus the like "*" & parts

This is my menu

When i click any of the 3 buttons on the left set up identically to the crosstab query it opens up, i can search and it works.

when i click open form (which is sourced to the crosstab query) i get this

I type in anything valid or not and hit ok, or if i hit cancel....the form opens and it works fine, but this box is killing me....

Here are results after i cancel that box and perform a search

 

[JamesMcS]

Dude, TAKE THE PARAMETERS OUT AND LEAVE THE CRITERIA IN- that's why it's asking for input!!!!!!!

 

[chaostheory]

ok...i remove the parameter and i get this....

Not that im trying to tell you your wrong, but the other 3 queries are setup the same way, and none of them prompt me for input when run from the form. Only the crosstab query does...

 

[JamesMcS]

Have a look at them and see if there's a difference then chap.

One last thing - txtlookupstrnumber is on the form frmlookupstrnumber, and not in a subform is it? If it's a subform the syntax is different.

And I could well be wrong - we're all learning

 

[chaostheory]

This is the query the first button on the form "Ticket Number"

and the parameter filled in

This one works perfectly as does every other query setup the same way...its only crosstab that fails.

You may wish to read this.
http://allenbrowne.com/bug-13.html

 

[chaostheory]

Have a look at them and see if there's a difference then chap.

One last thing - txtlookupstrnumber is on the form frmlookupstrnumber, and not in a subform is it? If it's a subform the syntax is different.

And I could well be wrong - we're all learning

From what i gather it is not a subform, im using the wrong terminology. I have 5 forms, i use one form to open up the other forms. But they are all at top level.. I guess a subform is a whole diff type of form altogether in access. Like i said this is first time ive created a form menu etc

 

[JamesMcS]

Beats me then mate. The error in post 33, as far as I know, will either mean you've name the text box something different or the form isn't open.

I've never known a query to have criteria and parameters either.

Sorry I can't help more.

 

[JamesMcS]

A subform is an object you put on a form, like a button, to show another form or table within your main form. It's useful if you're, say, selecting values from a combo box on the main form to fitler the records that are shown in the subform. Have a play with them next time.

So when you click the open form button to open the form that's based on the CT query, it immediately pops up asking for input, even though there's nothing in the parameters of the query and the form that the criteria is looking at is open? That's weird.

 

[chaostheory]

No actually if i remove the parameters i simply get the jet database engine error. I don't have to declare parameters in standard queries, but for some reason crosstab queries require it.

Found a solution, it's annoying but it works. As found in that article:

[/B]
[B]Specify column headings[/B]
 
Since the column headings are derived from a field, you only get fields relevant to the data. So, if your criteria limits the query to a period when Nancy Davolio made no sales, her field will not be displayed. If your goal is to make a report from the crosstab, the report will give errors if the field named "Davolio, Nancy" just disappears.

To solve this, enter all the valid column headings into the Column Headings property of the crosstab query. Steps:[LIST=1]
[*]In query design view, show the Properties box (View menu.)
[*]Locate the Column Headings property. (If you don't see it, you are looking at the properties of a field instead of the properties of the query.)
[*]Type in all the possible values, separated by commas. Delimit text values with quotes, or date values with #.
[/LIST]For the query above, set the Column Headings property like this (on one line):
[INDENT]"Buchanan, Steven", "Callahan, Laura", "Davolio, Nancy", "Dodsworth, Anne", "Fuller, Andrew", "King, Robert", "Leverling, Janet", "Peacock, Margaret", "Suyama, Michael"
[/INDENT]
Side effects of using column headings:
[LIST]
[*]Any values you do not list are excluded from the query.
[*]The fields will appear in the order you specify, e.g. "Jan", "Feb", "Mar", ...
[/LIST]Where a report has a complex crosstab query as its Record Source, specifying the column headings can speed up the design of the report enormously. If you do not specify the column headings, Access is unable to determine the fields that will be available to the report without running the entire query. But if you specify the Column Headings, it can read the field names without running the query.

I added in all the column headings into that "Column Headings" field in properties, and now it magically works. Gotta love Microsoft....

 

[JamesMcS]

That is mental, I never even though that would be the problem.... well done for finding out the solutoin! I'll bloody remember that one!