Changing Report query parameters in code before running Report

[sportsguy]

I have a good one for the MS Access masters of the web site:

I want to open a report from a button, and if the report is opened from Form A, the report query must have the Top Values parameter set to 25%, and when i open the report from another form, the report query must have the Top Values parameter set to 100%.

So I must open the reoprt in design view, acHidden, but then, how do i set the report queries parameters?

its not in any of the books that i have. . .

thanks

sportsguy

 

[shudini]

Is the record source of the report a SQL string, or a query saved in your database?

 

[sportsguy]

SQL string saved in the report

That makes it a bit more complikated, i realize

thanks

sportsguy

 

[phatnq2002]

I have a good one for the MS Access masters of the web site:

I want to open a report from a button, and if the report is opened from Form A, the report query must have the Top Values parameter set to 25%, and when i open the report from another form, the report query must have the Top Values parameter set to 100%.

So I must open the reoprt in design view, acHidden, but then, how do i set the report queries parameters?

its not in any of the books that i have. . .

thanks

sportsguy

Report has an event property named On Open. This occurs when report is called but not be loaded (so not be shown).
You can change the report RecordSource propery in the Report_Open event procedure. For example:

If callForm = "Form1" Then
RecordSource = "query1"
ElseIf callForm = "Form2" Then
RecordSource = "query2"
Else
...
End If


callForm: public variable contains name of the form call the report. It's value is set in the Click event procedure of the command button in that form.
query1: query returns top 25%
query2: query returns top 100%
so on ...

Or the second way:

In the Click event procedure of the command button in form 1, you issue the following statement:
Docmd.OpenReport "",acViewPreview,,,,"1"

And in the Report_Open event procedure will be:

If OpenArgs = "1" Then
RecordSource = "query1"
ElseIf OpenArgs = "2" Then
RecordSource = "query2"
Else
...
End If


OpenArgs is a property of the report (or the form) contains a value (string format) will be sent with the OpenReport command. Base of its value, you can do anything when report is opened to before it's closed.

 

[sportsguy]

That's what I was hoping to avoid, was having a string source in the code for each report, and swapping out the string source each time,because there is only one query, with different Top_values property for the recordsource query.

I would the have to parse up the string query by report to insert the Top Values property in the string and swap out the string each time, instead of being able to set the query properties . . .

oh well. . . .

thanks

sportsguy

 

[shudini]

That's what i was trying to understand - what did you want specifically. If you just want to manipulate the string of the row source, follow these instructions:

in the reports OnOpen event, put in this code:

    Me.RecordSource = "SELECT top " & OpenArgs & " percent ... FROM ... WHERE ...;"

where the query is the full SQL you want, and uses OpenArgs in place of what Percent you want. Save the report.
Create a global function named OpenMyReport (replace MyReport with your report name), with the following code:

Public Function OpenMyReport(topPercent As Integer)
DoCmd.OpenReport "MyReport", acViewPreview, , , , topPercent
End Function

Now, from any form in your database, create as many Command buttons as you want, and with each one, the OnClick event should say

=OpenMyReport(%%)

Obviously, you would replace the %% with the number of what percentage you want returned.
See - simple!