[Solved] Link subform records navigation to main form

[papijo]

Hi and a Happy New year to all forum members!

In the attached POC (Proof of Concept) base, I have the following items. 2 tables, PEOPLE and ORDERS, with a PEOPLE 1 -> n ORDERS relationship. The PEOPLE ID is linked to the ORDERS PEOPLE_ID field.

1. The "Edit PEOPLE" form contains only a Main Form which displays a NAME field and a Table Control. When navigating from record to record, the record marker in the Table Control is correctly updated, as expected.

2. Now, I would like to reproduce the same navigation behaviour in my "Edit ORDERS" form, but I can't.

2a. The "Edit ORDERS" form consists of a Main Form with fields from the ORDERS table. It also has a SubForm, with Content type set to this SQL command: SELECT NAME, ID, PEOPLE_ID FROM PEOPLE, ORDERS WHERE ID=PEOPLE_ID. Link Master fields and Slave fields are left empty. In the SubForm I have a Table Control displaying NAME and PEOPLE_ID fields. This is OK, except that when navigation from ORDERS record to record in the MainForm, the records in the SubForm Table Control are not marked.

2b. The "Edit ORDERS1" form is identical to the "Edit ORDERS" form, except that in the SubForm I have linked Master field PEOPLE_ID with Slave field ID. Now, when navigating from ORDERS record to record in the MainForm, the record(s) in the subform Table Control only display those NAMES corresponding to the current ORDER field.

I would like in my Edit ORDERS form, my Table Control to behave in the same way as the Table Control in my Edit PEOPLE form. That is to say I do not want to have to choose between the present behaviour of my "Edit ORDERS" form and "Edit ORDERS1" form. Maybe that is simply not possible, or only feasible with a macro, in which case any pointers are welcome.

[MTP]

Your edit people form has a table control showing the table that you are editing. All rows of the table are shown, and you are editing one of them.
Subform linking by nature loads only those rows in the subform that match the currently selected row in the master form. As you have found, subform linking will not do what you want.

Take a look at the "Edit ORDERS2" form in the attached file. I removed the subform. I changed the mainform source from a table to an SQL query, and set the query to select all the fields from ORDERS (which you need because you want to edit these fields) and also the matching fields from PEOPLE (you want to display the name, and the primary key also has to be selected so the query will be editable - queries are not editable unless all the primary keys from all the involved tables are included in the SELECT). I gave an alias to the PEOPLE primary key field so it is distinguishable from the ORDERS primary key field.

Does this do what you're looking for?

[papijo]

Many thanks, MTP, this does the trick! I had not realized that a MainForm data can be set to an SQL command!
In the meantime, since I posted my question this morning I found a workaround with a macro, but the macro-less solution you provide is much better.
ATB