CODEWITHSUNDEEP
vbams-accessfilterreportsubform
Nico_2014
Nico_2014

Reputation: 23

MS Access -- quick filter on a subform, then pass the filter to a report

I have a form (frmDocumentList) with a subform(frmDocumentDatasheet) and a print button which opens a report(rptDocumentList).

All i want to do is use the "quick filter function" (sort&filter-ribbon) on the subform. And then pass the filtered data to the report.

At the moment the report takes all of the data.

I think this is a simple problem but it is not possible for me atm to solve it. I have experience with vba. Hopefully someone can help me. -sorry for my bad english-

Here is the vba i tried: 

 Private Sub Command17_Click()

If Not IsNull(Me.Form![Document Datasheet].[Filter]) Then
    DoCmd.OpenReport "RptDocumentList", A_PREVIEW, , Me.Form![Document Datasheet].Filter
Else
    MsgBox "Apply a filter to the form first"
End If
End Sub

My problem is that the quick filter i used on the subform doesn't apply on the report.

Upvotes: 2

Views: 2218

Answers (1)

Smandoli
Smandoli

Reputation: 7019

If your command button is on the main form, then you want a different filter: you want the filter that is on the sub-form. Something like:

Option Explicit  ' <- be sure this is at top of module
                 ' ...and run Debug>Compile so it can perform its check

'' And on your button click event:
Private Sub Command17_Click()
    Dim strFilter as String
    strFilter = Me.Form![Document Datasheet].[Filter]
    Debug.Print strFilter
    If strFilter <> "" Then
        DoCmd.OpenReport "RptDocumentList", A_PREVIEW, , strFilter
    Else
        MsgBox "Apply a filter to the form first"
    End If
End Sub

Just reference the name of the subform -- that is, its control name as seen on the main form. More here.

Using a variable like strFilter will hopefully give you a bit more control so you can see what is (or is not) happening.

The Debug.Print command causes your filter string appear in the Immediate window. If you are unfamiliar with this, I suggest you do a bit of research as it will undoubtedly help you. For one thing, you can post the output -- maybe your string is somehow the problem.

You will notice my code tests for an empty string (""). Possibly your filter is an empty string and the IsNull() test is not helping you to detect that.

Upvotes: 1

Related Questions