Chapter 11. Master-Details
Let's select the companies sheet and add form fields at cell E4.
Then let's move cell F4, the Company field, to C1 and remove column E. Now cell C1 contains the active company.
Now, select cell E3 (outside of the active table) and connect to the dbo69.Payments table.
In the connection wizard, check at least the field used as a filter (Company) in the WHERE column:
and insert the table at cell E3:
We see that the dbo69.Payments table has ribbon parameters including Company.
Also, we see the selected company in cell C1 that can be used as a parameter. Let's link them.
Select cell C1 and click the Define Name button in the Defined Names group on the Formulas tab:
Specify the name as the parameter name, Company, and select the sheet name, companies, in the Scope field:
Click OK. Then select a row in the Company table, and voilà!
When you select the detail table, you can see its parameters at the ribbon:
Both tables are editable.
Here is a list of events that implement master-details:
- A user selects another row in a master table.
- The add-in updates form fields (cell C1 with Company).
- The add-in changes parameters of the detail tables using named cell values (cell C1 as Company).
- The add-in reloads the details with new parameter values.
You can build more complex forms using the same technique.
For example, the Northwind example contains the following tables with master-detail relations:
- Customer first char index (A-Z)
- Customer orders