Chapter 17. Master-Details

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:

1. A user selects another row in a master table.
2. The add-in updates form fields (cell C1 with ID).
3. The add-in changes parameters of the details view using named cell values (cell C1 as CompanyID).
4. 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:

1. Customer first char index (A-Z)
2. Customers
3. Customer orders
4. Orders

Creating master-details using VBA

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.