设置文本框控件源时导致下拉框Me.libEntry值置空问题排查

问题背景与现象 #

• 业务逻辑：通过窗体下拉框libEntry选择数据源表（如“Library 1”/“Library 2”），切换时先更新窗体RecordSource为选中的库表，再调整文本框的ControlSource以匹配新表的字段
• 异常现象：功能整体符合预期，但执行设置文本框ControlSource的代码后，下拉框libEntry的选中值会莫名变为空
• 补充说明：
  • libTableName变量在其他子程序中赋值为Me.libEntry的当前选中值
  • libEntry的RowSource为静态数据，取自存储库表名称及列标题的辅助表Libraries
  • 因是连续窗体，必须切换字段参数才能正常显示新数据源的数据
  • 代码逻辑：遍历最多14个字段（4个必填key字段、10个可选value字段），通过DLookup从Libraries表查询当前库表的对应列标题：存在则设置文本框ControlSource并显示控件；不存在则隐藏末尾控件，最终只显示与库表字段数量匹配的控件

触发问题的代码行 #

设置以下任意一行代码后，下拉框选中值会清空：

Me.Controls("formVal" & keyValNum).controlSource = "[" & libFieldVal & "]"

或

Me.Controls("recVal" & fieldNum).controlSource = "[" & libFieldVal & "]"

完整子程序代码 #

Public Sub recordViewSetup()

    Dim loopVar As Integer
    Dim fieldNum As Integer
    Dim keyValNum As Integer
    Dim numHideField As Integer
    Dim libFieldVal As String
    Dim keyNotVal As String
        
    fieldNum = 1
    numHideField = 0
    For loopVar = 1 To 14
    
        If loopVar > 4 Then
            keyNotVal = "value"
            keyValNum = loopVar - 4
        Else
            keyNotVal = "key"
            keyValNum = loopVar
        End If
        libFieldVal = Nz(DLookup("[" & keyNotVal & keyValNum & "]", "Libraries", "[partType] = '" & libTableName & "'"), "NULL")
        
        If libFieldVal = "NULL" Then
            Me.Controls("recVal" & 14 - numHideField).Visible = False
            If keyNotVal = "key" Then
                Me.Controls("labelKey" & 4 - numHideField).Visible = False
                Me.Controls("formVal" & 4 - numHideField).controlSource = ""
            End If
            numHideField = numHideField + 1
        Else
            If keyNotVal = "key" Then
                ReDim Preserve keys(keyValNum - 1)
                keys(keyValNum - 1) = libFieldVal
                Me.Controls("labelKey" & keyValNum).Caption = libFieldVal
                Me.Controls("labelKey" & keyValNum).Visible = True
                Me.Controls("formVal" & keyValNum).controlSource = "[" & libFieldVal & "]"
            Else
                ReDim Preserve values(keyValNum - 1)
                values(keyValNum - 1) = libFieldVal
            End If
            Me.Controls("recValLbl" & fieldNum).Caption = libFieldVal
            Me.Controls("recVal" & fieldNum).controlSource = "[" & libFieldVal & "]"
            Me.Controls("recVal" & fieldNum).Visible = True
            fieldNum = fieldNum + 1
        End If
        
    Next loopVar

End Sub

内容的提问来源于stack exchange，提问作者Daniel Schmidt