CODEWITHSUNDEEP
ms-accessvbarecordset
DrewCraven
DrewCraven

Reputation: 39

Access VBA: Editing a RecordSet in a continuous subform

I am trying to write a function to loop through the records in a continuous subform and clear the value in a specific field (Entity_Under_Consideration, which is a lookup field represented by a combobox on the subform) for every record.

The following does not work. It doesn't throw any errors either. Can anyone see where I am going wrong?

Public Function clearEUCData(subform As Control)

    'take a clone of the subform's recordset
    Dim entityRecSet As Recordset
    Set entityRecSet = subform.Form.Recordset.Clone()

    'if there are any records in the subform...
    If entityRecSet.RecordCount > 0 Then

        'start with the first record
        entityRecSet.MoveFirst

        'iterate through each row, clearing the data in the EUC field
        Do Until entityRecSet.EOF

            With entityRecSet
                .Edit
                    Entity_Under_Consideration = 0
                .Update
            End With

        entityRecSet.MoveNext
        Loop

    End If

    'close and purge the cloned recordset
    entityRecSet.Close
    Set entityRecSet = Nothing

End Function

Upvotes: 2

Views: 1493

Answers (1)

Gustav
Gustav

Reputation: 56026

You will have to be more explicit:

With entityRecSet
    .Edit
        .Fields("Entity_Under_Consideration").Value = 0
    .Update
End With

Upvotes: 5

Related Questions