Bug? Subquery Fails in Filter Property on Form

  • Thread starter Thread starter Kenton Hensley
  • Start date Start date
K

Kenton Hensley

(Please excuse, this is a repeat of a previous post with a poor, overly
verbost description of the problem and mistakes!)

SYNOPSIS:
In an MS Access 2003 Project (.ADP) with SQL 2000, using a subquery in a
filter on a form produces the error
"Cannot apply Filter on one or more fields
specified in the Filter property."

DESCRIPTION
In this example, frmMain is to be filtered on values in the
tblDetail.CategoryID column. The SQL should describe the picture:

Set the frmMain.RowSource to be:
SELECT * FROM tblMain

Set the Filter property to tblMain.ItemID IN
tblMain.ItemID IN (SELECT tblDetail.ItemID
FROM tblDetail
WHERE tblDetail.CategoryID = 30)

3) Set frmMain.FilterOn = True and the 'cannot apply fillter' error message
indicated above is produced.

STUFF I'VE LEARNED
1) There is reference in the KB to this error message but it was associated
with Query-by-Form in MS Access 2002, for which a fix was developed and
published in SP2 for that version.

2) Replace the subquery with a text list of CategoryIDs e.g.
tblDetail.ItemID IN (1,2,3,4), and the filter works just fine.

3) The problem does not exist when this is used in an MDB (it surfaced after
I upsized the database).

In his particular application, this is needed everywhere for filtering the
main form based on it's detail tables. It would be prefereable not to set
the rowsource to a new query that has the filter builtin to the WHERE
clause.

Thank you in advance for your comments and able assistance.

--Kenton
 
In my experience, Access cannot handle subqueries in any capacity
(filters, rowsources or other backend VB code).

\\B
 
Back
Top