FilterOn property is not set reliably for reports - Please help!

[mvital]

Hi All,

I have a dialog form in Access where a user selects certain criteria in different combinations or by just one criteria within several simple listboxes and then clicks on the filter report button. I have some selections that when selected and filtered by give me a report filtered by the selection. I have other selections within the listbox that do not work and I don't know why. I have tested the code by pasting MSGBOX so I can see the build string.

I am new to coding. Below is the code for one of my listboxes. I want to filter by sample_id '0001'. When I placed the message box I see where it is searching for '0001', but the report is not filtered I see a blank report. The data is there. I can see that sample if I print preview the report and when I run the query that builds the report.

' Build criteria string from lstSample listbox
For Each varItem In Me.lstSample.ItemsSelected
strSample = strSample & ",'" & Me.lstSample.ItemData(varItem) _
& "'"
Next varItem
If Len(strSample) = 0 Then
strSample = "Like '*'"
Else
strSample = Right(strSample, Len(strSample) - 1)
strSample = "IN(" & strSample & ")"
End If

When I enter the following in the immediate window the report is filtered!! Why does my code above not do that? And this code does!?

DoCmd.OpenReport "rpt_contaminant", acViewPreview, , "Sample_ID = '0001'"

I found an article on the web where it sounds like there is a bug with Access. It sounds like the FILTERON is not set reliably for reports. This is what the article said: If you open a form with a WhereCondition, Access reports the form's FilterOn is True. If you open a report with a WhereCondition, Access may not set its FilterOn property. Any code in the report's events is therefore unable to determine whether if the Filter is applied or not." Allen Browne is the author. (

http://allenbrowne.com/bug-02.html

He doesn't suggest what to do to fix it or a work around. I am certain this is what is happening to my report filter. I tested it using ?Report!rpt_contaminant.FilterOn and I get a False.

Anyone have any ideas?

Thank you SO much in advance,

Maribel

 

[PHV]

How, in your code, is strSample used in the DoCmd.OpenReport call ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[formerTexan]

Hi Marbel,

How about going a couple more steps and creating the entire SQL string for the report's recordset since youalready have the criteria string worked out. Change the recordset instead of changing the filter. If you are using A2002 or later, you can then pass the SQL string as the report's openargs. For that matter I guess you could pass the filter criteria as well. Then reference either on the report's Open event. Sorry, but if you are in A2K or earlier, there isn't an OpenArg property for reports and something else will have to be thought of.

Cheers,
Bill

 

[mvital]

PHV this is some more code. Before the report is filtered the user makes the criteria selection(s) and clicks the apply filter button and the report is then opened with the following code,

' Check that the report is open
Dim Response As VbMsgBoxResult
If SysCmd(acSysCmdGetObjectState, acReport, "rpt_ContaminantandExposureLimit") <> acObjStateOpen Then
Response = MsgBox("The report is not open." _
& vbCrLf & "Do you want to open it now?" _
, vbQuestion + vbYesNoCancel)
Select Case Response
Case vbYes
DoCmd.OpenReport "rpt_ContaminantandExposureLimit", acViewPreview
Case vbNo
Exit Sub
Case vbCancel
DoCmd.Close acForm, Me.Name
Exit Sub
End Select
End If

The filters are build the same way for the other fields (list boxes)

' Build filter string
strFilter = " [SAMPLE_ID] " & strSample & _
" AND [Sampling_Event] " & strInvestigation & _
" AND [Location] " & strLocation & _
" AND [Analyte] " & strAnalyte & _
" AND [Work_Task] " & strTask & _
" AND [Employee_Name] " & strEmployee & _
" AND [Company_Name] " & strCompany & _
" AND [Source] " & strSource & _
" AND [Limit_type] " & strLimitType

The filter is applied like this:

' Apply filter and sort to report
With Reports![rpt_ContaminantandExposureLimit]
.Filter = strFilter
.FilterOn = True
.OrderBy = strSortOrder
.OrderByOn = True
End With
End Sub

I hope this helps.

 

[PHV]

Have you tried to build strFilter BEFORE opening the report and then:
DoCmd.OpenReport "rpt_ContaminantandExposureLimit", acViewPreview, , strFilter

instead of playing with the report's Filter property ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mvital]

I have tried a lot of things. I will try it now. Thank you PHV.

 

[mvital]

Well PHV, I tried it and it didn't work. I can still see the selections I could see in the report's print preview and still can't see the selections I couldn't. How frustrating it is.

Not sure what to do at this point. I tried the OpenArgs that formerTexan suggested and I get prompted for a parameter value. Aaaaagghhh!

:} I need to release this app soon. This is the only think stopping me from doing so.

 

[PHV]

Are by chance the selections you can't see containing some Null value(s) in some tested field(s) ?
If this a the case you may try something like this:
strFilter = " Nz([SAMPLE_ID][tt],'') [/tt]" & strSample & _
" AND Nz([Sampling_Event][tt],'') [/tt]" & strInvestigation & _
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mvital]

Oh, PHV that was it! I guess I didn't think that could be since the particular list box I had been looking at did not have them, but the a couple of the others do. I specified is not null, but can still see them!

I could just *hug* you or by you lunch!!

thanks!

MV