问题描述
在回答此问题的过程中,我编写了一个简单的函数来测试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:
为什么变量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
这篇关于“对象无效或不再设置"使用变量引用集合时的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!