Fill report parameter inside macro

[Lutzilutz]

Hi ! I’m trying to open a report (report based on a query with 1 parameter) from inside a macro. This macro is runned when clicking a button on a form. So when opening the report, a prompt shows up to fill that parameter.

Everything works except I have to manually fill the query parameter with the prompt, when it tries to open the report. I have the value that should go as parameter inside the macro, I just need to fill that parameter without any human input.

My macro is currently opening the report as follows :

Thisdatabasedocument.reportdocuments.getbyname("myReport").open

I have found a similar question with a query, but in my case it’s a report that is asking for a parameter. Any idea how to code that ?

[Villeroy]

PowerFilter.FB.odb (50.8 KB) demonstrates a filter table.
The filtered subform makes use of a parameter query “ParamFilter”. The parameters are substituted by the parent form which stores the filter criteria in a filter table. The filtered subform is editable because it selects from a single table.
The report is made from query “qReport” which merges data from all tables including the criteria in the filter report. Instead of parameters, it addresses the filter table directly.

Form: ... WHERE ( :paramP IS NULL OR "PID" = :paramP ) ....
Report: ... WHERE ( "D"."PID" = "F"."INT1" OR "F"."INT1" IS NULL )

[gaetanopr]

You should use a macro to modify the query, then take the variable value from a field in the form and modify the query.
Then open the report.

[Lutzilutz]

I don’t really want to modify the query, it’s working as expected and asking for the parameter when runned. Same for the report. What I would like is to fill the report’s parameter from inside my macro.

[gaetanopr]

You can use a second query for reports only

If the query is parameterized, you cannot insert the parameter from a macro, but you must modify the query that feeds the report.
I attach an example of a macro that modifies a query

Sub editquery
    Idc = 10' changing value to take from the id field for example
    ocontroller = thisdatabasedocument.currentcontroller
    if not ocontroller.isconnected then ocontroller.connect
    oconnection = ocontroller.activeconnection
    oquery = oconnection.Queries.getbyname("myReport")
    oquery.Command = "SELECT ......... WHERE `Id` = " & Idc & "

End sub   

[Lutzilutz]

I’ll give it a try, thanks ! What I don’t like in your solution is that my query is quite big and there is no way of using the GUI to build it, it would only be editable in SQL. I can copy-paste but it’s not very clean.

[gaetanopr]

Big or small the concept is always the same, from basic you can give SQL instructions, I wrote you an example.
I repeat, the macro simply opens a report and if it has parameters you have to add it manually, if you want automatic work you have to eliminate the parameter from the query and change it if necessary

A more complete example, to be associated with the button that opens the report

Sub editquery(ev)
Dim oForm As Object
oForm = ev.Source.Model.Parent
Ctl = oForm.GetByName("ID")  ' changing value to take from the ID field for example
Idc = Ctl.CurrentValue     
ocontroller = thisdatabasedocument.currentcontroller
if not ocontroller.isconnected then ocontroller.connect
oconnection = ocontroller.activeconnection
oquery = oconnection.Queries.getbyname("myReport")
oquery.Command = "SELECT ......... WHERE `Id` = " & Idc & "

Thisdatabasedocument.reportdocuments.getbyname("myReport").open
End sub  

[Lutzilutz]

Thanks, it works ! The only thing I dislike is that I have to build a query with the GUI, copy the SQL code and paste it inside the macro. Not fond of that workflow but hey, at least it works !

[RobertG]

That’s the problem with parameter. I only use a “Filter” table to set values and views, which read the value from “Filter”. Views will be a better datasource for Report Builder than a query. There are many bugs in Report Builder special with alias in a query, sorting and grouping a query …