Output HTML report for each selected record

[KiwiGenie]

I have a form which lists selected records, there could be anything from 1 record to all records selected. I want to output a report for each record as HTML. I want each file to take its name from a certain field (RecipeName), so can't simply put a page break in the report.
Would it be possible to create a loop which opens the report hidden, then exports it to HTML, then closes the report, and does this for each selected record? Or would all that opening and closing be too much for access to handle?
Any help/ideas greatly appreciated as always :)

[nico5038]

You could write a recordset processing loop and issue for each row found:

dim qd as DAO.Querydef 'Set "Microsoft DAO version 3.##" Library usingTools/References
dim rs as DAO.Recordset

set rs = currentdb.openrecordset("Your table name")
set qd = currentdb.querydefs("qryReportX")
' when no data can be the case test here for "rs.eof and rs.bof" !
while not rs.eof
   ' use rs!rowid to filter the report query to show only one row
   qd.SQL = "select * from tblX where RowID=" & rs!RowID
   DoCmd.OutputTo acOutputReport, "rptHTML", acFormatHTML, "C:\temp\" & rs!filenamevariable & ".html"
   rs.movenext
wend

Getting the idea ?

Nic;o)

[KiwiGenie]

OK I'm sure I'm getting somewhere with this, however I am fully self-taught with Access, and I have never used OpenRecordset or QueryDefs before, in fact I have actively avoided them! Hopefully though they are not as scary as they first seemed.
At present, when I open the report (whose recordsource is tblRecipes) I use the where clause:

DoCmd.OpenReport "rptRecipes", acNormal, , "[Selected] = -1"

So this is what I tried using your example to output to HTML:

Private Sub CommandHTML_Click()
Dim qd As DAO.QueryDef 'Set "Microsoft DAO version 3.##" Library usingTools/References
Dim rs As DAO.Recordset
 
Set rs = CurrentDb.OpenRecordset("tblRecipes")
Set qd = CurrentDb.QueryDefs("qryReportX")
' when no data can be the case test here for "rs.eof and rs.bof" !
While Not rs.EOF
   ' use rs!rowid to filter the report query to show only one row
   qd.SQL = "select * from tblRecipes where RowID=" & rs!RowID
   DoCmd.OutputTo acOutputReport, "rptRecipesSimple", acFormatHTML, "C:\temp\" & rs!RecipeName & ".html", True
   rs.MoveNext
Wend
End Sub

Of course this gives me an item not found error on line 6, so after some research on Querydefs I figure I need to either create a new QueryDef, create a saved query and use that (I'm not sure what the difference is) or use a select statement in the OpenRecordset line above. Can you please advise?
Thanks for the help so far, very much appreciated :)
Jenny

[nico5038]

Great to hear that you did overcome your fear for VBA :-)

The Querydefs basically are all queries you see in the queries section of the Access database window.
To create your "qryReportX " you can just use copy/paste or define a dummy query, and save that with the name "qryReportX ". The contents don't matter, as we overwrite it every time with the real query we need for the report.
We're dynamically filling the query, followed by running the report off this query.

Getting the idea ?

Let me know the next hurdle !

Nic;o)

[KiwiGenie]

I'm not so sure I've gotten over the fear thing! lol
Now that I made a query qryReportX I get the error on this line instead:

 qd.SQL = "select * from tblX where RowID=" & rs!RowID

I'm not sure what tblX should be. I changed it to tblRecipes, the table the recordset is based on, but I'm not sure if this is right.
I want to start with only the selected records then output HTML for each (Selected is a yes/no field in tblRecipes), so where would I put the criteria for Selected=yes?
I can't find RowID in Access help or the object browser. Is this something that applies to newer versions of access? Mine is only Access97.
Any ideas?

[nico5038]

I'm not so sure I've gotten over the fear thing! lol
Now that I made a query qryReportX I get the error on this line instead:

 qd.SQL = "select * from tblX where RowID=" & rs!RowID

I'm not sure what tblX should be. I changed it to tblRecipes, the table the recordset is based on, but I'm not sure if this is right.
I want to start with only the selected records then output HTML for each (Selected is a yes/no field in tblRecipes), so where would I put the criteria for Selected=yes?
I can't find RowID in Access help or the object browser. Is this something that applies to newer versions of access? Mine is only Access97.
Any ideas?

You're close. The table in the select needs to be tblRecipes, but you also need to specify the unique identifier (Primairy Key).
I assumed the name was RowID, but you need to check the tblRecipes for the Keyfield and replace both RowID fields with that Key name.

As you only want the rows with "Selected = True" we also need to change the line:

Set rs = CurrentDb.OpenR ecordset("tblRe cipes")
into:

Set rs = CurrentDb.OpenR ecordset("SELEC T * FROM tblRecipes WHERE Selected = True")

Thus only selected rows will be used in the loop.
Nic;o)

[KiwiGenie]

Thank you! I was thinking RowID was a property or something..lol
Its all working good now, I had to use qryReportX as the report recordsource, so after the loop I set the sql back to what I want it to be if opening the report instead of outputting it to HTML (if that makes sense). So now I don't need the where clause on openreport either! :)

Private Sub CommandHTML_Click()
Dim qd As DAO.QueryDef 'Set "Microsoft DAO version 3.##" Library usingTools/References
Dim rs As DAO.Recordset
 
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblRecipes WHERE Selected = True")
Set qd = CurrentDb.QueryDefs("qryReportX")
' when no data can be the case test here for "rs.eof and rs.bof" !
While Not rs.EOF
   ' use rs!(Primary Key) to filter the report query to show only one row
   qd.SQL = "select * from tblRecipes where RecipeID=" & rs!RecipeID
   DoCmd.OutputTo acOutputReport, "rptRecipesHTML", acFormatHTML, "C:\temp\" & rs!RecipeName & ".html"
   rs.MoveNext
Wend
qd.SQL = "SELECT * FROM tblRecipes WHERE Selected = True"
End Sub

Thanks again for all your help, this has been a real learning curve for me. :)
Jenny

[nico5038]

Learning fast I see :-)

Success with your application!

Nic;o)