Solved Open a report that filters off a form field

[dullster]

I have a form that filters data by the DemoClientID to the subform. When a Client is selected on the form it brings up all the records for that Client in the subform. I have created a report that contains all the data I want to display and I have created a button to open that report. I want the report to filter off the DemoClientID that is on the from. This is the VBA code I have created but it doesn't filter by the DemoClientID. I brings up all the records. Can anyone tell me what I am doing wrong?

Private Sub cmdCurrent_Click()

    If Me.FilterOn And InStr(Me.Filter, "DemoClientID = " & Me!DemoClientID) > 0 Then
        DoCmd.OpenReport "rptVendorPayment", acViewPreview, , Me.Filter
    Else
        DoCmd.OpenReport "rptVendorPayment", acViewPreview
    End If

Exit_Preview_report_Click:
    Exit Sub

End Sub

 

[GaP42]

DoCmd.OpenReport has the following structure: OpenReport ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs
Where you are trying to pass the expression for the WhereCondition - A string expression that's a valid SQL WHERE clause without the word WHERE.
So what do you have? "Me.Filter" That is a value and not a string expression.
What should it look like? "[fieldname] = " & thevaluetofilterby
Look at the previous line in your code - the If statement where you check there is a value for DemoClientID. Looks like what you need.

 

[arnelgp]

maybe try:

Private Sub cmdCurrent_Click()
    dim strFilter As String
    strFilter = RemoveWordDotAndBang(Me.Filter)
    If Me.FilterOn  And nStr(strFilter, "DemoClientID") > 0 Then
        DoCmd.OpenReport "rptVendorPayment", acViewPreview, , strFilter
    Else
        DoCmd.OpenReport "rptVendorPayment", acViewPreview
    End If

Exit_Preview_report_Click:
    Exit Sub

End Sub

Function RemoveWordDotAndBang(ByVal inputStr As String) As String
    Dim re As Object
    Set re = CreateObject("VBScript.RegExp")
    With re
        ' Matches: sometext.  or  [sometext].
        .Pattern = "(\[?\w+\]?)\."
        .Global = True
        .IgnoreCase = True
        inputStr = .Replace(inputStr, "")
        .Pattern = "(\[?\w+\]?)\!"
        RemoveWordDotAndBang = .Replace(inputStr, "")
    End With
End Function

 

[dullster]

maybe try:

Private Sub cmdCurrent_Click()
    dim strFilter As String
    strFilter = RemoveWordDotAndBang(Me.Filter)
    If Me.FilterOn  And nStr(strFilter, "DemoClientID") > 0 Then
        DoCmd.OpenReport "rptVendorPayment", acViewPreview, , strFilter
    Else
        DoCmd.OpenReport "rptVendorPayment", acViewPreview
    End If

Exit_Preview_report_Click:
    Exit Sub

End Sub

Function RemoveWordDotAndBang(ByVal inputStr As String) As String
    Dim re As Object
    Set re = CreateObject("VBScript.RegExp")
    With re
        ' Matches: sometext.  or  [sometext].
        .Pattern = "(\[?\w+\]?)\."
        .Global = True
        .IgnoreCase = True
        inputStr = .Replace(inputStr, "")
        .Pattern = "(\[?\w+\]?)\!"
        RemoveWordDotAndBang = .Replace(inputStr, "")
    End With
End Function

 

[dullster]

After trial and error. I got it to work exactly as I wanted. When the subform populates with the records based off the DemoClientID on the main form, it opens a report with that information to print. I wanted to share my results for others that need to do this.

Private Sub btnVendorReport_Click()

    Dim strFilter As String

    If Not IsNull(Me!DemoClientID) Then
        strFilter = "DemoClientID = " & Me!DemoClientID
        
        If Me.FilterOn And InStr(Me.Filter, strFilter) > 0 Then
            DoCmd.OpenReport "rptVendorPayment", acViewPreview, , strFilter
        Else
            DoCmd.OpenReport "rptVendorPayment", acViewPreview, , strFilter
        End If
    Else
        MsgBox "DemoClientID is not selected.", vbExclamation
    End If

Exit_cmdCurrent_Click:
    Exit Sub

Err_cmdCurrent_Click:
    MsgBox "Error: " & Err.Description, vbCritical
    Resume Exit_cmdCurrent_Click

End Sub

 

[The_Doc_Man]

Just as an observation, your inner IF has identical actions for the TRUE and FALSE choices - which generally means that the inner IF is not needed. Your code works when the client ID is present because your local declaration of strFilter creates that variable as a zero-length string (an empty string). You certainly can provide an empty filter, i.e. it is not syntactically wrong to do that, so you would get no error message.

IF your goal was to open the report ONLY if the filter was enabled AND a non-empty DemoClientID was present, otherwise show an error - then your code might have looked more like this. IF you had another goal than what I just described, this suggestion doesn't matter.

Private Sub btnVendorReport_Click()

    Dim strFilter As String
    
    If Not IsNull(Me!DemoClientID) Then
        strFilter = "DemoClientID = " & Me!DemoClientID        
        If Me.FilterOn Then
            DoCmd.OpenReport "rptVendorPayment", acViewPreview, , strFilter
        Else
            MsgBox "Me.FilterOn was not enabled/true", vbExclamation
        End If
    Else
        MsgBox "DemoClientID is not selected.", vbExclamation
    End If

Exit_cmdCurrent_Click:
    Exit Sub

Err_cmdCurrent_Click:
    MsgBox "Error: " & Err.Description, vbCritical
    Resume Exit_cmdCurrent_Click

End Sub

 

[dullster]

Thank you. I will take a look at that maybe this weekend. I'm headed off to my daughter's later so I've got to get some stuff done today but thank you for noticing that

 

[dullster]

Just as an observation, your inner IF has identical actions for the TRUE and FALSE choices - which generally means that the inner IF is not needed. Your code works when the client ID is present because your local declaration of strFilter creates that variable as a zero-length string (an empty string). You certainly can provide an empty filter, i.e. it is not syntactically wrong to do that, so you would get no error message.

IF your goal was to open the report ONLY if the filter was enabled AND a non-empty DemoClientID was present, otherwise show an error - then your code might have looked more like this. IF you had another goal than what I just described, this suggestion doesn't matter.

Private Sub btnVendorReport_Click()

    Dim strFilter As String
   
    If Not IsNull(Me!DemoClientID) Then
        strFilter = "DemoClientID = " & Me!DemoClientID       
        If Me.FilterOn Then
            DoCmd.OpenReport "rptVendorPayment", acViewPreview, , strFilter
        Else
            MsgBox "Me.FilterOn was not enabled/true", vbExclamation
        End If
    Else
        MsgBox "DemoClientID is not selected.", vbExclamation
    End If

Exit_cmdCurrent_Click:
    Exit Sub

Err_cmdCurrent_Click:
    MsgBox "Error: " & Err.Description, vbCritical
    Resume Exit_cmdCurrent_Click

End Sub

I had time to run this code today. I immediately got the message box, "Me.FilterOn was not enabled/true", even though I should have data for the report.