Pass query parameters from Form to crosstab

[rodmc]

HI Folks

Ive got a form which lets the user input parameters for queries/reports. Now this works fine with standard SELECT queries, but I cant get it to work with crosstabs. The crosstab query tells me the fields are not recognised (even though the criteria in the query is the same as the SELECT queries). Is there a reason for this that Im overlooking?

Thanks

Rod

 

[rodmc]

I should probably add that Ive added the path to the values in the form in the criteria ie:
[Forms]![FormName]![Field]

 

[vbaInet]

Is the form opened before you run the query? It must be open before running the query.

 

[boblarson]

I've found that you need to put in the parameters in both the underlying query and the crosstab from it. So, when you have your query open in design view you go to QUERY > PARAMETERS and enter them there - the form reference and the datatype.

 

[rodmc]

the form is opened and I have declared the parameters in the Parameters box, BUT I haven't declared them in full

I'll give that a shot Bob

thanks guys

 

[rodmc]

entered them in full but now its throwing up parameter input boxes, god thats friggin annoying

 

[boblarson]

What exactly did you put into the parameter area? Can you post a screenshot (post it here not on a public file sharing site as those are blocked by my work). Here's how to post them here.

 

[vbaInet]

entered them in full but now its throwing up parameter input boxes, god thats friggin annoying

It's either a field name you've changed, or a field name you've typed in incorrectly if it's throwing the error in the query.

 

[rodmc]

Was thinking that I might have made a typo somewhere, cant see it though, cant see the woods from the tree's at this point

 

[vbaInet]

So what prompt is it throwing up now?

 

[rodmc]

So what prompt is it throwing up now?

its asking for [Forms]![frmParaform]![Start]

 

[vbaInet]

A few more questions:

1. Is Start the name of the textbox or the name of the field?
2. Is the textbox name the same as the field name?
3. Is Referral a table or a query? If it is a query, are you sure you don't a prompt in there as well?

 

[boblarson]

Is Referral a table or a query?

 

[rodmc]

Start is the name of the textbox as is End, the fields and controls are different names (field name is ActDte) and Referral is indeed a table

wife just logged me out to play solitaire thats just totally irrelevant and bizarre, but hey, there you go, seems to be the way of my thinking of recent

 

[vbaInet]

Interesting!! Can we see a copy of your db (when your wife allows you to log back in)?

Maybe you should buy her a Wii

 

[rodmc]

Ive got some test data in it, but its not genuine data, so I suppose I could.

Ive got a wii, but then it isnt wired up cos my xbox is, priorities n all that

Right, I know its a bit of a nick but I do tidy things up before I deploy it, honest lol, oh and the naming conventions

 

[vbaInet]

Looks like the Xbox has its rightful place

You've got quite alot of queries in your db and it might take a while to plough through to find the problem one. Maybe a hint of the query name might help?

 

[rodmc]

Looks like the Xbox has its rightful place

You've got quite alot of queries in your db and it might take a while to plough through to find the problem one. Maybe a hint of the query name might help?

Discharged1

 

[rodmc]

your right about the queries though could probably cut down on them but sometimes its the nature of where I work, targets for everything

 

[vbaInet]

It works for me. I don't think you had the form open. When I say open, I mean the form must be in Form view, not design view with the Start and End Dates entered.