Pass query parameters from Form to crosstab (1 Viewer)

R

rodmc

Registered User.

Local time

Today, 04:58

Joined

Apr 15, 2010

Messages

514

• Dec 20, 2010

• #1

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

 

R

rodmc

Registered User.

Local time

Today, 04:58

Joined

Apr 15, 2010

Messages

514

• Dec 20, 2010

• #2

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

 

V

vbaInet

AWF VIP

Local time

Today, 05:58

Joined

Jan 22, 2010

Messages

26,374

• Dec 20, 2010

• #3

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

 

boblarson

Smeghead

Local time

Yesterday, 21:58

Joined

Jan 12, 2001

Messages

32,059

• Dec 20, 2010

• #4

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.

 

R

rodmc

Registered User.

Local time

Today, 04:58

Joined

Apr 15, 2010

Messages

514

• Dec 20, 2010

• #5

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

 

R

rodmc

Registered User.

Local time

Today, 04:58

Joined

Apr 15, 2010

Messages

514

• Dec 20, 2010

• #6

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

 

boblarson

Smeghead

Local time

Yesterday, 21:58

Joined

Jan 12, 2001

Messages

32,059

• Dec 20, 2010

• #7

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.

 

V

vbaInet

AWF VIP

Local time

Today, 05:58

Joined

Jan 22, 2010

Messages

26,374

• Dec 20, 2010

• #8

rodmc said:

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

Click to expand...

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.

 

R

rodmc

Registered User.

Local time

Today, 04:58

Joined

Apr 15, 2010

Messages

514

• Dec 20, 2010

• #9

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

 

Attachments

• 
  FET1.jpg

  85.3 KB · Views: 2,709

V

vbaInet

AWF VIP

Local time

Today, 05:58

Joined

Jan 22, 2010

Messages

26,374

• Dec 20, 2010

• #10

So what prompt is it throwing up now?

 

R

rodmc

Registered User.

Local time

Today, 04:58

Joined

Apr 15, 2010

Messages

514

• Dec 20, 2010

• #11

vbaInet said:

So what prompt is it throwing up now?

Click to expand...

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

 

V

vbaInet

AWF VIP

Local time

Today, 05:58

Joined

Jan 22, 2010

Messages

26,374

• Dec 20, 2010

• #12

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

Smeghead

Local time

Yesterday, 21:58

Joined

Jan 12, 2001

Messages

32,059

• Dec 20, 2010

• #13

Is Referral a table or a query?

 

R

rodmc

Registered User.

Local time

Today, 04:58

Joined

Apr 15, 2010

Messages

514

• Dec 20, 2010

• #14

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

 

V

vbaInet

AWF VIP

Local time

Today, 05:58

Joined

Jan 22, 2010

Messages

26,374

• Dec 20, 2010

• #15

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

 

R

rodmc

Registered User.

Local time

Today, 04:58

Joined

Apr 15, 2010

Messages

514

• Dec 20, 2010

• #16

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

 

Attachments

• Evo FETIS.zip

  438.5 KB · Views: 94

V

vbaInet

AWF VIP

Local time

Today, 05:58

Joined

Jan 22, 2010

Messages

26,374

• Dec 20, 2010

• #17

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?

 

R

rodmc

Registered User.

Local time

Today, 04:58

Joined

Apr 15, 2010

Messages

514

• Dec 20, 2010

• #18

vbaInet said:

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?

Click to expand...

Discharged1

 

R

rodmc

Registered User.

Local time

Today, 04:58

Joined

Apr 15, 2010

Messages

514

• Dec 20, 2010

• #19

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

 

V

vbaInet

AWF VIP

Local time

Today, 05:58

Joined

Jan 22, 2010

Messages

26,374

• Dec 20, 2010

• #20

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.