PDF report based on recordset filter- not working! HELP
Posted on 2012-03-14
In a member billing database, I have a table with all the member billing information.
I want to be able to print a report (which is an invoice) to a PDF file- one page per record. In other words, each PDF will be a single member's invoice for the past month.
What I have so far:
IN a module, I'm able to create a recordset based on the records for the month.
I am trying to apply a filter to that recordset and then use the filtered recordset as the report's recordsource.
However, when the code " DoCmd.OutputTo" line below, it is actually printed a 16 page reports (number of records in recordset 'rst')
I've removed the reports recordsource from the 'front end' and added the following code:
Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = grstFiltered.Name
but still all 16 records are in the PDF file!
Option Compare Database
Public grstFiltered As DAO.Recordset
Public Function PrintReport()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strFN As String
Dim strLN As String
Dim strReportName As String
Dim strNow As String
On Error GoTo PrintReport_Error
Set dbs = CurrentDb
strSQL = "SELECT BillsNew.* FROM BillsNew WHERE (BillsNew.BillDate='Feb 2012');"
Set rst = dbs.OpenRecordset(strSQL)
Do While Not rst.EOF
'Retrieve the name of the first Member in the selected recordset
strFN = rst.Fields("FirstName")
strLN = rst.Fields("LastName")
'Now filter the Recordset to return only one member
rst.Filter = "FirstName = '" & strFN & "' and LastName = '" & strLN & "'"
Set grstFiltered = rst.OpenRecordset
'Process the rows
Do While Not grstFiltered.EOF
strReportName = grstFiltered.Fields("LastName") & grstFiltered.Fields("FirstName")
strNow = Format(Now, "yyyymmddhhMMss")
DoCmd.OutputTo acOutputReport, "rptInvoice_New_PDF", acFormatPDF, "C:\Test\MemberINvoice" & strReportName & "_" & strNow & ".pdf"
Set rst = Nothing
Set grstFiltered = Nothing
Set rst = Nothing
Set dbs = Nothing
On Error GoTo 0
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure PrintReport of Module modPrinting"