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 :)
7 2939 nico5038 3,080
Recognized Expert Specialist
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)
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 3,080
Recognized Expert Specialist
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)
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 3,080
Recognized Expert Specialist
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.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)
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 3,080
Recognized Expert Specialist
Learning fast I see :-)
Success with your application!
Nic;o)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Kingdom |
last post by:
I Need some serious help here. strugling novis with ASP and javascript
any help would be greatly appreciated
The script below does exactly what I want it to do for each product on
the two passes...
|
by: deko |
last post by:
I can't move a multi-page report to the last record unless I keep the popup
form (that defined it's subreports) open.
DoCmd.OpenReport "rptStandard", acViewNormal
DoCmd.Close acForm,...
|
by: John |
last post by:
Hi,
I have simple database based on 3 tables. Relationship is 'one to many'
between table 1 and 2, also between 2 and 3 table'one to many'.
I have made form where I enter data in all 3 tables....
|
by: Nathan Bloomfield |
last post by:
Does anyone know if there is any documentation which relates to Access2k + ?
or can anyone help adjust the code?
I am having trouble converting the DAO references.
TITLE :INF: How to...
|
by: Tom |
last post by:
I have a report where one field tends to be rather lengthly and ends
up being several lines long while another field has several short
entries. The problem I have is that the first of the short...
|
by: Sami |
last post by:
I hope someone will tell me how to do this without having to do any VB
as I know nothing in that area. I am a rank beginner in using Access.
I have created a database consisting of student...
|
by: cyclops |
last post by:
Hi All,
I am working on this project which requries making a product by adding one of 7 types of components (each type of component in turn has many variations).
product is made of about 25...
|
by: Fran |
last post by:
I recently tried to use code for "Use a multi-select list box to
filter a report" from Allen Browne in my database. I was able to add
the code and adapt it to my needs, however I am getting an...
|
by: Gord |
last post by:
Me again,
I'm new to Access and am self teaching from a couple of books, so bear with
me. (I've got a little experience with Visual Basic)
As I understand so far, if I want to perform a bunch...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |