Provide a value to a query prompt – while running a report

[salzan]

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:

Private Sub Report_Open(Cancel As Integer)
MsgBox "Now In Report_Open Sub"
'Do the calculation here
'Provide a value for Year_Id of the query prompt
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

[salzan]

Any ideas, anyone?
THank you,
Salzan

[Scott Price]

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

[salzan]

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

[Scott Price]

If you are going to force a calculation, why not write it into the query itself?

Regards,
Scott

[salzan]

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

[Scott Price]

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

[salzan]

Thanks Scott. I'll check out the article and report back on my progress.

[NeoPa]

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.

[NeoPa]

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.