Export report with different filename per record

[farcanal]

I am trying to:
1. email a different report to a list of recipients (contained in a table) so that each gets their relevant data/page only - similar to 'Dawnd3' - have got some ideas from the forum so will test.

2. Export the report (RTF) but save each page seperately with a different filename using a record reference (from the underlying query). Each page must then contain the relevant sub-report data. I can do this for one record using a combo box on a form, but
How do I get it to run through the table do a 'DoCmd.OutputTo' for each record AND use the record reference as part of the filename?

Thanks in advance

 

[kjv1611]

Maybe in a loop, either with a for-next or with looping through a recordset of your table or query, like below:

Dim db as DAO.Database
Dim rs as DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("TableName")

Do While Not rs.EOF
  strFileName = "MyFileName" & rs.Fields("ID")
  do the report for this record...

  rs.MoveNext
Loop

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

does that make sense?

 

[JerryKlmns]

Watch Out!

Dim db as DAO.Database
Dim rs as DAO.Recordset
[b]Set db = CurrentDb[/b]
Set rs = db.OpenRecordset("TableName")

Do While Not rs.EOF
  strFileName = "MyFileName" & rs.Fields("ID")
  do the report for this record...

  rs.MoveNext
Loop

rs.Close
[b]db.Close[/b]
Set rs = Nothing
Set db = Nothing

You 'll end up with your database closing! Do remove

db.Close

 

[kjv1611]

JerryKlmns,

db.Close does not actually close the "real" copy of the database, but rather the copy placed in memory for creating a recordset. I know this from experience, as I use that method very often, actually...

An Example:

Dim db as DAO.Database
Dim rs as DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("TableName")

With rs
   Do While Not .EOF
      .Edit
      .Fields("Name") = "A Super Duper Name"
      .Update
      .MoveNext
   Loop
End With

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

This example opens a copy of the database, then of the table (a recordset) in memory, makes the changes or does the search I want to do, then closes them out of memory (kind of like copying to the clipboard, then emptying the clipbaord).

Do close the database, you'd have to use a different command.

Even if there was a copy of the table open, that wouldn't close the table, just the recordset reference.

To close everything (access application included, do this):

DoCmd.Quit

I can't remember what the code is to only close the database, but it's not db.Close

 

[farcanal]

Thanks for ideas so far - but I have come unstuck on the first line.
'Dim db As DAO.Database' gives me a Compile Error: User defined type - not defined!

And when I search for help on DAO - its tells me help not installed - then goes into attempting to install this feature loop which returns with no success, over and over.

Access2000 issues?

Thanks again...

 

[PHV]

When in VBE menu Tools -> References ...
Tick the Microsoft DAO 3.# Object Library

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kjv1611]

Thanks, PHV, I didn't think to say [er, type] that part.