forms - 在数据表子表单中使用级联组合框

Question

在 Access 2010 中，我有表 Task和 Action通过表具有多对多关系 ActionTask .在 Task 的表格中，我想为所有Action放一个子表单s 通过 ActionTask 与当前任务相关连接表。

这本身，我可以做到。

诀窍是Action实际上是四层表层次结构的最底层。
每个Action属于Goal , 每个 Goal属于Theme , 每个 Theme属于 Strategy .

而不是只有一个组合框列出所有可用的 Action s，我想对 Strategy 使用级联组合框/Theme/Goal/Action .

这个我也可以。

问题是当我在数据表中使用此技术时，如果我选择给定的行，则所选行显示正确的 Strategy/Theme/Goal/Action ，但在所有其他行中

Action为空白

Strategy/Theme/Goal设置为当前行的值，而不是该行的值

我尝试使用“连续表单” View 而不是“数据表” View ，但结果几乎相同。




我想我知道为什么(我的 Me.StrategyCombo = ... 回调中的 Form_Current 东西)，但我不知道实现这一目标的另一种方法。

如何让子表单正确显示所有行？

Here's the Access file ，但所有相关细节都应该在下面。

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

我解决了数据表中相关(级联)组合框的问题。
本质上，如果组合框 1 依赖于来自组合框 2 的值，它会将当前行中的“行源”应用于数据表中的所有行

解决方案:

1 在加载或设计器中，定义行源以包含所有行，以便其后面的数据值始终与行源中的选项匹配(否则显示空白值)

IE:

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

2 在依赖组合框获得焦点事件中，重新定义行源以基于来自前者所依赖的其他组合框的值。重新查询控件

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 在相关组合框失去焦点事件中，重新定义行源，使其不基于前者所依赖的其他组合框的值。

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