Ask Your Question

dynamic report - macro to change the query

asked 2015-07-02 13:46:22 +0200

jay Arr gravatar image

updated 2020-07-12 20:28:33 +0200

Alex Kemp gravatar image

LO Version: & MAC OSX 10.10.4

I have designed a form interface to a selection of BASE reports like this:

image description

and with this forum's help have been able to get the form's selection options to function.

What I want to be able to do, having generated with a macro a suitable query that replicates the users reporting options, is to edit and change the query to the basic EXISTING designed report so that when I open it with

DoCmd.OpenReport "myExistingReportName",2

the users reporting options e.g. Sorted in reverse chronological order, filtered between dates Date1 and Date2 will be executed.

By way of example you can do this with a form and pass a query and filter by way of arguments on the open statement.

I'd like some way of replacing that functionality with a report.

The alternative is to have 36 ( = 2 (sort) x 2 (ordered) x3 (content) x3 (filter options) ) SLIGHTLY DIFFERENT pre-designed reports which seems excessive and wasteful.


1) I know I can make the existing queries for the pre-designed reports somewhat dynamic by having parameterised queries for the Date filtering.

2) I'd be happy to have 3 reports (one for each of the different contents) so long as I could pass AT RUNTIME a generated query from the users selection.

What I'm asking for is the get/set macros to the internal PROPERTIES, & CONTROLS (which are hidden unlike a form) of a predesigned report; in particular it's

  • query
  • sort order
  • heading
  • labels

so that my reports can be truly dynamic and responsive to the user demands.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2015-07-03 15:24:00 +0200

pierre-yves samyn gravatar image

updated 2015-07-04 14:19:09 +0200


I wrote this wiki page showing how to parameter a report with values ​​from form fields. The page is in French, but the code should be understandable. There is an example to download.

The user enters the criteria in the form, click a button to open a report that will use the fields to filter data according to criteria.

The solution presented here is to base the report on a query. The program will change the SQL query before opening the report. In this example we use the form to enter a start date and an end date. We want to run the report on the data between these dates.

The principle is as follows:

  • Going from the "call" event (click on the form button) to "parent", then to the connection then to to the query.
  • The SQL query is updated to change the BETWEEN clause.
  • The procedure is associated with the Execute action of the button.


It is also possible to open and edit a report. I can not give an example of all that you want to modify. In the example below the label of a control is changed in the header of the detail section (replaced with time).

I advise you as a tool like Xray to find other properties.

I draw your attention to the fact that I do not guarantee the stability of this type of change. I advise you to save your database before your test. See ModifierRapports.odb

sub PysEditReport

dim Prop(2) as new
dim oConnection as object, oReports as object, oReport as object, oCtrl as object

oConnection = ThisDatabaseDocument.DataSource.getconnection("","")

Prop(0).Name = "ActiveConnection"
Prop(0).Value = oConnection
Prop(1).Name = "Hidden"
'choose true/false to open hidden or visible'
'Prop(1).Value = true'
Prop(1).Value = false
Prop(2).Name = "OpenMode"
Prop(2).Value = "openDesign"

oReports = ThisDatabaseDocument.ReportDocuments
oReport = oReports.loadComponentFromURL("Clients", "", 0, Prop())

'work on the detail section'
'for each oCtrl in oReport.Detail'

'work on the 1st "group" header. Here the detail header'
for each oCtrl in oReport.groups.getByIndex(0).header
    if oCtrl.label = "RéfClient" then
        oCtrl.label = time
    end if
next oCtrl

're-open the report visible, not design mode'
Prop(1).Value = false
Prop(2).Value = "open"

oReports.loadComponentFromURL("Clients", "", 0, Prop())

end sub

HTH. Regards

edit flag offensive delete link more


Pierre-Yves, yes this does help with the dynamic SQL and works very well with my reporting - despite my complete lack of French (had to reply on Google to translate). I'm inferring from your response that access to change controls, headings, labels in a report from a macro is not possible. Is this correct?

jay Arr gravatar imagejay Arr ( 2015-07-04 09:36:01 +0200 )edit

It is possible to edit a report. I update my answer to attach example.

pierre-yves samyn gravatar imagepierre-yves samyn ( 2015-07-04 14:21:51 +0200 )edit

Thank you again Pierre-Yves - this works really well and I'm now able to offer my users 48(!) different reports from 1(!) selection form, all fully annotated with the report's ordering and filtering criteria. The selection form uses 3 content reports and 3 queries all 6 of which are edited by macro. This provides a great template to build on the remaining reporting I'm developing.

jay Arr gravatar imagejay Arr ( 2015-07-05 20:46:39 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-07-02 13:46:22 +0200

Seen: 1,388 times

Last updated: Jul 04 '15