Create multiple output files

  • Thread starter Thread starter John
  • Start date Start date
J

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
 
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
 
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
 
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.
 
Back
Top