Using cascading combo boxes in a datasheet subform

Question

In Access 2010, I have tables Task and Action that have a many-to-many relationship through table ActionTask. In the form for Task, I want to put a subform for all the Actions related to the current task through the ActionTask junction table.

This, in itself, I can do.

The trick is that Action is actually the bottom rank of a four-tier hierarchy of tables. Each Action belongs to a Goal, each Goal belongs to a Theme, each Theme belongs to a Strategy.

Rather than just have a combo box listing all the available Actions, I'd like to use cascading combo boxes for the Strategy/Theme/Goal/Action.

This I can also do.

The problem is when I use this technique in a datasheet, if I select a given row, the selected row shows the proper Strategy/Theme/Goal/Action, but in all the other rows

• the Action is blank
• the Strategy/Theme/Goal is set to the current row's values, rather than that row's values

I've tried using the "Continuous Forms" view rather than the "Datasheet" view, but the result is pretty much the same.

I think I know why (the Me.StrategyCombo = ... stuff in my Form_Current callback), but I don't know another way to achieve that.

How can I make the subform display all the rows properly?

---

Here's the Access file, but all the relevant details should be below.

Tables:
  Strategy   : (ID, Desc)
  Theme      : (ID, StrategyID, Desc)
  Goal       : (ID, ThemeID, Desc)
  Action     : (ID, GoalID, Desc)
  Task       : (ID, Desc, ...)
  ActionTask : (ActionID, TaskID)

Form Settings:

  [Forms]![Task]![ActionTaskSub]:
    Link Master Fields: ID
    Link Child Fields : TaskID

  [Forms]![Task]![ActionTaskSub].[Form]:
    On Current:
      Private Sub Form_Current()
          ' when the form loads a record, should reverse propegate
          ' action > goal > theme > strategy
          Dim goalID, themeID, strategyID

          ' figure out the goal, theme, and strategy that go with this action
          If (Me.ActionID) Then
            goalID = DLookup("[GoalID]", "Action", "[ID] = " & CStr(Me.ActionID))
            themeID = DLookup("[ThemeID]", "Goal", "[ID] = " & CStr(goalID))
            strategyID = DLookup("[StrategyID]", "Theme", "[ID] = " & CStr(themeID))
          End if

          ' populate the combo boxes and make the appropriate selections
          Me.StrategyCombo = strategyID
          Me.ThemeCombo.Requery
          Me.ThemeCombo = themeID
          Me.GoalCombo.Requery
          Me.GoalCombo = goalID
          Me.ActionCombo.Requery
          Me.ActionCombo = Me.ActionID
      End Sub

  [Forms]![Task]![ActionTaskSub].[Form]![StrategyCombo]:
    Row Source  : SELECT [Strategy].[ID], [Strategy].[Desc] FROM [Strategy];
    After Update:
      Private Sub StrategyCombo_AfterUpdate()
          Me.ThemeCombo = Null
          Me.ThemeCombo.Requery
          Call ThemeCombo_AfterUpdate
      End Sub

  [Forms]![Task]![ActionTaskSub].[Form]![ThemeCombo]:
    Row Source  : SELECT [Theme].[ID], [Theme].[Desc] FROM [Theme] WHERE 
                  [Theme].[StrategyID] = [Forms]![Task]![ActionTaskSub].[Form]![StrategyCombo];
    After Update:
      Private Sub ThemeCombo_AfterUpdate()
          Me.GoalCombo = Null
          Me.GoalCombo.Requery
          Call GoalCombo_AfterUpdate
      End Sub

  [Forms]![Task]![ActionTaskSub].[Form]![GoalCombo]:
    Row Source  : SELECT [Goal].[ID], [Goal].[Desc] FROM [Goal] WHERE 
                  [Goal].[ThemeID] = [Forms]![Task]![ActionTaskSub].[Form]![ThemeCombo];
    After Update:
      Private Sub GoalCombo_AfterUpdate()
          Me.ActionCombo = Null
          Me.ActionCombo.Requery
      End Sub

  [Forms]![Task]![ActionTaskSub].[Form]![ActionCombo]:
    Row Source  : SELECT [Action].[ID], [Action].[Desc] FROM [Action] WHERE 
                  [Action].[GoalID] = [Forms]![Task]![ActionTaskSub].[Form]![GoalCombo];

Answer 1

I solved the issue of dependent (Cascading) comboboxes in a datasheet. By nature, if combobox 1 is dependent on a value from combobox 2, it will apply the "rowsource" from the current row to all rows in the datatable

Solution:

1 In the load or designer, define the rowsource to include all rows so the datavalue behind it will always match with an option in the rowsource (otherwise you display a blank value)

ie:

Private Sub Form_Load()
    Me.cmbProjects.RowSource = "SELECT [Projects].[ProjectPK],  [Projects].[ProjectName], [Projects].[PI_ID] FROM [Projects]  ORDER BY  [ProjectName] "
End Sub

2 In the dependent combobox get focus event, redefine the rowsource to be based on a value from the other combo box that the former depends on. Requery the control

Private Sub cmbProjects_GotFocus()
Me.cmbProjects.RowSource = "SELECT [Projects].[ProjectPK],  [Projects].[ProjectName], [Projects].[PI_ID],  [Projects].[Status] FROM [Projects]  WHERE [PI_ID] = " + CStr(cmbPIs.Value) + " ORDER BY [PI_ID], [ProjectName] "
cmbProjects.Requery
End Sub

3 In the dependent combobox lost focus event, redefine the rowsource to NOT be based on a value from the other combo box that the former depends on.

Private Sub cmbProjects_LostFocus()
Me.cmbProjects.RowSource = "SELECT [Projects].[ProjectPK],  [Projects].[ProjectName], [Projects].[PI_ID],  [Projects].[Status] FROM [Projects]  ORDER BY [PI_ID], [ProjectName] "
cmbProjects.Requery
End Sub

Answer 2

You can't. Any action applies to the subform current record but appears to affect all controls. There are various work-arounds.