使用变量VBA ADODB的recordset.find [英] recordset.find using variable VBA ADODB

问题描述

我变得非常绝望，试图让这个琐碎的搜索工作起来:

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 :(

推荐答案

您有一个表类型的重新设置，并且正在通过键进行搜索.这是Seek方法而不是Find方法的典型用例.

You have a table-type recorset and you're searching it by the key. This is the typical use-case for the Seek method instead of the Find method.

据此: https://support.microsoft.com/zh-CN /kb/108149

查找方法(FindFirst，FindLast，FindNext和FindPrevious)适用于动态集和快照，但不适用于Table对象.相反，Seek方法仅在Table对象上可用.

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.

尽管该页面似乎比ADO更关注DAO，但在两种情况下都应采用相同的逻辑.

Although that page seems focused on DAO more than ADO, but the same logic should apply to both cases.

我认为您应该尝试Seek方法

I think you should try the Seek method https://msdn.microsoft.com/en-us/library/ms675109(v=vs.85).aspx

rst2.Seek "=", messID
If rst2.NoMatch Then ' not found ...

这篇关于使用变量VBA ADODB的recordset.find的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！