Full Version: Error Using A Form Filter Using Select
UtterAccess Forums > Microsoft® Access > Access Forms
My form uses a recordset that has two keys, STOCK and WAREHOUSE (both string types).

This works well:

Me.Filter = "StockCode IN (SELECT StockCode FROM tblStockCodeToTest) AND WAREHOUSE='S0'"

tblStockCodeToTest is a table. If I remove the last part AND WAREHOUSE='S0' it still works.

However this doesn't and I get an error 3709: "The Search Key was not found in any Record"

Me.Filter = "StockCode IN (SELECT StockCode FROM qryAllocSalesShort2) AND WAREHOUSE='S0'"

qryAllocSalesShort2 is a complex query with three tables.

What is wrong?
tina t
well, i'd check first to see if field StockCode is included in the output of qryAllocSalesShort2. then i'd run qryAllocSalesShort2 separately, and see if the stock code, or codes, is present in the records returned by qryAllocSalesShort2.

Yeah stockcode is integrally duplicated. My guess is that this method won't work if any field in the 2nd query (qryAllocSalesShort2) isn't already included the 1st one.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.