How to separate PDFs in one report

**twinnyfo** · Feb 23 '19, 12:30 AM

As stated before, if you open a report, you can filter it but not save it. If you export it, you can’t filter it. This is a shortfall in Access. In order to do what you are trying to do, you must build a filter into the query or the report that is managed outside the report. That is a very esoteric description but the basics is this: you establish a public string that you use as a filter. You set its value to "" then as you cycle through records you set its value to the filter strFilter = "EmployeeID = " & myrs!EmplyeeID. Then in the OnOpen event of the report, you check the value of that public string. If it is "" then do nothing but if not, then apply that filter string to the report’s filter. You do not OPEN the report, you must export it and that should work.

By the way, you may notice the simplicity I have shown for using a recordset’s value. Your method of referring to the Fields and value of that field is quite clumsy.

Please note that I have not analyzed your code in depth as I wanted to get you on the right track first.

**beginneraccess** · Feb 25 '19, 04:15 PM

Thanks twinnyfo for the info. I tried putting a filter into the query itself by putting

Code:

fGetID()

into the criteria, then making it a public function in a separate module. Then calling upon fGetID to equal EmployeeID. When that failed, I have tried to put a filter called strFilter upon opening the public sub report_load then adding the code you gave in above to my code. And now I'm totally confused... Are either methods I used what you are referring to?

Which code would be better to continue attempting this task?

**twinnyfo** · Feb 25 '19, 04:41 PM

You are 100% on the right track.

If you are using fGetID(), how are you setting the value of fGetID()? That would be my first question, because, in theory, that should work as well.

If you use my descsribed method, again, in a separate module, declare a public String Variable (in my case I use gstrReportFilte r). Your loop would look similar to this:

Code:

gstrReportFilter = ""
Do While Not myrs.EOF
    gstrReportFilter = = "EmployeeID = " & myrs!EmplyeeID
    DoCmd.OutputTo [I]etc., etc., etc.[/I]
    myrs.MoveNext
Loop

In the Report's VBA Module:

Code:

Private Sub Report_Open(Cancel As Integer)
On Error GoTo EH

    If Not gstrReportFilter = "" Then
        With Me
            .Filter = gstrReportFilter
            .FilterOn = True
        End With
    End If

    Exit Sub
End Sub

I prefer to set the filter at the Report level, rather than the Query level, because there may be cases in which you want to see all the records in teh Query. This just makes it easier (IMO).

Hope this hepps!

**beginneraccess** · Feb 25 '19, 05:06 PM

Thanks Twinnyfo

Code:

Private Sub cmdprintsep_Click()
    Dim rs As DAO.Recordset
    Dim myPDF, myStmt As String
    Dim gstrReportFilter As String

    myStmt = "SELECT distinct EmployeeID from queBalance"
    Set rs = CurrentDb.OpenRecordset(myStmt)
    gstrReportFilter = ""
    Do While Not rs.EOF
        myPDF = "C:\Users\user\Desktop\reports\" & Format(rs!EmployeeID, "000000") & ".pdf"
        
        gstrReportFilter = "EmployeeID = " & rs!EmployeeID
        DoCmd.OpenReport "Balance", acViewPreview, gstrReportFilter, "[EmployeeID] = '" & rs!EmployeeID & "'", acWindowNormal
        DoCmd.OutputTo objectType:=acOutputReport, objectName:="Balance", outputformat:=acFormatPDF, outputfile:=myPDF, outputquality:=acExportQualityPrint

        DoCmd.Close
        rs.MoveNext

    Loop
    rs.Close
    Set rs = Nothing

     
End Sub

NVM I GOT IT!!! THANK YOU SO MUCH TWINNYFO!!!!!!!

**twinnyfo** · Feb 25 '19, 05:43 PM

In a separate module:

Code:

Option Compare Database
Option Explicit

Public gstrReportFilter As String

Delete your Line 4
Delete your Line 13 (you never need to "open" a report to do this)
Delete your line 16

Thus:

Code:

Private Sub cmdprintsep_Click()
    Dim db     As DAO.Database
    Dim rs     As DAO.Recordset
    Dim myPDF  As String
    Dim myStmt As String
 
    myStmt = _
        "SELECT DISTINCT EmployeeID " & _
        "FROM queBalance;"
    Set db = CurrentDB()
    Set rs = db.OpenRecordset(myStmt)
    gstrReportFilter = ""
    With rs
        If Not (.BOF And .EOF) Then
            .MoveFirst
            Do While Not .EOF
                myPDF = _
                    "C:\Users\user\Desktop\reports\" & _
                    Format(!EmployeeID, "000000") & ".pdf"
                gstrReportFilter = "EmployeeID = " & !EmployeeID
                DoCmd.OutputTo _
                    ObjectType:=acOutputReport, _
                    ObjectName:="Balance", _
                    OutputFormat:=acFormatPDF, _
                    OutputFile:=myPDF, _
                    OutputQuality:=acExportQualityPrint
                .MoveNext
            Loop
        End If
        .Close
    End With
    db.Close
    Set rs = Nothing
    Set db = Nothing

    Exit Sub
End Sub

You may notice I made a few other minor adjustments. In the olden days, you could declare mutliple variables on one line without confusion; apparently this now only results in Variants being declared, except for the one followed by "As ...". (If I remember correctly). It is always better to be more explicit than less explicit when it comes to Access.

Although your method of setting the Recordset works absolutely fine, if you set several recordsets using this method, it creates more engine overhead. By declaring a separate variable as a Database, you can assign as many recordsets as you want and only use this one instance of the DB.

Note Line 11 especially. This is an absolute must every time you open any recordset. This method positively confirms that there are records to use (and if not, it just exits the process). Otherwise, your code may break or hang or cause a major rift in the intergalactic cosmic continuum vortex matrix. It's just a good habit to get into.

I made a few formatting changes to make things a bit easier to read, especially on the Forum.

I also highly recommend that you begin inserting some type of Error Handling code into each and every one of your procedures (even if it is just to tell the code to continue). This will prevent your DB from breaking on your users (and prevent more of those wormholes from forming).

Also, another recommendation. If you are saving your reports--especially over time, for historical purposes--you may want to add a descriptive name to the Report, e.g.:

Code:

myPDF = _
    "C:\Users\user\Desktop\reports\" & _
    "Current Balance Report - " & _
    Format(!EmployeeID, "000000") & _
    " - " & _
    Format(Date, "yyyy-mm-dd") & _
    ".pdf"

This way, you know what the report is about, who it belongs to, and when it was created.

Hope these little tidbits hepp!

How to separate PDFs in one report

How to separate PDFs in one report

Comment

Comment

Comment

Comment

Comment