CODEWITHSUNDEEP
ms-access
Zack
Zack

Reputation: 67

Code To Loop Through and Edit Recordsets

I have found how to loop through recordsets with the following link:

Code to loop through all records in MS Access

However, I want to know if it is possible if I can remove a record from the recordset if it doesn't meet criteria that I specify in the loop.

EDIT

I am now getting an error with the following code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("fieldHistory", dbOpenTable)

where fieldHistory is the name of the query recordset I want to open. Why am I getting this error? The last line of code there is the source of the error and Access simply states "Invalid operation"

Upvotes: 1

Views: 5286

Answers (2)

HansUp
HansUp

Reputation: 97101

Yes, you can use the DAO recordset's Delete method to delete the current record. This example will delete rows where the fname value is "xxx".

Public Sub DeleteRecordsetRow()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblDiscardMe", dbOpenTable)
    Do While Not rs.EOF
        If rs!fname = "xxx" Then
            rs.Delete
            '* the next line would trigger *'
            '* error 3167: "Record is deleted." *'
            ''Debug.Print rs!fname
        End If
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Notice that immediately after rs.Delete (i.e. before MoveNext), the deleted row is still "current", but you can't access its values. You can uncomment the Debug.Print line to examine this further.

Edit: Since your record source is a query rather than a table, try this to narrow down the reason you're getting an error with OpenRecordset.

Public Sub foo20110527a()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("fieldHistory")
    If Not (rs.BOF And rs.EOF) Then
        rs.MoveLast
        MsgBox "RecordCount: " & rs.RecordCount
    Else
        MsgBox "No records"
    End If
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Upvotes: 2

RolandTumble
RolandTumble

Reputation: 4703

Since you used English (rather than English-like technical terms), your intent isn't very clear. You ask if you can "...remove a record...", which can mean either that you want to Delete it (in which case you already have a good answer form HansUp), or that you want to filter it out so that you don't see it, while leaving it in the underlying database.

If your intent is the latter (filtering), you have two choices:

  1. Use a SQL statement with a WHERE clause in the original OpenRecordset call.
  2. Use the Recordset's .Filter property before you enter the loop.

Access comes with adequate (if not stellar) Help on either topic.

Upvotes: 1

Related Questions