M
msnews.microsoft.com
Can anyone shed light on the darkness described below?
SYNOPSIS:
In an MS Project (.ADP), if you use a subquery as part of a filter on a
form it produces the error "Cannot apply Filter on one or more fields
specified in the Filter property."
DESCRIPTION
The goal is to filter form based on values found in a detail table.
1) For example, assume a one to many relationship between tblMain and
tblDetail: tblMain.ItemID < tblDetail.ItemID. tblDetail also has a column
tblCategoryID and we want to filter tblMain based values of CategoryID.
2) The full SQL is straightforward assuming want to retrieve all records in
tblMain that have an associated record in tblDetail with a CategoryID of 30:
Set the form RowSource to be:
SELECT * FROM tblMain
Set the Filter property to tblMain.ItemID IN
tblMain.ItemID = (SELECT tblDetail.ItemID
FROM tblDetail
WHERE tblDetail.CategoryID = 30)
3) Set frmMain.FilterOn = True and the error message recorded in the
SYNOPSIS above is produced.
STUFF I'VE LEARNED
1) Replacing the subquery with a text list, e.g. IN (1,2,3,4) and all works
just fine.
2) The problem does not exist when this is used in an MDB (it surfaced after
I upsized).
In his particular application, this is needed everywhere. I'd rather not use
an already filtered recordset for the RowSource, which is one workaround.
Any other ideas on how to fix it?
SYNOPSIS:
In an MS Project (.ADP), if you use a subquery as part of a filter on a
form it produces the error "Cannot apply Filter on one or more fields
specified in the Filter property."
DESCRIPTION
The goal is to filter form based on values found in a detail table.
1) For example, assume a one to many relationship between tblMain and
tblDetail: tblMain.ItemID < tblDetail.ItemID. tblDetail also has a column
tblCategoryID and we want to filter tblMain based values of CategoryID.
2) The full SQL is straightforward assuming want to retrieve all records in
tblMain that have an associated record in tblDetail with a CategoryID of 30:
Set the form RowSource to be:
SELECT * FROM tblMain
Set the Filter property to tblMain.ItemID IN
tblMain.ItemID = (SELECT tblDetail.ItemID
FROM tblDetail
WHERE tblDetail.CategoryID = 30)
3) Set frmMain.FilterOn = True and the error message recorded in the
SYNOPSIS above is produced.
STUFF I'VE LEARNED
1) Replacing the subquery with a text list, e.g. IN (1,2,3,4) and all works
just fine.
2) The problem does not exist when this is used in an MDB (it surfaced after
I upsized).
In his particular application, this is needed everywhere. I'd rather not use
an already filtered recordset for the RowSource, which is one workaround.
Any other ideas on how to fix it?