Ok, I think I have it figured out and I wanted to share just in case someone else tries this. Keep in mind, I just fixed this, so I might encounter other problems but so far it's working.
The recordset that populates the Sub Category repeat selection (see second image above) had to be recreated as a JOIN so that I brought the dependencies in from the relational table. But my first attempt caused a problem. My first JOIN looked like this;
SELECT SubCategoryID, SubCategoryName, Parent_SubCategoryID
FROM tblSubCategories LEFT JOIN relCat_SubCat ON Parent_SubCategoryID = SubCategoryID
The above was wrong because my query showed multiples of each Sub Category name.
Food
Food
Food
Bowls
Bowls
Bowls
Toys
Toys
Toys
Since there were 3 records at the time, for each Sub Category in the relational table, it was showing me everything.
After some study, I figured out I needed to include a GROUP BY in my statement. So I changed it to this;
SELECT SubCategoryID, SubCategoryName, Parent_SubCategoryID
FROM tblSubCategories LEFT JOIN relCat_SubCat ON Parent_SubCategoryID = SubCategoryID
GROUP BY SubCategoryID
So now I get this;
Food
Bowls
Toys
(This was very exciting to discover. The more I learn about JOINS the more control I find I have over everything).
Then, on to the delete for the relational table. I did just as Jaffa suggested and added a second delete behavior. It's a multiple delete records behavior. I used the same delete checkbox as the key, just as the first uses. But I referenced the relational table and Parent_SubCatID column instead of the unique TableID column. So even though it's getting the SubCatID from the first table, it uses that same value anyway to then look into the Parent_SubCatID column on the relational table and delete how ever many rows use this value.
One thing I did have to do by hand is change some values in the second multiple delete behavior because it tries to name things to make the second tables column. For example, this line
$WA_loopedIDField = array("WADA_RepeatID_Parent_SubCategoryID");
had to be changed to
$WA_loopedIDField = array("WADA_RepeatID_SubCategoryID");
to match the hidden field used in the form, for the first delete. I guess I could have added a second one but I don't know if that would have worked. This did.
So anyway, using the MRT, I can now insert new Sub Categories with Categories assigned to them within the relational table, then with a second delete behavior, it's deleting from both tables just fine. I'll report back if there are any problems. And if anyone sees a better way for me to do this, please let me know.
Thanks,
TroyD
