Calling a report from a macro with parameters

[didier.besset]

(It seems like something everyone would like to do, but could only links to outdated pages)

Given
1) a report build on a query with some parameters. In my case, the 2 parameters are the unique ID of some database records.
2) a form in which the parameter can be extracted. In my case, the end user selects the record in 2 table controls.

Wanted:
a button which, when clicked, calls the report with the parameters bounds to the user selection.

I started with writing a macro that correctly extracts the desired parameter values. I am trying to find a way to call this report while somehow binding the parameters to the extracted values.

If there is a solution to this problem, it would be nice to put it in the FAQ.

Cheers,

Didier

[Villeroy]

download/file.php?id=14591
Criteria entered into the main form are stored in a particular row of table "Filter".
Query "paramFilter" is the source of the filtered subform. It substitutes the parameters with the corresponding parent form values. This query depends on the single "Data" table includin its primary key so you can edit this filtered result set.
Query "qAggregateV" is for the calculation results based on the same filter criteria in row #1 of the filter table.
Query "qReport" is the source of the report (read-only). It simply merges all table data and joins them with the row #1 of the filter table.

[didier.besset]

Thanks for the prompt reply. I had come accross your solution in my many searches on this topic. I have no problem in using a filter table.

However, there is one thing I cannot graps: how is the filter table filled? Somehow, I cannot understand how a selection of a row implies the overwriting of a value in another table. If you would be so kind to explain this fine point...

Cheers,

Didier

[Villeroy]

Open the form for editing.
View>Toolbars>Form Design
5th button calls the form navigator.
Get the properties of the main form called "FilterForm", tab "Data"
select * from "Filter" WHERE FID=1
The form is bound to one particular row where ID equals 1. DOn't forget to create that particular row before you start testing.
No, we must not add nor remove any records here, nor must we see any navigation bar for a single record.

Get the properties of the subform called "DataForm", tab "Data"
it is bound to the ParamFilter query.
---------------------------------------------------
Filter-Row #1--> Data table
ParentField --> SubformParam (description)
---------------------------------------------------
D1 --> paramD1 (from date)
D2 --> paramD2 (until date)
INT1 --> paramP (Person ID)
INT2 --> paramC (Catagory ID)
otherwise it offers full edit, delete and insert access plus navigation bar.

Get the properties of the sub-subform or "DataForm" called "CalculationForm", tab "Data"
It is bound to the qAggregateV which calculates the aggregated values that correspond to row #1 in table "Filter". Calculated results are read-only anyway.
This form could be a sub-form of "FilterForm" as well because its fields are not linked to any parent field. Drag its form navi icon onto the "FilterForm" icon and it will work as well as before. You can also make it a stand-alone form (drag it onto the "Forms") but then it requires its own refresh button too.

1) Subforms refresh automatically with their parents.
2) Subform fields are filtered by the corresponding fields[*] of their parent form's current record which in this particular case is one particular record from a table of filter criteria.
3) Subform fields inherit default values for new records from corresponding fields of their parent form as you see when you enter new records into the filtered DataForm.
[*]The "corresponding fields" of a subform are defined by pairs of master fields and slave fields in the subforms data properties. You edit the multi-line controls in this dialog by entering unquoted lines separated by Shift+Enter. When you enter simple field names, these paires are simple mappings like A=X, B=Y, C=Z. Using parameters you can build more sophisticated criteria sets such as A=function(X), B<=Y, (C>=Z) OR (Z IS NULL).

[Villeroy]

Oh, partially that was off topic. So forget about the subforms. Look at the filter table, row #1 and look at the query "qReport":

Code: Select all

SELECT "D"."D" AS "Date", "P"."N" AS "Person Name", "C"."N" AS "Category", "D"."V" AS "Value" 
FROM "Categories" AS "C", "Data" AS "D", "Persons" AS "P", "Filter" AS "F" 
WHERE "C"."ID" = "D"."CID" AND "P"."ID" = "D"."PID" AND "F"."FID" = 1 
  AND ( "D"."PID" = "F"."INT1" OR "F"."INT1" IS NULL ) 
  AND ( "D"."CID" = "F"."INT2" OR "F"."INT2" IS NULL ) 
  AND ( "D"."D" >= "F"."D1" OR "F"."D1" IS NULL ) 
  AND ( "D"."D" <= "F"."D2" OR "F"."D2" IS NULL )

Which selects all the data we want to see in the report by joining all tables together so we see the category names instead of the IDs, the person names instead of the IDs, the dates and the values.
Everything is compared to the corresponding criteria values in row #1 of the Filter table ignoring any missing criteria.

Code: Select all

... AND ( "D"."PID" = "F"."INT1" OR "F"."INT1" IS NULL ) AND ... 

Let's assume Data.PID equals 1 and Filter.INT1 stores a 2 at row #1, this part of the criteria is not matched and the data record will be filtered out.
Let's assume Data.PID equals 1 and Filter.INT1 stores a 1 at row #1, this part of the criteria is matched and the data record will included if the other AND conditions match as well.
Let's assume Data.PID equals 1 and Filter.INT1 stores a NULL at row #1, this part of the criteria is matched anyway becaue it states that Data.PID should equal Filter.INT1 OR Filter.INT1 IS NULL. Due to the OR operator either part of this condition can make the whole condition return TRUE