CODEWITHSUNDEEP
ms-accessvbams-access-2010daorecordset
ProtoVB
ProtoVB

Reputation: 783

Find DAO Record

Is it possible to use DoCmd.GoToRecord or DoCmd.FindRecord in order to quickly find a record in a table, edit the record and get the focus on that record (I want to start looping from that record later)?

I believe such method (if applicable) would be faster than looping through the entire recordset (especially with a large recordset).

Assuming the Primary key is 9999 (Fields(0) = 9999), I have tried:

Dim rs as DAO.Recordset

Set rs = CurrentDb.OpenRecordset("Tbltest")
DoCmd.FindRecord "9999", acEntire, True, acSearchAll, True
Debug.Print rs.Fields(0)

I get "1"; the method failed.

Upvotes: 0

Views: 1042

Answers (1)

Andre
Andre

Reputation: 27634

With a DAO.Recordset, you use the rs.FindFirst and rs.FindNext methods.

Set rs = CurrentDb.OpenRecordset("Tbltest", dbOpenDynaset)
lngValue = 9999
rs.FindFirst "myPrimaryKey = " & lngValue

' start loop from there
If Not rs.NoMatch Then
    Do While Not rs.EOF
        Debug.Print rs(0)
        rs.MoveNext
    Loop
End If

If it's a local table, there is also the rs.Seek method, but if there is a chance that the table will some day be linked from a backend or server database, I suggest sticking with the Find methods.

Upvotes: 3

Related Questions