CODEWITHSUNDEEP
ms-accessvba
Michel837
Michel837

Reputation: 11

Get the current record of a recordset

I have a recordset of objects employees, I can move using the .MoveX's methods. However, I would like to know how I can create an object employee with the current record of the recordset employees.

Dim employees As DAO.Recordset
Dim employee  As DAO.Recordset

Set employees = database.OpenRecordset("SELECT * FROM EMPLOYEES", dbOpenDynaset)
Do While Not employees.EOF

    // Some magic function 'GetCurrentRecord'
    employee = employees.GetCurrentRecord
    // I can do stuff with 'employee'
    MsgBox "Name: " & employee.Fields("Name").Value

    employees.MoveNext
Loop

Upvotes: 1

Views: 9721

Answers (2)

Gustav
Gustav

Reputation: 55961

As you mention yourself, the obvious method is to create a new recordset with that single current record:

Dim employees As DAO.Recordset
Dim employee  As DAO.Recordset

Set employees = database.OpenRecordset("SELECT * FROM EMPLOYEES", dbOpenDynaset)
Do While Not employees.EOF
    ' Specify condition when to extract the current record.
    If SomeCondition = True Then
        employees.Filter = "Id = " & employees!Id.Value & ""
        Set employee = employees.OpenRecordset
        Exit Do
    End If   
    employees.MoveNext
Loop

If Not employee Is Nothing Then
    ' I can do stuff with 'employee'
    MsgBox "Name: " & employee.Fields("Name").Value
End If

Upvotes: 0

Sergey S.
Sergey S.

Reputation: 6336

From example not clear why you need employee object, you can use employees recordset for accessing data in current record in each loop step, but if you want, for instance, to find some recordset rows in loop and then access those rows, you can use Bookmark recordset property for storing/restoring current recordset position.

Upvotes: 1

Related Questions