Every time I have used the links between Master and Slave fields to link a subform to a main form, it has worked… until now. In this example, the master and slave links have absolutely no effect at all.
The scenario is that I have an inventory (aka “item”) table where each row represents an item in inventory. There is a subform underneath the item form, and the two are linked by the common field “itemid.” In the item table, the field is just called “id,” but this doesn’t matter.
The expected behavior is that only the line(s) that share a common Item ID master/slave relationship will appear in the table control. However, as I scroll through records of the Main Item form, nothing at all happens to the subform with the table control inside. It shows every single possibility even if the item id doesn’t match between the two forms.
You might think from my image that I didn’t include the Item ID in the subform because it doesn’t show as a column. I assure you it is there. To verify, I reveal the exact SQL statement used as a source of the subform, along with the visual output of testing/running the SQL in the SQL editor. Clearly the Item ID is among the columns. Not only that, but for thorough testing, I actually added the Item ID as a column in the subform table control, but then hid that column because it isn’t needed visually.
The filter master/slave connection does nothing at all. You can see that all three possibilities show up no matter what, even though their Item IDs don’t match the main form.
Is it because the SQL of the subform is quite complex instead of just a table call? It needs to be complex because it selects all invoice lines, summing up the totals grouped by month. This effectively gives me monthly usage information - quantity and total price. There isn’t much data yet because I’m just testing. There are only 3 unique items entered on one invoice, thus the 3 entries for November 2016.
It won’t filter for some reason. Any ideas why?
Visual on how the forms are related, all 3 items show up. In this case none should show up because there are no Item ID # 11 among the three rows. Rather, the 3 Item IDs returned are 5, 8 & 12:
frmMonthlyUsage is the subform which contains the table control. Here are screenshots of the linked fields.
Here is frmMonthlyUsage’s source SQL and how it looks when I test/run the SQL… revealing that the Item IDs are part of the returned set, and this column is in the table control too, just hidden:
The reason I used that particular SQL statement is because I have a MySQL backend. If I try TO_CHAR from HSQLDB instead of DATE_FORMAT, it throws an error. Turning the SQL Native mode on and writing the query in MySQL syntax is the only way to get it to return the results, and it works like a charm… but won’t filter on the subform!!!