首页 文章

Mainform选项卡控制将值传递给select case的子表单变量

提问于
浏览 次
0

我一直在搜索这个过去三个小时，所以如果有人问过并回答我很抱歉，但我找不到解决办法 .

我有一个mainform， frmPHDLP 和子表单（连续数据表） frmPHDUpdate . 两种形式都没有 . frmPHDLP 上有一个选项卡控件，可显示真实世界的办公地点 . 子表单将根据所选选项卡显示每个位置的员工列表 . 我在VBA中的子窗体中设置RecordSource .

第一个标签很完美 . 问题是，当我在mainform上选择一个新选项卡时，我无法获取子窗体以使用新位置重新查询SQL . SQL语句构建在子窗体的 Form_Current 事件中 .

显然运行 Me!frmPHDUpdate.Form.Requery 不会触发子窗体上的 Form_Current 事件 . 因为它为什么会这样？

三个小时 . 纳达 . 谢谢你的帮助 .

frmPHDLP代码：

Private Sub tabOffices_Change()
    Me!frmPCLPUpdateSF.Requery

End Sub

frmPHDUpdate代码：

Private Sub Form_Current()
    Dim strSearch As String
    Dim strSQL As String

    Select Case Me.Parent!tabOffices.Value
        Case 0
            strSearch = "8401"
        Case 1
            strSearch = "8400"
        Case 2
            strSearch = "8403"
        Case 3
            strSearch = "8402"
        Case 4
            strSearch = "8404"
        Case 5
            strSearch = "8405"
        Case 6
            strSearch = "8413"
        Case 7
            strSearch = "8411"
    End Select

    strSQL = "SELECT tblEmployee.ID, tblEmployee.[LastName] & "", "" & [FirstName] AS EmpName, tblPHDLProgram.MemberOfPHDL, tblOffices.OfficeID FROM tblOffices INNER JOIN (tblPHDLP RIGHT JOIN tblEmployee ON tblPHDLP.ID = tblEmployee.ID) ON tblOffices.ID = tblEmployee.Office WHERE (((tblOffices.OfficeID)= " & strSearch & "));"
    Me.RecordSource = strSQL

End Sub
ms-access subform

2 回答

  • 0

    您应该使代码更加模块化 . 此外，您不应该更改Form_Current上的表单recordsource，因为它会重新查询表单，并触发另一个当前流，并触发无限循环 . 这可能都是由Access避免无限循环引起的 .

    frmPHDUpdate代码：

    Public Sub BuildSQL()
    Dim strSearch As String
    Dim strSQL As String
    Select Case Me.Parent!tabOffices.Value
        Case 0
            strSearch = "8401"
        Case 1
            strSearch = "8400"
        Case 2
            strSearch = "8403"
        Case 3
            strSearch = "8402"
        Case 4
            strSearch = "8404"
        Case 5
            strSearch = "8405"
        Case 6
            strSearch = "8413"
        Case 7
            strSearch = "8411"
    End Select
    strSQL = "SELECT tblEmployee.ID, tblEmployee.[LastName] & "", "" & [FirstName] AS EmpName, tblPHDLProgram.MemberOfPHDL, tblOffices.OfficeID FROM tblOffices INNER JOIN (tblPHDLP RIGHT JOIN tblEmployee ON tblPHDLP.ID = tblEmployee.ID) ON tblOffices.ID = tblEmployee.Office WHERE (((tblOffices.OfficeID)= " & strSearch & "));"
    Me.RecordSource = strSQL
End Sub

Private Sub Form_Load()
    Me.BuildSQL
End Sub

    frmPHDLP代码：

    Private Sub tabOffices_Change()
    Me!frmPCLPUpdateSF.Form.BuildSQL
End Sub

    或者，甚至更好：将case语句移动到子表单SQL：

    作为frmPHDUpdate的记录源

    SELECT tblEmployee.ID, tblEmployee.[LastName] & "", "" & [FirstName] AS EmpName, tblPHDLProgram.MemberOfPHDL, tblOffices.OfficeID 
FROM tblOffices 
INNER JOIN (tblPHDLP RIGHT JOIN tblEmployee ON tblPHDLP.ID = tblEmployee.ID) ON tblOffices.ID = tblEmployee.Office 
WHERE tblOffices.OfficeID = Switch(
    Forms!frmPHDLP!tabOffices = 0, 8401,
    Forms!frmPHDLP!tabOffices = 1, 8400,
    Forms!frmPHDLP!tabOffices = 2, 8403,
    Forms!frmPHDLP!tabOffices = 3, 8402,
    Forms!frmPHDLP!tabOffices = 4, 8404,
    Forms!frmPHDLP!tabOffices = 5, 8405,
    Forms!frmPHDLP!tabOffices = 6, 8413,
    Forms!frmPHDLP!tabOffices = 7, 8411
);
    回复于
  • 0

    查看MasterLinkFields / ChildLinkFields .

    我想你可以使用tabOffices和OfficeID并删除所有这些代码 .

    回复于

相关问题