How to Filter a Report from a Pop-Up Form

[scatterbrain]

I'm trying to create a popup form so that my peers have and easy way to filter information by 4 categories: Presenter, Evaluator, Date, or Topic. All this information is stored in my Data table and I have created a report called rptEvaluation. Seperately I have created a form with these Presenter, Evaluator, etc in a combo box. I have gotten as far as my pop up box coming up with the report and all the search options listed. I have also included a 'Clear' command button that successfully clears the data in the combo boxes. Everything works except for my 'Set Filter' button. I've been mimicking the codes in all the tutorials I've found but I am still unseccessful. This is the code I have for the 'Set Filter' command button:

Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 4
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Reports![rptEvaluation].filter = strSQL
Reports![rptEvaluation].FilterOn = True
Else
Reports![rptEvaluation].FilterOn = False
End If

End Sub

When I try to run it I get a message saying asking me to enter a parameter value. I'm not sure what this means.

I'm sure this question has already been answered but I'm still having difficulty. Please help

thank you

[JKing]

What variable are you being prompted to enter as a parameter value?

[scatterbrain]

What variable are you being prompted to enter as a parameter value?

I have no idea..the message just says "enter parameter value". In ignorance I've just typed in random names and numbers to see what happens and of course, my report shows up blank

[JKing]

I'm almost 100% sure the problem lies within strSQL. I think Me("Filter" & intCounter).Tag is returning Null so you're left with [] within your strSQL which is what is prompting you to enter a parameter. To verify this do a Debug.Print strSQL after your for loop. This will show you what criteria you're passing to the filter.

If this is indeed the problem. I can help you properly build the filter string if you tell me what controls and fields you are using on your form.

[scatterbrain]

I'm almost 100% sure the problem lies within strSQL. I think Me("Filter" & intCounter).Tag is returning Null so you're left with [] within your strSQL which is what is prompting you to enter a parameter. To verify this do a Debug.Print strSQL after your for loop. This will show you what criteria you're passing to the filter.

If this is indeed the problem. I can help you properly build the filter string if you tell me what controls and fields you are using on your form.

I just tried to learn this over the past two days so I'm not quite sure what you need. I already appreciate all that you're doing so if you can really decifer what the heck this all is then you will be my new super hero.

My Popup 'Presentation Filter' Form looks like this:

Presenter: (Filter1) rowsource:SELECT Data.[Presenter's Name] FROM Data GROUP BY [Presenter's Name];
Evaluator: (Filter2) rowsource: SELECT Data.Evaluator FROM Data GROUP BY [Evaluator];
Date: (Filter3) rowsource: SELECT Data.Date FROM Data GROUP BY [Date];
Topic: (FIlter4) rowsource: SELECT Data.Topic FROM Data GROUP BY [Topic];
(commandbutton)'Clear' (commandbutton)'Set Filter'

I'm trying to use these to filter my 'rptEvaluation' report. All fields come from my 'Data' table.


This is everything I have:

Private Sub Clear_Click()
Dim intCounter As Integer
For intCounter = 1 To 4
Me("Filter" & intCounter) = ""
Next
End Sub

Private Sub Form_Close()
DoCmd.close acReport, "rptEvaluation" 'Close the Evaluation report.
DoCmd.Restore 'Restore the window size

End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenReport "rptEvaluation", A_PREVIEW 'Open Evaluation report.
DoCmd.Maximize 'Maximize the report window.
End Sub


Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 4
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Reports![rptEvaluation].filter = strSQL
Reports![rptEvaluation].FilterOn = True
Else
Reports![rptEvaluation].FilterOn = False
End If

End Sub

[puppydogbuddy]

I think your problem may be that the report (unlike a form) actually has a filterOn property (vs a method for a form), and that it has to be set to Yes (not True) according to what I am reading from the following link, which says “For reports, you can apply a filter by setting the FilterOn property to Yes in the report's property sheet”..
http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx

so try changing the FilterOn to Yes as shown. I am not sure if Yes should be in quotes or not, so try it both ways.

'Set the Filter property
Reports![rptEvaluation].filter = strSQL
Reports![rptEvaluation].FilterOn = “Yes”
Else
Reports![rptEvaluation].FilterOn = “No”
End If
 

[JKing]

Try this:

Private Sub Set_Filter_Click()
Dim strSQL As String
 
'Build SQL String
If Not IsNull(Me.Filter1) Then
strSQL = strSQL & "[Presenter's Name] = '" & Me.Filter1 & "' AND "
End If
 
If Not IsNull(Me.Filter2) Then
strSQL = strSQL & "[Evaluator] = '" & Me.Filter2 & "' AND "
End If
 
If Not IsNull(Me.Filter3) Then
strSQL = strSQL & "[Date] = #" & Me.Filter3 & "# AND "
End If
 
If Not IsNull(Me.Filter4) Then
strSQL = strSQL & "[Topic] = '" & Me.Filter4 & "' AND "
End If
 
If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Reports![rptEvaluation].filter = strSQL
Reports![rptEvaluation].FilterOn = True
Else
Reports![rptEvaluation].FilterOn = False
End If
 
End Sub
 

[JKing]

On a side note, I think you might be wanting to use the ORDER BY clause instead of group by for your row sources. Group by is used when there are aggregates in the select statement.

