“对象无效或不再设置"使用变量引用集合时 [英] "Object Invalid or no Longer Set" when using Variable to Reference Collection

问题描述

在回答此问题的过程中，我编写了一个简单的函数来测试MS Access表是否包含所有字段在提供的数组中:

In the process of answering this question, I wrote a simple function to test whether an MS Access table contained all fields in a supplied array:

Function ValidateFields(strTbl As String, arrReq As Variant) As Boolean
    Dim fld
    Dim fldTmp As Field
    On Error GoTo err
    For Each fld In arrReq
        Set fldTmp = CurrentDb.TableDefs(strTbl).Fields(fld)
    Next fld
    ValidateFields = True
err:
    Exit Function
End Function

?ValidateFields("TempTable", Array("Field1", "Field2", "Field3"))
False

这可以按预期执行，但是为了提高效率，我尝试将Fields Collection分配给For Each循环之外的变量:

This performs as expected, however, to improve the efficiency I tried assigning the Fields Collection to a variable outside of the For Each loop:

Function ValidateFields(strTbl As String, arrReq As Variant) As Boolean
    Dim fld
    Dim fldTmp As Field
    Dim colFld As Fields
    Set colFld = CurrentDb.TableDefs(strTbl).Fields
    On Error GoTo err
    For Each fld In arrReq
        Set fldTmp = colFld(fld)
    Next fld
    ValidateFields = True
err:
    Exit Function
End Function

现在，如果我注释掉On Error语句，则会收到以下错误，并以Set fldTmp = colFld(fld)行突出显示为原因:

And now, if I comment out the On Error statement, I receive the following error with the line Set fldTmp = colFld(fld) highlighted as the cause:

运行时错误'3420':

对象无效或不再设置.

Run-time error '3420':

Object invalid or no longer set.

为什么变量colFld在For Each循环中会丢失其值?

Why would the variable colFld lose its value within the For Each loop?

推荐答案

这里的问题是:

CurrentDb创建当前打开的数据库的DAO.Database对象.您的TableDef是该成员.

CurrentDb creates a DAO.Database object of the currently open database. Your TableDef is a member of that.

但是，由于您没有存储该对象，因此在将tabledef复制到对象之后，它会立即关闭并释放，并且成员也将被释放.

But since you're not storing that object, it gets closed and deallocated right after you copied the tabledef to an object, and with it the members will be deallocated too.

存储数据库对象，成员也将保留:

Store the database object, and the members will also persist:

Function ValidateFields(strTbl As String, arrReq As Variant) As Boolean
    Dim fld
    Dim fldTmp As Field
    Dim colFld As Fields
    Dim db As DAO.Database
    Set db = CurrentDb
    Set colFld = db.TableDefs(strTbl).Fields
    On Error GoTo err
    For Each fld In arrReq
        Set fldTmp = colFld(fld)
    Next fld
    ValidateFields = True
err:
    Exit Function
End Function

这篇关于“对象无效或不再设置"使用变量引用集合时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！