Pass report parameter through a macro

[Guest]

I have several reports that are run on a monthly basis. They all have a
parameter for fiscal year and fiscal period. I want to build a macro that
will run all needed reports sequentially instead of having to do this
manually. What is the best way to pass the parameters so the user does not
need to enter it for each new report?

Also 1 of the reports contains info for several salespeople. In the macro
definition is it possible to use the where clause to get the report to run
for only 1 specific sales person at a time - or do I need to make mulitple
copies of the report and define a separate report for each sales person?


Thanks in advance.

 

[Steve Schapel]

Doug,

Can you please explain what exactly you mean by "a parameter for fiscal
year and fiscal period"? Thanks. Perhaps a specific example would help.

At this stage, I would guess that the criteria in the queries that the
reports are based on can be based on automatically calculated values.
Or else they can reference the value of unbound textboxes or comboboxes
on a from which is open at the time that the reports are processed.

No, you will not need to make a separate copy of the report for each
salesperson. Do you mean that you will want the report to print
separately for each of the salespeople, or do you mean you want to be
able to select specifically just one of the salespeople?

 

[Guest]

The report prompts for 2 parameters - fiscal year and fiscal period. I have
created a form to populate these values and the macro is running the main
report successfully.

My report layout currently is as follows:

Salesperson A
Data
Subtotal Salesperson A

Salesperson B
Data
Subtotal Salesperson B

ETC, ETC, ETC,
Report Total

The reports are distributed so that the manager gets a comprehensive report
of all the sales people and each sales person receives their individual
section. The question is - in my macro can i run the same report several
times filtering the individual sales people out or do i need to create a
separate report for each salesperson and include these individual reports in
my macro?

thanks in advance.

 

[Steve Schapel]

Doug,

One possible simple answer here would be to set the Force New Page
property of the Salesperson Footer section of the report, to 'After
Section'. So each salesperson's data will be printed on a separate
page, in effect the same outcome as printing the report separately for
each. Print the entire report twice, give the manager a complete set,
and give each salesperson his own section. Would that serve the purpose?

 

[Guest]

I have set the force new page property. The sales people are not all in the
same location. These reports are being delivered electronically - so the
manager receives the full report, then the full report has all of the extra
data deleted leaving only the appropriate individual salesperson data. This
has to happen multiple times to get each salesperson their data. I am not
totally against creating a separate report for each salesperson since i can
run via the macro, but would like to avoid it if possible.

Thanks in advance.

 

[Steve Schapel]

Doug,

It's ok, I was just shooting for the simplest solution first.

Outputting a separate copy of the report for each salesperson involves
looping sort of logic. While this is possible using a macro, it is
rahter awkward, and this type of process is where a VBA procedure would
be easier. The skeleton of the code is something like this...

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT EmployeeID FROM Employees
WHERE StaffType='Sales'")
With rst
Do Until .EOF
DoCmd.OpenReport "YourReport", , , "EmployeeID=" & !EmployeeID
.MoveNext
Loop
End With

Does that look something like it might be workable?