Access VBA：验证Table A的CTR_NBR是否存在于Table B报错求助

原代码存在几个关键问题，既导致了类型不匹配错误，也无法实现预期逻辑：

• 变量名错误：rst.Fields(2)应为rst_B.Fields("CTR_NBR")（用字段名比索引更可靠，避免列顺序变动引发问题）
• 逻辑漏洞：仅对比Table B的默认首行，未检查所有记录
• 未处理Null值：若字段值为Null，直接比较会触发类型不匹配

方案1：使用DAO FindFirst（高效推荐） #

利用记录集的FindFirst方法直接定位匹配值，无需遍历所有行，适合大数据量场景：

Dim dbs As DAO.Database
Dim rst_A As DAO.Recordset
Dim rst_B As DAO.Recordset
Dim ctrValue As Variant

Set dbs = CurrentDb
Set rst_A = dbs.OpenRecordset("Table A", dbOpenDynaset)
Set rst_B = dbs.OpenRecordset("Table B", dbOpenDynaset)

' 先获取Table A的CTR_NBR值，处理Null情况
If Not IsNull(rst_A!CTR_NBR) Then
    ctrValue = rst_A!CTR_NBR
    ' 构建查找条件：文本型字段加单引号，数值型去掉单引号
    ' 示例为文本型，若为数值型改为 "CTR_NBR = " & ctrValue
    rst_B.FindFirst "CTR_NBR = '" & Replace(ctrValue, "'", "''") & "'"
    
    ' 判断是否找到匹配项
    If rst_B.NoMatch Then
        MsgBox "Value does not exist in Table B"
    Else
        MsgBox "Value exists in Table B"
    End If
Else
    MsgBox "Table A's CTR_NBR is empty"
End If

' 关闭记录集释放资源
rst_A.Close
rst_B.Close
Set rst_A = Nothing
Set rst_B = Nothing
Set dbs = Nothing

方案2：遍历Table B所有行（适合小数据量） #

逐行遍历Table B，逐一对比CTR_NBR值：

Dim dbs As DAO.Database
Dim rst_A As DAO.Recordset
Dim rst_B As DAO.Recordset
Dim ctrValue As Variant
Dim isFound As Boolean

Set dbs = CurrentDb
Set rst_A = dbs.OpenRecordset("Table A", dbOpenDynaset)
Set rst_B = dbs.OpenRecordset("Table B", dbOpenDynaset)

isFound = False
If Not IsNull(rst_A!CTR_NBR) Then
    ctrValue = rst_A!CTR_NBR
    ' 遍历Table B所有行
    Do While Not rst_B.EOF
        ' 处理Null值，避免类型不匹配
        If Not IsNull(rst_B!CTR_NBR) Then
            If rst_B!CTR_NBR = ctrValue Then
                isFound = True
                Exit Do ' 找到后提前退出循环
            End If
        End If
        rst_B.MoveNext
    Loop
End If

' 弹出结果提示
If isFound Then
    MsgBox "Value exists in Table B"
Else
    MsgBox "Value does not exist in Table B"
End If

' 释放资源
rst_A.Close
rst_B.Close
Set rst_A = Nothing
Set rst_B = Nothing
Set dbs = Nothing

关键注意事项 #

• 字段类型统一：确保两个表的CTR_NBR字段类型一致（同为文本/数值），若类型不同需用CStr()/CLng()等函数转换后再比较
• Null值判断：必须先检查字段是否为Null，否则直接比较会触发类型不匹配错误
• 资源释放：使用完记录集后务必关闭并释放对象，避免内存泄漏

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