CODEWITHSUNDEEP
ms-access
DarkW1nter
DarkW1nter

Reputation: 2851

navigating and updating in MS Access datasheet subform

Im using the code below to loop through the recordset of a subform in datasheet view and update a field, so that I avoid doing a SQL update as I want to also manually update when needed and this way avoids the "The data has been changed by another user..." message.

My question is, if I have 10 records in the recordset but the cursor is on the 5th record down on the form, then only the records from that point on get changed, eg 5 to 10. Should this not update all the records? I can't figure out why it's not.

thanks

Dim tmprs As DAO.Recordset
Dim fld As DAO.Field
Dim bkmrk As Variant
Set tmprs = Forms!frmtanks!Child67.Form.Recordset
bkmrk = Me.Bookmark
tmprs.MoveFirst
While Not tmprs.EOF
    For Each fld In tmprs.Fields
        If fld.name = "freeDays" Then
            freeDays = fd
        End If
    Next
    tmprs.MoveNext
Wend
Me.Bookmark = bkmrk

Upvotes: 0

Views: 598

Answers (1)

Brad
Brad

Reputation: 12253

You should use the form's RecordsetClone to do your updates. This will not alter the active record of your form when you use the MoveNext/MovePrevious type functions, start from first record of the recordset, and not require you to move back to your record with a bookmark.

Dim tmprs As DAO.Recordset
Dim fld As DAO.Field
Dim bkmrk As Variant
Set tmprs = Forms!frmtanks!Child67.Form.RecordsetClone

On Error GoTo Cant_Update
While Not tmprs.EOF
    tmprs("freeDays") = fd
    tmprs.Update
    tmprs.MoveNext
Wend

Upvotes: 0

Related Questions