Creating individual page files from an access report.

[AdrianLines]

I've searched around for a solution but without much success.

I need to be able to export to pdf/jpeg each page of a multipage report (where each page is an individual record), but I need to automate it so that the filename is created from the primary key field of each record.

I have tried ePrint 5 which does have an API but have limited vb knowledge .

We are trying to integrate a digital pen system into our 'scheduled service database'.

 

[Dontremb]

Have you tried this?

http://www.lebans.com/reporttopdf.htm

?

I'm not sure if it will solve ALL your problems, but it's another avenue.

 

[AdrianLines]

Yes, I have looked at Lebans libraries, but it seems to work only on the complete reports, not individual pages.

I was hoping it would create individual snapshots of each page first but it appears to do this in the library and not in the VB code.

 

[sxschech]

You may need to run the report one at a time. Perhaps a recordset of the list to be run, then do a loop and use the filename or identifying item to filter the report, print to pdf using the supplied filter/filename, then loop to the next item. If you don't have too many items, you could do a series of docmds or use the RunReportAsPDF code from Eric Provencher (Search in Tek-Tips using Keywords)

Then in your code do something like below. You can subsitute the variable name of the report for Report01, etc.

    Call RunReportAsPDF("rptCover", "c:\temp\Report01.pdf", , PrintChoice)
    DoEvents
Call RunReportAsPDF("rptOverview", "c:\temp\Report02.pdf", , PrintChoice)
    DoEvents
...

 

[AdrianLines]

I've got a bit further today !
I've managed to create the output style I require using the Lebans library with a single table. I.e a PDF file per page with a filename generated from the primary index field.
But now I'm stuck on the SQL Query format within the Recordset.
The actual report I'm trying to create is from three linked tables.
------VB Code follows----------
Private Sub Command9_Click()
Dim strDir As String 'This is the directory where you want to save the files
Dim blRet As Boolean
Dim rs As New ADODB.Recordset
Dim key As String
Dim MainDatabase As Database
Dim NewQuery As QueryDef
Dim QueryString As String
Dim GeneralQuery As String
Dim SearchDate As Date
strDir = "Y:\Service Records\Exports"
Set MainDatabase = CurrentDb
SearchDate = #6/6/2009# 'Form!("Work Due In Particular Month")![Date Due]
'This query narrows report down to a few specific records based on date.
GeneralQuery = "SELECT Visits.ID FROM Visits WHERE Visits.VisitDate > #" & SearchDate & "# ;"
rs.Open GeneralQuery, CurrentProject.Connection, adOpenKeyset
Do While Not rs.EOF
'set visit ID
key = rs.Fields("ID").Value
'set query to current Visit ID and include all fields necessary for final report.
QueryString = "SELECT Visits.*, Sites.*, SiteEquipment.* FROM (Visits INNER JOIN Sites ON Visits.SiteRef = Sites.SiteRef) INNER JOIN SiteEquipment ON Sites.SiteRef = SiteEquipment.SiteRef WHERE Visits.ID =" & key & ";"
'Delete existing Query
MainDatabase.QueryDefs.Delete "VisitIDQuery"
'Create new query based on QueryString
Set NewQuery = MainDatabase.CreateQueryDef("VisitIDQuery", QueryString)
'Run Query
Set NewQuery = MainDatabase.QueryDefs("VisitIDQuery")
NewQuery.Close
Set NewQuery = Nothing
'Lebans dll call
blRet = ConvertReportToPDF("Service Certificate", vbNullString, strDir & "Visit ID " & key & ".pdf", False, False, 0, "", "", 0, 0)
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub

 

[AdrianLines]

The error message I am getting with this code is..
'You tried to execute a query that does not include the specified expression [visits].[visitcompleteddate] as part of an aggregate function'

This field is part of the report, but should be included within the select statement Visits.* !