Ok, time for some un-funkiness, that goes by the name of MS Access: we love to have fun while coding, but as we still have to eat sometimes we may happen to come across such irritating technologies, and the funky way of dealing with them is sharing pieces of the arcane knowledge required.

So, let’s say you have a MS Access db and you want to export a distinct MS Word file for every record shown in a form, which may be, for example, a letter to send to every client displayed by your form.

MS Access, very kindly, provides the built-in macro command DoCmd.outputTo that allows to export anything in it, be it a form, a query, a table or a report in any format chosen among Xls, Rtf, Txt and Html: sounds exactly like what we need, doesn’t it?

We should just put somewhere in our form a button that does DoCmd.outputTo when clicked and everything should be ok, right?

Of course not, we’re still working with MS Access, an environment where “expected” is far from reality.

The button we just added doesn’t export the data shown in our form, it exports the whole table, which is definitely wrong; how do we solve this?

The solution is really tricky, unelegant and unmaintainable, but it works and that is enough: basically, we have to add a dummy, empty query and set it as our report’s data source, then we have to dynamically inject the appropriate SQL for our query:

Dim qd As DAO.QueryDef
Set qd = CurrentDb.QueryDefs("QueryTestReport")
qd.SQL = "Select * from people where id_person = " & idPerson

Where, of course, “QueryTestReport” is the dummy query we just added.

We’re halfway through our journey, then: we still need to produce a separate file for every record in our form’s RecordSet, but that’s quite easy, since MS Access forms expose their own recordset allowing us to loop through it:

Dim rs As Recordset
Set rs = Me.RecordsetClone
while not rs.EOF
   idPerson = rs("id_person")

Voila