Filter vs WhereCondition in Report Access

powerz · May 19th, 2006, 06:05 PM

Hi, When I open a report, I want to use the filter in current Form.

Code:

If me.flterOn then
strFilter=Me.Filter
end if
Docmd.OpenReport myReport,acPreview,strFilter

The report displays all record regardless my filter.

If I put the strFilter in WhereCondition clause, it works.

Code:

Docmd.OpenReport myReport,acPreview,,strFilter

Could you tell me why? Thanks.

??VBA and ACCESS ??

echovue · May 19th, 2006, 06:16 PM

Someone else will probably have a better answer than me, but from what I understand, the Filter part of the OpenReport command refers to a named filter - not sure what that is though.

The where command is entered into the Filter property on the Report.

HTH

Mike

Mike
EchoVue.com

powerz · May 19th, 2006, 06:52 PM

Thanks Mike. My problem is when I filter on combobox in the form. The report would not recognize this filter.
Table :
tblAsset (as_id, at_id(foreign key))
tblAssetType (at_id, at_name)
Form :
frmAsset( txtAssetID, cmbAssetType)
Report:
rptAsset (txtAssetID, cmbAssetType)

When I set filter on cmbAssetType on frmAsset, I am not able to carry this filter to the report by using above code.
Access pops up windows "Enter Parameter Value: Lookup_cmbAssetType.at_name"

Any solution?

Sorry. forgot to mention the Row source for cmbAssetType is SELECT * FROM tblAssetType; Bound column 1, but display column 2 only.

??VBA and ACCESS ??

echovue · May 19th, 2006, 07:25 PM

So you're setting the filter based on the combobox, right?

When I do this, I set up the combo box, then when I run the code for the report, I do something like...

DoCmd.OpenReport "My Report", acPreview,, "AssetID=" & txtAssetID & " AND AssetType=" & cmbAssetType

Does that help out at all?

Mike

Mike
EchoVue.com

powerz · May 23rd, 2006, 02:57 PM

Yes. You got my point. But the solution seems not practical because I have more 20 fields in the form and 5 of them are combbox on the form. When I run the report, the user may have a filter that consists of more than 2 fields. If I can not pass Me.filter to the report, I have to analyze the filter and re-generate a WhereCondition and it seems no sense.

What do you think?

??VBA and ACCESS ??