Create multiple output files

[John]

I receive a flat file monthly from my bank which shows all
the credit card charges for 60+ employees. I need to
generate a statement for each employee for them to review
and attach receipts. However, I don't want to generate
this statement as one report (with page breaks) since the
data is confidential...and I need to distribute individual
statement files via email.

I would like to write some code that, with one click, will
create an output file for each employee. The report would
have to identify each unique employee record, update a
query definition to filter by the first employee,
generate/export a report, and then move on to the next
employee and repeat. I understand the code required for
opening and exporting a report, but have not been able to
figure out how to loop from one employee to the next and
update the query def.

Can anyone help? I don't want to resort to creating 60
query defs and then running 60 reports with a macro.

Thanks,
John

 

[Greg Doudna]

See if this helps.

Create a query to select the individual employee ids.

Use that query as a recordset source in VBA, that then steps through each
Employee ID, executes a standard report but you pass as a filter the
employee ID number to the report.

Greg

 

[tina]

how about this? you could use a recordset in VBA, based on
the employee table and including the primary key to do the
following:

Rst.MoveFirst
Do
'output the report using a report filter
where "EmployeeID = " & Rst("EmployeeID")
Rst.MoveNext
Loop Until EOF = True

somewhere (in the report's underlying query, for instance)
you'll want to exclude employees who had no expenditures
listed in the flat file.

hth

 

[Mark Andrews]

You can steal code from our examples in our evaluation for our batch
reporting
module.

HTH,
Mark
RPT Software
http://www.rptsoftware.com

PS: If you decide to email PDF file attachments, maybe you will even
purchase our
product. Otherwise the code is free.