recordset.find using variable VBA ADODB

Question

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

Answer 1

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

According to this: 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.

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

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 ...

Answer 2

I don't use ADO very often, but 2 ideas I had neither very graceful-

1) Application.WorksheetFunction.Transpose(rst2.GetRows) (make sure to use .MoveLast and .MoveFirst first) to get an array of the recordset which you can then iterate through to find the ID you are looking for...

2) Same idea but just do it in the recordset...

Also, as others have previously mentioned a new query would certainly get you the value youre looking for...

Hope this helps, TheSilkCode