By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,608 Members | 1,925 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,608 IT Pros & Developers. It's quick & easy.

Provide a value to a query prompt while running a report

P: 38
I built a query and use it in a report. The query has one parameter that must be satified at run time which is [Year_Id]. Rather than user type a value, I need to provide a value based on certain calculation which I intend to do when the report is open. I have the following code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2. MsgBox "Now In Report_Open Sub"
  3. 'Do the calculation here
  4. 'Provide a value for Year_Id of the query prompt
  5. End Sub
What I need to know is how can I reference the [Year_Id] of the query and give it a value.

Greatly appreciate your help in advance.
Saleem
Feb 23 '08 #1
Share this Question
Share on Google+
10 Replies


P: 38
Any ideas, anyone?
THank you,
Salzan
Feb 23 '08 #2

Scott Price
Expert 100+
P: 1,384
I doubt that doing this calculation in the On Open event of your report is going to work, since the query is 'queried' before the On Open event fires.

You'll have to work backward and do your calculation in the query itself, or in the On Click event of a button that opens the report.

What calculation are you attempting to perform?

Regards,
Scott
Feb 23 '08 #3

P: 38
I know for fact that the query is not executed prior to Report_Open. I tested it by placing a MsgBox in the code which comes up first and then the query prompt follows as I step in through the code. I'm assuming there's a control/attribute associated with the query paramter. I need to find a way to access it and set its valur.
Salzan
Feb 23 '08 #4

Scott Price
Expert 100+
P: 1,384
If you are going to force a calculation, why not write it into the query itself?

Regards,
Scott
Feb 23 '08 #5

P: 38
It's complicated. I have to process 3 files. collect the info that was recently entered. Determine the record date for the data. Read a 4th table which is a determines a valid fical year range and then produce the report for that year. I think it'll be easier if I can just findout how to set a value for a parameter in a query. Don't you think?
Salzan
Feb 23 '08 #6

Scott Price
Expert 100+
P: 1,384
In response to your question, I would say 'maybe'... I do not know of a way to satisfy the parameter value through code directly like you are wanting to do. It may be possible, but I doubt that it's very common, therefore you're not likely to find much about it on the internet, nor in MS help files. A parameter query is specifically constructed to require user participation, so therefore, attempting to short-cut this by background calculation is not likely to have very many proponents.

This MS article provides a possible work-around in that it involves creating a form to collect the user parameters. (btw you are right, something I didn't know, but is mentioned in the article is that the report On Open event fires before the RecordSource is queried).

You could possibly use this form to do the calculations you wish, simply opening it hidden, making it do the calculation and then closing it with the report.

Good luck!

Regards,
Scott
Feb 23 '08 #7

P: 38
Thanks Scott. I'll check out the article and report back on my progress.
Feb 23 '08 #8

NeoPa
Expert Mod 15k+
P: 31,769
I know for fact that the query is not executed prior to Report_Open. I tested it by placing a MsgBox in the code which comes up first and then the query prompt follows as I step in through the code. I'm assuming there's a control/attribute associated with the query paramter. I need to find a way to access it and set its valur.
Salzan
You're right Salzan. This is where the Help system suggests you add code to modify the RecordSource for a report.
However, unless you want to go to the trouble of :
  1. Getting the .SQL from the RecordSource.
  2. Doctoring it in code to apply whatever changes you would like.
  3. Setting the .RecordSource to the SQL string just created.
... then you'll have difficulty.
A QueryDef, run by the code, can have PARAMETERs passed to it, but this is not available (as far as I'm aware) when running a report BASED on a QueryDef.
Feb 28 '08 #9

NeoPa
Expert Mod 15k+
P: 31,769
If it's possible (you're not too clear in your question), it would certainly be better simply to apply a filter. That is, if the change you want to apply is simply in the WHERE clause.
Feb 28 '08 #10

NeoPa
Expert Mod 15k+
P: 31,769
I just looked at Scott's link. It's the D's Bs.
Look in there for pretty well all you could wish to know on the subject.
Feb 28 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.