Let's create a new worksheet, rename it to CompanyPayments, and connect to the Company table at cell B3.
Then let's add a cursor and add form fields at cell F4.
Then let's move the ID and Name fields to cells C1 and D1, and remove labels in column F:
Now, select cell F3 (outside of the active table) and connect to the viewPayments view.
In the connection wizard, select only the one CompanyID field in the WHERE column:
and insert the table at cell F3:
We see that the viewPayment view has only one parameter at the ribbon, Company.
Also, we see the selected company ID 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 cell name as the parameter name, CompanyID, and select the worksheet name, CompanyPayments, in the Scope field:
Click OK. Then select a row in the Company table, and voilà!
Here is a list of events that implement this behavior:
You can build more complex forms using the same technique.
For example, the Northwind example contains the following tables with master-detail relations:
The following macro executes the steps described in the chapter:
' Creating master-details, Chapter 17 Sub Chapter17_1_CreateCompanyPaymentsWorksheet() Dim addIn As Object Set addIn = GetAddInAndCheck() If addIn Is Nothing Then Exit Sub Dim wb As Workbook Set wb = ActiveWorkbook Call addIn.InsertAddInSheets(wb) Dim connString As String connString = GetConnectionString() Dim ws As Worksheet Set ws = GetOrCreateWorksheet(wb, "CompanyPayments") ws.Select If ws.ListObjects.Count > 0 Then MsgBox "Worksheet CompanyPayments already has tables" Exit Sub End If Call InsertConnectedListObject(addIn, _ Range("B3"), connString, "dbo67.Companies", "TABLE", True) If ws.ListObjects.Count < 1 Then Exit Sub Call InsertConnectedListObject(addIn, _ Range("F3"), connString, "dbo67.viewPayments", "VIEW", True) If ws.ListObjects.Count < 2 Then Exit Sub Dim lo As ListObject Set lo = ws.ListObjects(1) Call addIn.AddTableCursor(lo) Dim fieldIdName As String fieldIdName = addIn.GetFormFieldCellName(lo, "ID") Dim fieldNameName As String fieldNameName = addIn.GetFormFieldCellName(lo, "Name") ws.Names.Add Name:=fieldIdName, RefersTo:=Range("C1") ws.Names.Add Name:=fieldNameName, RefersTo:=Range("D1") Set lo = ws.ListObjects(2) addIn.IsRibbonField(lo, "AccountID") = False addIn.IsRibbonField(lo, "CompanyID") = True addIn.IsRibbonField(lo, "ItemID") = False ' Set the initial value using ID from cell C1 (or using Name from cell D1) addIn.ParameterValue(lo, "CompanyID") = Range("C1").Value ' addIn.ParameterRibbonValue(lo, "CompanyID") = Range("D1").Value ws.Names.Add Name:="CompanyID", RefersTo:=Range("C1") ' Cell C1 must have two names: field_CompanyPayments_Table1_ID and CompanyID ws.Range("D4").Select End Sub
It creates a new worksheet and inserts two tables, dbo67.Payments and dbo67.viewPayments.
Then it adds a table cursor.
The code uses tricks to create the ID and Name form fields in the specified cells, C1 and D1.
You can use the GetFormFieldCellName function to get form field names and the VBA Worksheet.Names.Add and Workbook.Names.Add methods to create form field cells.
Then the code configures ribbon fields for the detail table. It leaves the CompanyID field only.
Then the code creates the CompanyID name for the form field in cell C1.
So, when a user changes a row in a parent table, the add-in updates the form fields, then updates parameters and reloads detail tables with new parameter values.