troubleshooting Question

Report not opening with filter condition - need VBA filter help

Avatar of clinthammer
clinthammer asked on
Microsoft Access
4 Comments1 Solution970 ViewsLast Modified:
I have a search form with 5 fields. The user enters data in one or more fields and presses search. A vba filter takes this entered data and uses it as a condition when opening the report.
Report data is basically based on the search form.

This is the data type of the fields mentioned:
[Media Category] = text
[Advertising PR Genre] = text - combo box taking data from anoter table
[Advertising Agency Type] =text -combo box taking data from anoter table
[Country] = text - combo box taking data from anoter table
[Name] = text

The table containing those fields is called Advertising and PR. The fields are in a search form entitled frmSearchAdvertisingPR.

The field Media Category is always set to Advertising PR. for the other fields the user selects the criteria from the combo box or if its just a text field, the user types in the criteria.

When search is pressed the following code is run:

-------------------------------------------------------------------------------
Private Sub Command17_Click()

Dim strFilter As String
Dim strMsg As String

If IsNull(Me.cmbAgencyType) And IsNull(Me.cmbCountry) And _
IsNull(Me.cmbGenre) And IsNull([txtName]) Then
    strMsg = MsgBox("Please select or enter a search term!", vbOKOnly)
Exit Sub
End If
   
strFilter = ""

If Not IsNull(Me!txtMediaCategory) Then
    strFilter = strFilter & "[Media Category] like '*" & Me!txtMediaCategory & "*' AND "
End If

If Not IsNull(Me!cmbGenre.Value) Then
    strFilter = strFilter & "[Advertising PR Genre] = " & Me!cmbGenre.Value & " AND "
End If

If Not IsNull(Me!cmbAgencyType.Value) Then
    strFilter = strFilter & "[Advertising Agency Type] = " & Me!cmbAgencyType.Value & " AND "
End If

If Not IsNull(Me!cmbCountry.Value) Then
    strFilter = strFilter & "[Country] = " & Me!cmbCountry.Value & " AND "
End If

If Not IsNull(Me!txtName) Then
    strFilter = strFilter & "[Name] like '*" & Me!txtName & "*' AND "
End If

If strFilter <> "" Then
strFilter = Left$(strFilter, Len(strFilter) - 4)
strMsg = MsgBox(strFilter, vbOKOnly)
End If

On Error GoTo ErrorHandler
    DoCmd.OpenReport "rptAdvertisingPR", acViewPreview, , strFilter
ExitHandler:
    Exit Sub

ErrorHandler:
'This traps the Output to Error message
    If Err = 2501 Then
        Resume ExitHandler
    Else
        MsgBox Err.Description
        Resume ExitHandler
    End If


End Sub
--------------------------------------------------------------------------------
The report rptAdvertisingPr should open with criteria from the strfilter but it doesn't.

If I select only critera in the AdvertisingPR Genre combo box and press search it gives me a syntax error.

If I select criteria in either Advertising Agency Type  or Country, it asks me to Enter Parameter Value.

If I enter criteria in the Name field only - the report opens.

Why am I getting these syntax or enter parameter errors?
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros