Hi,
I've been running into problems with a form, which is quite complex, but I think I can demonstrate the problem by analogy. Basically, I want to set recordsource (selection) criteria through a sub-sub form value.
Three tables are the basis:
1. tbl_Top with field 'Top_ID' (linked to tbl_Middle.TopID), and unrelated fields
2. tbl_Middle with fields 'Middle_ID', child field 'TopID' linked to tbl_Top.Top_ID (One-to-Many) and unrelated fields
3. tbl_Bottom with fields 'Bottom_ID', child field 'MiddleID' linked to tbl_Middle_ID (One-toMany), unrelated fields and a criteria setting boolean field 'YES/NO'.
The form consists of a main form (called Top), a linked sub form (called Middle), and a linked sub-sub form (called Bottom).
If I use the following query to build the form with the form wizard
SELECT DISTINCT tbl_Top.*, tbl_Middle.*, tbl_Bottom.[UnRel], tbl_Bottom.[Yes/No]
FROM tbl_Top INNER JOIN (tbl_Middle INNER JOIN tbl_Bottom ON tbl_Middle.Middle_ID=tbl_Bottom.MiddleID) ON tbl_Top.Top_ID=tbl_Middle.TopID;
the wizard makes a form similar to what I have (and want - incl. subforms).
However, if I use the same query with the added 'Where' clause using a criteria out the bottom table
SELECT DISTINCT tbl_Top.*, tbl_Middle.*, tbl_Bottom.Bottom2, tbl_Bottom.[Yes/No]
FROM tbl_Top INNER JOIN (tbl_Middle INNER JOIN tbl_Bottom ON tbl_Middle.Middle_ID=tbl_Bottom.MiddleID) ON tbl_Top.Top_ID=tbl_Middle.TopID
WHERE (((tbl_Bottom.[Yes/No])=False)); (or True, whichever)
the wizard builds a form without subforms. What would I have to do that the form with subforms works also for the WHERE clause case?
I understand that my explanation is not very clear, and I apologize.
I appreciate any help, georgp.
I've been running into problems with a form, which is quite complex, but I think I can demonstrate the problem by analogy. Basically, I want to set recordsource (selection) criteria through a sub-sub form value.
Three tables are the basis:
1. tbl_Top with field 'Top_ID' (linked to tbl_Middle.TopID), and unrelated fields
2. tbl_Middle with fields 'Middle_ID', child field 'TopID' linked to tbl_Top.Top_ID (One-to-Many) and unrelated fields
3. tbl_Bottom with fields 'Bottom_ID', child field 'MiddleID' linked to tbl_Middle_ID (One-toMany), unrelated fields and a criteria setting boolean field 'YES/NO'.
The form consists of a main form (called Top), a linked sub form (called Middle), and a linked sub-sub form (called Bottom).
If I use the following query to build the form with the form wizard
SELECT DISTINCT tbl_Top.*, tbl_Middle.*, tbl_Bottom.[UnRel], tbl_Bottom.[Yes/No]
FROM tbl_Top INNER JOIN (tbl_Middle INNER JOIN tbl_Bottom ON tbl_Middle.Middle_ID=tbl_Bottom.MiddleID) ON tbl_Top.Top_ID=tbl_Middle.TopID;
the wizard makes a form similar to what I have (and want - incl. subforms).
However, if I use the same query with the added 'Where' clause using a criteria out the bottom table
SELECT DISTINCT tbl_Top.*, tbl_Middle.*, tbl_Bottom.Bottom2, tbl_Bottom.[Yes/No]
FROM tbl_Top INNER JOIN (tbl_Middle INNER JOIN tbl_Bottom ON tbl_Middle.Middle_ID=tbl_Bottom.MiddleID) ON tbl_Top.Top_ID=tbl_Middle.TopID
WHERE (((tbl_Bottom.[Yes/No])=False)); (or True, whichever)
the wizard builds a form without subforms. What would I have to do that the form with subforms works also for the WHERE clause case?
I understand that my explanation is not very clear, and I apologize.
I appreciate any help, georgp.
