By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,694 Members | 2,015 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,694 IT Pros & Developers. It's quick & easy.

How to Filter a Report from a Pop-Up Form

P: 7
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
Jun 28 '07 #1
Share this Question
Share on Google+
15 Replies


JKing
Expert 100+
P: 1,206
What variable are you being prompted to enter as a parameter value?
Jun 28 '07 #2

P: 7
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
Jun 28 '07 #3

JKing
Expert 100+
P: 1,206
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.
Jun 28 '07 #4

P: 7
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
Jun 28 '07 #5

puppydogbuddy
Expert 100+
P: 1,923
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.
Expand|Select|Wrap|Line Numbers
  1. 'Set the Filter property
  2. Reports![rptEvaluation].filter = strSQL
  3. Reports![rptEvaluation].FilterOn = “Yes”
  4. Else
  5. Reports![rptEvaluation].FilterOn = “No”
  6. End If
  7.  
Jun 28 '07 #6

JKing
Expert 100+
P: 1,206
Try this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Set_Filter_Click()
  2. Dim strSQL As String
  3.  
  4. 'Build SQL String
  5. If Not IsNull(Me.Filter1) Then
  6. strSQL = strSQL & "[Presenter's Name] = '" & Me.Filter1 & "' AND "
  7. End If
  8.  
  9. If Not IsNull(Me.Filter2) Then
  10. strSQL = strSQL & "[Evaluator] = '" & Me.Filter2 & "' AND "
  11. End If
  12.  
  13. If Not IsNull(Me.Filter3) Then
  14. strSQL = strSQL & "[Date] = #" & Me.Filter3 & "# AND "
  15. End If
  16.  
  17. If Not IsNull(Me.Filter4) Then
  18. strSQL = strSQL & "[Topic] = '" & Me.Filter4 & "' AND "
  19. End If
  20.  
  21. If strSQL <> "" Then
  22. 'Strip Last " And "
  23. strSQL = Left(strSQL, (Len(strSQL) - 5))
  24. 'Set the Filter property
  25. Reports![rptEvaluation].filter = strSQL
  26. Reports![rptEvaluation].FilterOn = True
  27. Else
  28. Reports![rptEvaluation].FilterOn = False
  29. End If
  30.  
  31. End Sub
  32.  
Jun 28 '07 #7

JKing
Expert 100+
P: 1,206
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.
Jun 28 '07 #8

P: 7
Try this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Set_Filter_Click()
  2. Dim strSQL As String
  3.  
  4. 'Build SQL String
  5. If Not IsNull(Me.Filter1) Then
  6. strSQL = strSQL & "[Presenter's Name] = '" & Me.Filter1 & "' AND "
  7. End If
  8.  
  9. If Not IsNull(Me.Filter2) Then
  10. strSQL = strSQL & "[Evaluator] = '" & Me.Filter2 & "' AND "
  11. End If
  12.  
  13. If Not IsNull(Me.Filter3) Then
  14. strSQL = strSQL & "[Date] = #" & Me.Filter3 & "# AND "
  15. End If
  16.  
  17. If Not IsNull(Me.Filter4) Then
  18. strSQL = strSQL & "[Topic] = '" & Me.Filter4 & "' AND "
  19. End If
  20.  
  21. If strSQL <> "" Then
  22. 'Strip Last " And "
  23. strSQL = Left(strSQL, (Len(strSQL) - 5))
  24. 'Set the Filter property
  25. Reports![rptEvaluation].filter = strSQL
  26. Reports![rptEvaluation].FilterOn = True
  27. Else
  28. Reports![rptEvaluation].FilterOn = False
  29. End If
  30.  
  31. End Sub
  32.  
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?
Jun 28 '07 #9

P: 7
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
Jun 28 '07 #10

JKing
Expert 100+
P: 1,206
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT Data.[Presenter's Name] 
  2. FROM Data
  3. ORDER BY Data.[Presenter's Name]
  4.  
The keyword distinct eliminates any duplicates.
Jun 28 '07 #11

P: 7
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT Data.[Presenter's Name] 
  2. FROM Data
  3. ORDER BY Data.[Presenter's Name]
  4.  
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.
Jun 28 '07 #12

P: 7
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.
Jun 28 '07 #13

P: 1
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.
Jul 10 '07 #14

P: 3
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
Jul 30 '07 #15

P: 1
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Clear_Click()
  2.    Dim intCounter As Integer
  3.    For intCounter = 1 To 4
  4.        Me("Filter" & intCounter) = Null
  5.    Next
  6.  
  7.   'Set Report back to its unfiltered state
  8.     Reports![rptCustomers].FilterOn = False
  9.  
  10. End Sub
Nov 21 '08 #16

Post your reply

Sign in to post your reply or Sign up for a free account.