Ability to Create/Print Report from User Filtered Sub-form

[Tamille2020]

My end user is using a filter on a sub-form (SearchQSubform ) then pressing the "PrintPDF" button that is located on the form (SearchForms). However, all the searched data in the sub-form is printing. I only wish to print the user's filtered data. I tried putting the following code in the "On Click" event procedure for the "PrintPDF" button:

Private Sub PrintPDF_Click( )

DoCmd.OpenRepor t ReportName:="Re port", View:=acViewRep ort, WhereCondition: ="SearchQSubfor m.Filter"

Here are the details of the database:

Main Form Name: "SearchForm s:
Sub-form Name: "SearchQSubform "
The Query the sub-form is based on: "SearchQ"
Name of the report to be viewed/printed: "Report"

What am I doing wrong? Please help.

[NeoPa]

Nothing obvious that I can see. Oh yes there is. Almost missed it. Your WhereCondition parameter has quotes around it. This means instead of getting the value that's already set in the .Filter property you simply get the string "SearchQSubform .Filter" as your filter. Essentially a no-effect filter.

Also, remember that there is a difference between the SubForm Control and the Form that is currently set as being used by that Control. See Referring to Items on a Sub-Form for more on that.

Try instead ("SearchQSubfor m" needs to be the name of the SubForm Control) :

DoCmd.OpenReport ReportName:="Report" _
               , View:=acViewReport _
               , WhereCondition:=SearchQSubform.Form.Filter

[Tamille2020]

Hi NeoPa. The SubForm Control is SearchQSubform. I tried the suggested code, I get the "Enter Parameter Value" popup box. It is asking for "SearchQSubform .Recipients" input. The column that is being filtered by the user is "Recipients ".

[NeoPa]

That generally means that a reference is not recognised. For someone other than you to determine what & why would need a fuller set of information.

What is being filtered and what is the actual (string) value of SearchQSubform. Form.Filter at the time this is attempted. The following line of code will show this value in the Immediate Pane (See Debugging in VBA) :

Debug.Print SearchQSubform.Form.Filter;

Put it on the line prior to the DoCmd that runs the Report.

Another bit of information required to compare with is the Table, Query or SQL used as the RecordSource of the report. If it's a Query then we'll need to see the SQL of the Query and if it's a Table then we'll need a list of the Fields it has.

[Tamille2020]

Great suggestion NeoPa. I was able to get the sub-form filter to transfer over to the report via the following code:

Private Sub PrintPDF_Click()
    Dim strCriteria As String
        strCriteria = Trim$(Me![SearchQSubform].[Form].[Filter] & "")
    If Len(strCriteria) Then
        strCriteria = Replace$(Replace$(strCriteria, "[SearchQSubform].", ""), "SearchQSubform.", "")
    Else
        strCriteria = "(1 = 1)"
    End If

    DoCmd.OpenReport ReportName:="Report", View:=acViewReport, WhereCondition:=strCriteria
End Sub

But I'm wondering, is there a more simple method of doing this? I'm working with database administrators who may find this code hard to understand.

[NeoPa]

Here's a very slightly tidier version. In reality you're pretty close to what you need :

Private Sub PrintPDF_Click()
    Dim strCriteria As String

    With Me.SearchQSubform.Form
        strCriteria = Trim(Nz(.Filter, ""))
        If strCriteria > "" Then
            strCriteria = Replace(Replace(strCriteria) _
                                        , "[SearchQSubform].", "") _
                                        , "SearchQSubform.", "")
        Else
            strCriteria = "True"
        End If
    End With
    DoCmd.OpenReport ReportName:="Report" _
                   , View:=acViewReport _
                   , WhereCondition:=strCriteria
End Sub

In reality the .Filter value set for you should use either one of the formats so you could simplify further by removing the Replace() call that isn't used if you wanted to. Otherwise it's pretty close to doing just what it says on the tin and further simplification is impossible.