By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,854 Members | 2,015 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,854 IT Pros & Developers. It's quick & easy.

Output HTML report for each selected record

P: 41
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 :)
Oct 8 '07 #1
Share this Question
Share on Google+
7 Replies


nico5038
Expert 2.5K+
P: 3,072
You could write a recordset processing loop and issue for each row found:

Expand|Select|Wrap|Line Numbers
  1. dim qd as DAO.Querydef 'Set "Microsoft DAO version 3.##" Library usingTools/References
  2. dim rs as DAO.Recordset
  3.  
  4. set rs = currentdb.openrecordset("Your table name")
  5. set qd = currentdb.querydefs("qryReportX")
  6. ' when no data can be the case test here for "rs.eof and rs.bof" !
  7. while not rs.eof
  8.    ' use rs!rowid to filter the report query to show only one row
  9.    qd.SQL = "select * from tblX where RowID=" & rs!RowID
  10.    DoCmd.OutputTo acOutputReport, "rptHTML", acFormatHTML, "C:\temp\" & rs!filenamevariable & ".html"
  11.    rs.movenext
  12. wend
  13.  
  14.  
Getting the idea ?

Nic;o)
Oct 10 '07 #2

P: 41
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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "rptRecipes", acNormal, , "[Selected] = -1"
So this is what I tried using your example to output to HTML:
Expand|Select|Wrap|Line Numbers
  1. Private Sub CommandHTML_Click()
  2. Dim qd As DAO.QueryDef 'Set "Microsoft DAO version 3.##" Library usingTools/References
  3. Dim rs As DAO.Recordset
  4.  
  5. Set rs = CurrentDb.OpenRecordset("tblRecipes")
  6. Set qd = CurrentDb.QueryDefs("qryReportX")
  7. ' when no data can be the case test here for "rs.eof and rs.bof" !
  8. While Not rs.EOF
  9.    ' use rs!rowid to filter the report query to show only one row
  10.    qd.SQL = "select * from tblRecipes where RowID=" & rs!RowID
  11.    DoCmd.OutputTo acOutputReport, "rptRecipesSimple", acFormatHTML, "C:\temp\" & rs!RecipeName & ".html", True
  12.    rs.MoveNext
  13. Wend
  14. 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
Oct 10 '07 #3

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 10 '07 #4

P: 41
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:
Expand|Select|Wrap|Line Numbers
  1.  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?
Oct 11 '07 #5

nico5038
Expert 2.5K+
P: 3,072
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:
Expand|Select|Wrap|Line Numbers
  1.  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.OpenRecordset("tblRecipes")
into:

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblRecipes WHERE Selected = True")

Thus only selected rows will be used in the loop.
Nic;o)
Oct 11 '07 #6

P: 41
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! :)
Expand|Select|Wrap|Line Numbers
  1. Private Sub CommandHTML_Click()
  2. Dim qd As DAO.QueryDef 'Set "Microsoft DAO version 3.##" Library usingTools/References
  3. Dim rs As DAO.Recordset
  4.  
  5. Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblRecipes WHERE Selected = True")
  6. Set qd = CurrentDb.QueryDefs("qryReportX")
  7. ' when no data can be the case test here for "rs.eof and rs.bof" !
  8. While Not rs.EOF
  9.    ' use rs!(Primary Key) to filter the report query to show only one row
  10.    qd.SQL = "select * from tblRecipes where RecipeID=" & rs!RecipeID
  11.    DoCmd.OutputTo acOutputReport, "rptRecipesHTML", acFormatHTML, "C:\temp\" & rs!RecipeName & ".html"
  12.    rs.MoveNext
  13. Wend
  14. qd.SQL = "SELECT * FROM tblRecipes WHERE Selected = True"
  15. End Sub
Thanks again for all your help, this has been a real learning curve for me. :)
Jenny
Oct 12 '07 #7

nico5038
Expert 2.5K+
P: 3,072
Learning fast I see :-)

Success with your application!

Nic;o)
Oct 12 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.