[scatterbrain]

Try this:

Private Sub Set_Filter_Click()
Dim strSQL As String
 
'Build SQL String
If Not IsNull(Me.Filter1) Then
strSQL = strSQL & "[Presenter's Name] = '" & Me.Filter1 & "' AND "
End If
 
If Not IsNull(Me.Filter2) Then
strSQL = strSQL & "[Evaluator] = '" & Me.Filter2 & "' AND "
End If
 
If Not IsNull(Me.Filter3) Then
strSQL = strSQL & "[Date] = #" & Me.Filter3 & "# AND "
End If
 
If Not IsNull(Me.Filter4) Then
strSQL = strSQL & "[Topic] = '" & Me.Filter4 & "' AND "
End If
 
If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Reports![rptEvaluation].filter = strSQL
Reports![rptEvaluation].FilterOn = True
Else
Reports![rptEvaluation].FilterOn = False
End If
 
End Sub
 

Holy cow, it works! I really have been trying to figure this out for nearly three days now; I'm so grateful. However the 'Clear Button' isn't working the way I want it to. I assumed that once I applied a filter then pressing this button would refresh the report to its original state without filters but all it does is clear the combo box. Any suggestions?

[scatterbrain]

Holy cow, it works! I really have been trying to figure this out for nearly three days now; I'm so grateful. However the 'Clear Button' isn't working the way I want it to. I assumed that once I applied a filter then pressing this button would refresh the report to its original state without filters but all it does is clear the combo box. Any suggestions?

I changed the ORDER BY because instead of listing just several names I wanted, it listed the names everytime it appeared in the table. If Jose Noriega was a presented 10 times, his name appeared 10 times on my combo box when I just wanted it once

[JKing]

Glad to hear the filter works.

Try adding Reports![rptEvaluation].FilterOn = False to the on click event of the clear button. You're clearing the fields but the filter for the form is still set to the previous values. Upon turning off the filter it should set the report back to it's unfiltered state.

For the row sources you can eliminate the duplicates and still use orderby to sort the information nicely. Here's how:

SELECT DISTINCT Data.[Presenter's Name] 
FROM Data
ORDER BY Data.[Presenter's Name]
 

The keyword distinct eliminates any duplicates.

[scatterbrain]

Glad to hear the filter works.

Try adding Reports![rptEvaluation].FilterOn = False to the on click event of the clear button. You're clearing the fields but the filter for the form is still set to the previous values. Upon turning off the filter it should set the report back to it's unfiltered state.

For the row sources you can eliminate the duplicates and still use orderby to sort the information nicely. Here's how:

SELECT DISTINCT Data.[Presenter's Name] 
FROM Data
ORDER BY Data.[Presenter's Name]
 

The keyword distinct eliminates any duplicates.

I sincerely thank you. I've made so much progress and I appreciate the time you took to help me out.

[scatterbrain]

I sincerely thank you. I've made so much progress and I appreciate the time you took to help me out.

Sorry but something else just came up.

Once I select a filter and set it, it works fine. But once I clear it and it goes back to its unfiltered state the filter doesn't work a second time; I have to close and reopen it each time I try to change a filter.

[aaquis]

Sorry but something else just came up.

Once I select a filter and set it, it works fine. But once I clear it and it goes back to its unfiltered state the filter doesn't work a second time; I have to close and reopen it each time I try to change a filter.

Hey,

Thanks for the previous postings. It really saved my ass.

The workaround I found for your issue was to reload the report once "clear" was clicked. So, your Clear_Click macro, should look like:

Private Sub Clear_Click()
Dim intCounter As Integer
For intCounter = 1 To 4
Me("Filter" & intCounter) = ""
Next

DoCmd.close acReport, "rptEvaluation"
DoCmd.OpenReport "rptEvaluation", A_PREVIEW 'Open Evaluation report.
DoCmd.Maximize 'Maximize the report window.

End Sub

If anyone has a better solution, please post.

[kcachaler]

Hey,

Thanks for the previous postings. It really saved my ass.

The workaround I found for your issue was to reload the report once "clear" was clicked. So, your Clear_Click macro, should look like:

Private Sub Clear_Click()
Dim intCounter As Integer
For intCounter = 1 To 4
Me("Filter" & intCounter) = ""
Next

DoCmd.close acReport, "rptEvaluation"
DoCmd.OpenReport "rptEvaluation", A_PREVIEW 'Open Evaluation report.
DoCmd.Maximize 'Maximize the report window.

End Sub

If anyone has a better solution, please post.

I think you might be able to use the requery command or something like that instead. Not sure though.
docmd.requery "name of query"
you might have to look into that in a bit more detail

[Kyrandia84]

Sorry but something else just came up.

Once I select a filter and set it, it works fine. But once I clear it and it goes back to its unfiltered state the filter doesn't work a second time; I have to close and reopen it each time I try to change a filter.

The filter doesn't work a second time because your Clear button is setting the value of the combo box fields to
" " (space) instead of NULL.

So... your Clear button code should be:

Private Sub Clear_Click()
   Dim intCounter As Integer
   For intCounter = 1 To 4
       Me("Filter" & intCounter) = Null
   Next
 
  'Set Report back to its unfiltered state
    Reports![rptCustomers].FilterOn = False
 
End Sub