recordset.find 使用变量 VBA ADODB
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41677943/
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
recordset.find using variable VBA ADODB
提问by l.wach
I am getting pretty desperate trying to get this trivial search to work:
我非常绝望地试图让这个微不足道的搜索工作：
rst2.Find "ID = '" & messID & "'"
I have tried just about any combination but it just never returns a search result. the whole code would be here:
我几乎尝试过任何组合，但它从未返回搜索结果。整个代码将在这里：
Option Compare Database
Option Explicit
'Modul zum Updaten des Status eines Messmittels in der Stammdatenbank (Entnommen/Verfügbar)3
Public Function updateStatus()
Dim rst2 As ADODB.Recordset
Dim rst As ADODB.Recordset
Dim messID As String
Set rst = New ADODB.Recordset 'Stammdaten zur Bearbeitung ?ffnen
rst.ActiveConnection = CurrentProject.AccessConnection
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
rst.Open "Stammdaten"
rst.MoveFirst
Set rst2 = New ADODB.Recordset 'zur Bearbeitung ?ffnen
rst2.ActiveConnection = CurrentProject.AccessConnection
rst2.CursorType = adOpenKeyset
rst2.LockType = adLockOptimistic
rst2.Open "Verwendung"
Do While Not rst.EOF
messID = rst!ID
Debug.Print messID
rst2.Find "ID = '" & messID & "'"
If rst2.EOF = True Then 'Falls nicht vorhanden
Debug.Print "Keine Verwendung gefunden!"
Else
rst2.Sort = "Nr DESC"
rst2.MoveFirst
Debug.Print rst2!Status
End If
rst.MoveNext
Loop
rst.Close
rst2.Close
End Function
What am I missing? I literally tried hunderds of diffrent search strings :(
我错过了什么？我真的尝试了数百个不同的搜索字符串:(
采纳答案by A.S.H
You have a table-type recorset and you're searching it by the key. This is the typical use-case for the Seekmethod instead of the Findmethod.
您有一个表类型的记录集，并且正在通过键进行搜索。这是Seek方法而不是Find方法的典型用例。
According to this: https://support.microsoft.com/en-us/kb/108149
根据这个：https: //support.microsoft.com/en-us/kb/108149
The find methods (FindFirst, FindLast, FindNext, and FindPrevious) apply to Dynasets and Snapshots but not to Table objects. Conversely, the Seek method is available only on the Table object.
查找方法（FindFirst、FindLast、FindNext 和 FindPrevious）适用于动态集和快照，但不适用于表对象。相反，Seek 方法仅适用于 Table 对象。
Although that page seems focused on DAO more than ADO, but the same logic should apply to both cases.
尽管该页面似乎更侧重于 DAO 而不是 ADO，但同样的逻辑应该适用于这两种情况。
I think you should try the Seekmethod https://msdn.microsoft.com/en-us/library/ms675109(v=vs.85).aspx
我认为你应该试试这个Seek方法https://msdn.microsoft.com/en-us/library/ms675109(v=vs.85).aspx
rst2.Seek "=", messID
If rst2.NoMatch Then ' not found ...
回答by TheSilkCode
I don't use ADO very often, but 2 ideas I had neither very graceful-
我不经常使用 ADO，但是我有两个想法都不是很优雅-
1) Application.WorksheetFunction.Transpose(rst2.GetRows)(make sure to use .MoveLastand .MoveFirstfirst) to get an array of the recordset which you can then iterate through to find the ID you are looking for...
1）Application.WorksheetFunction.Transpose(rst2.GetRows)（确保首先使用.MoveLast和.MoveFirst）获取记录集的数组，然后您可以遍历该数组以找到您要查找的ID...
2) Same idea but just do it in the recordset...
2）同样的想法，但只是在记录集中做......
Also, as others have previously mentioned a new query would certainly get you the value youre looking for...
此外，正如其他人之前提到的，新查询肯定会为您提供您正在寻找的价值...
Hope this helps, TheSilkCode
希望这会有所帮助，TheSilkCode
回答by Dave Phillips
I know this is old, but I had this same issue and figured it out. The issue was that the pointer for the recordset wasn't at the BOF and therefore, it couldn't find the value I was looking for because it was "above" the current place of the pointer. The .Find doesn't wrap around to search the whole recordset.
我知道这很旧，但我遇到了同样的问题并想通了。问题是记录集的指针不在 BOF 处，因此找不到我正在寻找的值，因为它在指针的当前位置“上方”。.Find 不会环绕搜索整个记录集。
I solved this problem by just adding a rs.moveFirst right before the .Find line. This solved the problem.
我通过在 .Find 行之前添加一个 rs.moveFirst 解决了这个问题。这解决了问题。
