How do I automate exporting multiple files from Access?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table ("Base_Table") which is updated on a weekly basis. Each week,
after update, I'd like to export a one-page report (snapshot format) for each
record (report already designed) using two fields from the query behind the
report (which is based upon "Base_Table") as part of the file name in the
export.

The filename should be "[Report Query]![Field1] WSR for [Report
Query]![Field2].snp"

(Note: I'm using a query rather than the table itself because it was the
simplest way to add a separately changing value to each of records in
Base_Table for the complete report. If it would be easier to to this from a
single table, let me know.)

I am not trained in Visual Basic at all, but have developed a working
knowledge of it. Can someone help me set up a function which runs the report
for each record in Base_Table separately and exports each report with the
file name structure above?
 
Here is one way using macros

Make a macro using these actions:
OutputTo
GoToRecord , Next
In the Output File argument of the OutputTo, put the
equivalent of...(Watch for wordwrap)
=[Report Query]![Field1] &" WSR for "
&[ReportQuery]![Field2] &".snp"

Then make a second macro, with these actions:
GoToRecord , First
RunMacro , Name of first macro.
In the Repeat Count argument of the RunMacro action, put
10. This assumes there will always be 10, otherwise you
need to put an expression that counts the number of records.

Jim
 
Thanks for your help on this. I've used a variation of your suggestion (with
the second macro running based on a DCount of the original table).

The only portion of the macro running into a snag is the attempt to build
fields from the record into the Output File name. Do you know the proper
format for doing this? Everything I've tried just crashes the program.

Jim/Chris said:
Here is one way using macros

Make a macro using these actions:
OutputTo
GoToRecord , Next
In the Output File argument of the OutputTo, put the
equivalent of...(Watch for wordwrap)
=[Report Query]![Field1] &" WSR for "
&[ReportQuery]![Field2] &".snp"

Then make a second macro, with these actions:
GoToRecord , First
RunMacro , Name of first macro.
In the Repeat Count argument of the RunMacro action, put
10. This assumes there will always be 10, otherwise you
need to put an expression that counts the number of records.

Jim
-----Original Message-----
I have a table ("Base_Table") which is updated on a weekly basis. Each week,
after update, I'd like to export a one-page report (snapshot format) for each
record (report already designed) using two fields from the query behind the
report (which is based upon "Base_Table") as part of the file name in the
export.

The filename should be "[Report Query]![Field1] WSR for [Report
Query]![Field2].snp"

(Note: I'm using a query rather than the table itself because it was the
simplest way to add a separately changing value to each of records in
Base_Table for the complete report. If it would be easier to to this from a
single table, let me know.)

I am not trained in Visual Basic at all, but have developed a working
knowledge of it. Can someone help me set up a function which runs the report
for each record in Base_Table separately and exports each report with the
file name structure above?
.
Click to expand...
Click to expand...
 
Back
Top