无法让DLookup识别Combobox组合框中的值问题求助

我有一个AdminPage管理页面，用于修改用户的[Password]字段，使用文本框时功能正常，但切换为Combobox（下拉组合框）选择目标用户后，组合框始终返回Null，导致功能无法正常运行。相关VBA代码如下：

Private Sub ValidateBtn_Click()

On Error Resume Next
    
        'Check if the UserNameCombo matches [UserName] in the UserT table
        If IsNull(DLookup("UserID", "UserT", "UserName = '" & Me.UserNameCombo.Value & "'")) Then
        'If IsNull(DLookup("UserID", "UserT", "UserName = '" & Me.txtCombo & "'")) Then
        MsgBox "UserName not found", vbCritical, "Denied"
        Me.UserNameCombo.SetFocus
        'Me.txtCombo.SetFocus
        
            'Check if the txtNewPassword field is empty
            ElseIf IsNull(Me.txtNewPassword) Or Me.txtNewPassword = "" Then
            MsgBox "Please input your new Password", vbInformation, "New password Required"
            Me.txtNewPassword.SetFocus
   
            'Check if the txtconfirmPassword field is empty
            ElseIf IsNull(Me.txtConfirmPassword) Or Me.txtConfirmPassword = "" Then
            MsgBox "Please confirm your new password", vbInformation, "Confirm new Password Required"
            Me.txtConfirmPassword.SetFocus
    
            'Verify if the txtNewPassword and txtConfirmPassword are the same
            ElseIf Me.txtNewPassword <> Me.txtConfirmPassword Then
            MsgBox "Your new and confirm Passwords do not match", vbCritical, "Denied"
    
            'Clear fields
            Me.UserNameCombo = ""
            'Me.txtCombo = ""
            Me.txtNewPassword = ""
            Me.txtConfirmPassword = ""
            Me.UserNameCombo.SetFocus
            'Me.txtCombo.SetFocus
    
            Else
     
                'Run Query to update the [Password] in UserT table
                DoCmd.OpenQuery "UpdatePasswordAdminQ"
                MsgBox "Password succesfully changed", vbCritical, "Congratulations"
    
                'Clear fields
                Me.txtNewPassword = ""
                Me.txtConfirmPassword = ""
                Me.txtNewPassword.SetFocus
    
            End If
    
End Sub

排查与修复方案 #

• 检查组合框基础设置
  确认组合框的Row Source已正确关联UserT表的UserName字段，Bound Column（绑定列）设置为存储UserName的列（若Row Source为SELECT UserName FROM UserT，则绑定列设为1）。同时确保Control Source为空（无需绑定到表字段，仅用于选择输入）。
• 修正组合框值的获取逻辑
  移除On Error Resume Next（它会掩盖Null相关的错误），先判断组合框是否为空，再执行后续逻辑。直接使用Me.UserNameCombo替代Me.UserNameCombo.Value即可获取选中值，无需额外调用Value属性。
• 优化DLookup的条件判断
  先校验组合框非空，避免因Null导致DLookup的条件变为UserName = ''，引发无效查询。
• 确认更新查询的条件
  检查UpdatePasswordAdminQ查询的更新条件，确保它引用组合框的选中值，例如WHERE UserT.UserName = Forms!AdminPage!UserNameCombo，否则无法定位目标用户完成更新。

修改后的VBA代码 #

Private Sub ValidateBtn_Click()
    ' 先检查组合框是否选中用户
    If IsNull(Me.UserNameCombo) Or Me.UserNameCombo = "" Then
        MsgBox "请选择目标用户", vbInformation, "提示"
        Me.UserNameCombo.SetFocus
        Exit Sub
    End If
    
    ' 验证用户是否存在
    If IsNull(DLookup("UserID", "UserT", "UserName = '" & Me.UserNameCombo & "'")) Then
        MsgBox "未找到该用户", vbCritical, "错误"
        Me.UserNameCombo.SetFocus
    ' 检查新密码输入
    ElseIf IsNull(Me.txtNewPassword) Or Me.txtNewPassword = "" Then
        MsgBox "请输入新密码", vbInformation, "提示"
        Me.txtNewPassword.SetFocus
    ' 检查确认密码输入
    ElseIf IsNull(Me.txtConfirmPassword) Or Me.txtConfirmPassword = "" Then
        MsgBox "请确认新密码", vbInformation, "提示"
        Me.txtConfirmPassword.SetFocus
    ' 验证两次密码一致性
    ElseIf Me.txtNewPassword <> Me.txtConfirmPassword Then
        MsgBox "新密码与确认密码不匹配", vbCritical, "错误"
        ' 清空输入字段
        Me.UserNameCombo = ""
        Me.txtNewPassword = ""
        Me.txtConfirmPassword = ""
        Me.UserNameCombo.SetFocus
    Else
        ' 执行密码更新
        DoCmd.OpenQuery "UpdatePasswordAdminQ"
        MsgBox "密码修改成功", vbInformation, "成功"
        ' 重置输入字段
        Me.txtNewPassword = ""
        Me.txtConfirmPassword = ""
        Me.UserNameCombo.SetFocus
    End If
End Sub

内容的提问来源于stack exchange，提问作者Debunker Debunk