Filter a report from a pop-up form

[strong888hold]

Hi

I used this small database (see zip file attached) to adapt it to mine. I added 4 combo boxes (the database attached has 5 combo boxes)

I can select values in any of my combo boxes, my report property is set to “Filter On” however when I click on the Set Filter Button it shows an error message “Microsoft Access cannot find Filter(1,2,3 or which every filter i click on) contained in your expression” There is some error or text missing within the set filter Command button expression. How can I ensure all of my combo boxes are included in my SQL expression?

The SQL Script is shown below is what i have copied. The text highlighted in red is what the debug program highlights.

Private Sub Set_Filter_Click()
Dim strSQL as String, intCounter as Integer

' Build SQL String.
For intCounter = 1 To 5
[COLOR="Red"]If Me("Filter" & intCounter) <> "" Then[/COLOR]
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![rptBy].Filter = strSQL
Reports![rptBy].FilterOn = True
End If
End Sub

I tried to change the Me("Filter...) to Me.Filter = "[OwnerID]= & Chr(34) & Me.OwnerID.Value & Chr(34)...but I have the same problem and my report cannot be filtered until I debug this.
Hope anyone has a better idea.

Thanks

strong888hold

 

[Rickster57]

Report

I downloaded your db and clicked on the Select Filter button and got no errors. However, once I used one of the filters, run the report (which looks fine), and then click on Clear and again click on Select Filter, the data does not refelect All data it just keeps the old filter.

I placed the following code at the very top of your OnClick Event for your Select Filter button and this issue disappeared....

DoCmd.Close acReport, "rptCustomers"
DoCmd.OpenReport "rptCustomers", A_PREVIEW

 

[strong888hold]

Yes, the sample file works and there were no errors. I forgot to add my own database with the errors.
Check this. Click on form "FrmFilter2" then when reports open, choose a BusinesOwner Name or somethin else and then click on "Set filter". There is a line highlighted in yellow and that's the one I cannot debug. I used the same programming as in the sample database and now it can't work.

strong888hold

 

[Rickster57]

report

I made some changes. See if this does the trick

 

[strong888hold]

Hi Rickster

It works now!

A big big thanks for your help

strong888hold

 

[Rickster57]

report

The Select Filter code was looking for 5 combo boxes and the form only had four. Same with the code behind the Clear button.

Also changed the Tag of each combo box to it's data source field name.

Then added the code mentioned above.

 

[strong888hold]

I didn't noticed that and now it works fine. Again a big thanks for all.



strong888hold