Macro to run sub reports

[Mike]

I want to automate a series of reports I manually produce for 30 workunits
and I cannot get my head around macros in Access.

I have a main report with 5 subreports. The report queries have a parameter
field that prompts for number code for each group of reports.

I run the reports for 30 work units so it is 30 x 5 entries.

Each report is published in MSWord as a .rtf file.

Some sample code to run and publish this type of project would be appreciated.

Thanks

 

[Steve Schapel]

Mike,

You would use an OutputTo macro action to automate the export of a
report to RTF. However, since you need to do this for each of the 30
work units, this is quite awkward and clunky to do with a macro. It
would be better to consider a VBA procedure, which will allow you to
more simply loop through the work units. I don't understand the bit
about the number code and the 30 x 5 entries. If you need more specific
help, could you give some more detail, maybe with examples.

 

[Mike]

Steve

There are 30 work units, each with an unique code . A group of 5 reports are
prepared via queries from data in 5 tables for each workunit on different
aspects of personnel, annual, long service, sick leave liability etc. I have
one query for each table with a parameter field that prompts for the work
unit code, thus for each work unit the code is entered 5 times.

Mke

 

[Steve Schapel]

Mike,

Well, ok, you could reasonably easliy do this with a macro.

First of all, make a form which is a continuous view form that shows the
number codes for the work units. Probably you will base this on a table
of work units, so if you haven't already got one of these it will be a
simple table.

Then, in your queries, in the place of the parameter prompts inthe
criteria, put instead a reference to the number code on the form, using
syntax such as...
[Forms]![NameOfForm]![Number Code]

Make a macro with these two actions:
OutputTo / nominate your report and output file arguments etc
GoToRecord / Next

Name and save this macro.

Now make another macro, using this action:
RunMacro
Macro Name: nominate the first macro
Repeat Count: 30

Ok, then assign this second macro on the Click event of a command button
on the form, set the focus to the first work unit record on the form,
click the button, and away you go. I think this should work ok.

 

[Mike]

Steve

Thanks for that. I have made-up some dummy data and applied you macro
suggestion and have had a result. The procedure overwrites the previous file
[Output to: c:\testsave\test.rtf] unless I select "No" to overwrite and
rename the file.

Mike

Mike,

Well, ok, you could reasonably easliy do this with a macro.

First of all, make a form which is a continuous view form that shows the
number codes for the work units. Probably you will base this on a table
of work units, so if you haven't already got one of these it will be a
simple table.

Then, in your queries, in the place of the parameter prompts inthe
criteria, put instead a reference to the number code on the form, using
syntax such as...
[Forms]![NameOfForm]![Number Code]

Make a macro with these two actions:
OutputTo / nominate your report and output file arguments etc
GoToRecord / Next

Name and save this macro.

Now make another macro, using this action:
RunMacro
Macro Name: nominate the first macro
Repeat Count: 30

Ok, then assign this second macro on the Click event of a command button
on the form, set the focus to the first work unit record on the form,
click the button, and away you go. I think this should work ok.

--
Steve Schapel, Microsoft Access MVP

Steve

There are 30 work units, each with an unique code . A group of 5 reports are
prepared via queries from data in 5 tables for each workunit on different
aspects of personnel, annual, long service, sick leave liability etc. I have
one query for each table with a parameter field that prompts for the work
unit code, thus for each work unit the code is entered 5 times.

Mke

 

[Steve Schapel]

Yes, Mike, you will need to use an expression in the Output File
argument of the macro in order to individually name the RTFs.