vba 如何测试记录集中是否存在项目？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18317059/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to test if item exists in recordset?
提问by Jeff Brady
I have a crosstab query that is being loaded into a recordset. I'm then writing the query fields to an Excel spreadsheet. The problem is that a field may not exist based on the query results.
我有一个正在加载到记录集中的交叉表查询。然后我将查询字段写入 Excel 电子表格。问题是根据查询结果可能不存在字段。
For example, I have the following line:
例如，我有以下几行：
oSheet5.Range("F1").Value = rsB2("AK")
...which would write the value of the recordset item named "AK" to the spreadsheet. But if "AK" doesn't exist, I get an error Item not found in this collection.
...这会将名为“AK”的记录集项的值写入电子表格。但是如果“AK”不存在，我会收到一个错误Item not found in this collection。
How I can I test to see if there's an item named "AK"?
我如何测试以查看是否有名为“AK”的项目？
I tried...
我试过...
If rsB2("AK") Then
oSheet5.Range("F" & Count).Value = rsB2("AK")
End If
...but that didn't work.
……但这没有用。
I also tried...
我也试过...
If rsB2("AK") Is Nothing Then
oSheet5.Range("F" & Count).Value = ""
Else
oSheet5.Range("F" & Count).Value = rsB2("AK")
End If
...and still the same error.
......仍然是同样的错误。
There are 50+ items/fields to check .. all states in USA plus a few extras. Thanks!
有 50 多个项目/字段需要检查……美国所有州以及一些额外项目。谢谢！
回答by Khinsu
You can use Recordset.FindFirst Method (DAO)take a look hereor here
你可以使用Recordset.FindFirst Method (DAO)看看这里或这里
Small example:
小例子：
Sub FindOrgName()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
'Get the database and Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblCustomers")
'Search for the first matching record
rst.FindFirst "[OrgName] LIKE '*parts*'"
'Check the result
If rst.NoMatch Then
MsgBox "Record not found."
GotTo Cleanup
Else
Do While Not rst.NoMatch
MsgBox "Customer name: " & rst!CustName
rst.FindNext "[OrgName] LIKE '*parts*'"
Loop
'Search for the next matching record
rst.FindNext "[OrgName] LIKE '*parts*'"
End If
Cleanup:
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub
回答by HansUp
You could add an error handler to catch the item not found error ... ignore it and/or do something else instead.
您可以添加一个错误处理程序来捕获未找到项目的错误......忽略它和/或做其他事情。
Or if the first recordset field always maps to the first sheet column regardless of the field's name, you can reference it by its ordinal position: rsB2(0)
或者，如果第一个记录集字段始终映射到第一个工作表列，而不管字段的名称如何，您可以通过其序号位置引用它： rsB2(0)
Or you could examine the recordset's Fieldscollection to confirm the field name is present before attempting to retrieve its value.
或者，您可以Fields在尝试检索其值之前检查记录集的集合以确认该字段名称是否存在。
After you open the recordset, load a dictionary with its field names. This code sample uses late binding. I included comment hints in case you want early binding. Early binding requires you to set a reference for Microsoft Scripting Runtime.
打开记录集后，加载带有字段名称的字典。此代码示例使用后期绑定。如果您想要早期绑定，我包含了注释提示。早期绑定要求您设置Microsoft Scripting Runtime的引用。
Dim objDict As Object 'Scripting.Dictionary
'Set objDict = New Scripting.Dictionary
Set objDict = CreateObject("Scripting.Dictionary")
Dim fld As DAO.Field
For Each fld In rsB2.Fields
objDict.Add fld.Name, vbNullString
Next
Then later you can use the dictionary's Existsmethod to your advantage.
然后，您可以使用字典的Exists方法对您有利。
If objdict.Exists("AK") = True Then
oSheet5.Range("F1").Value = rsB2("AK")
End If
