Need assistance to automate a process

[The Yankees Stat Zone]

Hi,
How do I export an HTML file from access based on a recordset?

Currenly, I use this command to export multiple HTMLs with the file
name as the rs!Shortname

DoCmd.OutputTo acOutputQuery, "Client Visit Record", acFormatHTML,
stExportLocation & "query\" & rs!ShortName & ".htm", no,
StTemplateLocation & "\Query Template.htm"

"Client Visit Record" is a parameter query that pops up the text box
for user to input a client shortname.
The recordset is
Set rs = db.OpenRecordset("UpdateClients_Upload2", dbOpenSnapshot)

I would like to do away with user data entry, and have access feed the
shortname to the parameter query and export the HTML file.

Any insight and guidance would be appreciated.

[Douglas J. Steele]

Only approach I can think of would be to dynamically change the SQL of the
query.

Let's assume that the SQL for Client Visit Record is something like:

SELECT Table1.Field1, Table1.Field2, Table2.Field2, Table2.Field3,
Table1.ShortName
FROM Table1 INNER JOIN Table2
ON Table1.Field1 = Table2.Field1
WHERE Table1.ShortName = [Client ShortName]

You'd need to add the following declarations to your code:

Dim qdf As DAO.QueryDef
Dim strSQL As String

Inside your loop that gives you all of the values of ShortName for which you
need to generate reports, you'd replace the one line of code you showed with
the following:

strSQL = "SELECT Table1.Field1, Table1.Field2, Table2.Field2, " & _
"Table2.Field3, Table1.ShortName " & _
"FROM Table1 INNER JOIN Table2 " & _
"ON Table1.Field1 = Table2.Field1 " & _
"WHERE Table1.ShortName = '" & rs!ShortName & "'"
Set qdf = CurrentDb.QueryDefs("Client Visit Record")
qdf.SQL = strSQL
qdf.Close
DoCmd.OutputTo acOutputQuery, "Client Visit Record", acFormatHTML, _
stExportLocation & "query\" & rs!ShortName & ".htm", no, _
StTemplateLocation & "\Query Template.htm"

Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele
Co-author: "Access Solutions", published by Wiley
(no e-mails, please!)

"The Yankees Stat Zone" wrote in message
news:f1580312-3a82-4ff2...@e7g2000vbw.googlegroups.com...