Code To Loop Through and Edit Recordsets

Question

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"

HansUp · Answer 1 · 2011-05-27T15:00:26.290

2

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

edited May 27 '11 at 15:00

answered May 25 '11 at 15:45

HansUp

95,961
11
77
135

Is fieldHistory a table or query? The dbOpenTable recordset type can only be used with an actual table, not a query. – HansUp May 27 '11 at 14:30
it is a query - what should I replace the dbOpenTable with then? – Zack May 27 '11 at 14:36
First just discard dbOpenTable for your query --- it should work. Later you can check Access' Help topic for OpenRecordset, and look at the RecordsetTypeEnum Enumeration. Or read about it at msdn: http://msdn.microsoft.com/en-us/library/bb225803(office.12).aspx – HansUp May 27 '11 at 14:41
I actually did try that - I tried Set rs = db.OpenRecordset("fieldHistory") and it gave me an error like expecting parameter. – Zack May 27 '11 at 14:46
In that case the database engine doesn't recognize something in your query and thinks that unrecognized thing must be a parameter. Open the query from the query designer and see what Access thinks is the parameter. – HansUp May 27 '11 at 15:02
I have opened the query in the query designer and gotten no errors. Also I tried that code and again got "Too few parameters." I have also tried replacing "fieldHistory" with other queries and SQL statments always getting the same error of "Too few parameters" – Zack May 27 '11 at 15:18
In that case I am completely stumped. The code I added to my answer worked without error after I renamed an existing SELECT query to fieldHistory. Seems you now have a new question "How do I open a query as a DAO recordset?" Include the query's SQL and just enough of the VBA code to illustrate the problem. – HansUp May 27 '11 at 15:23
Uh, deleting the record from the recordset deletes it from the source table. Is that what's desired? – David-W-Fenton May 28 '11 at 21:08
@Zack: since it _seems_ that you're fairly new to Access, I'm going to explicitly ask some things that the rest of us may be assuming. When you "open the query in the query designer" do you mean that in Datasheet View or just in Design View? If Datasheet, is there a pop-up before the results are displayed, asking you to enter a value? If so, that's your "parameter". – RolandTumble Jun 03 '11 at 18:39

score 1 · Answer 2 · answered May 25 '11 at 19:00

1

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:

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

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

answered May 25 '11 at 19:00

RolandTumble

4,633
3
32
37

I cannot come up with a justification for filtering an already-open recordset. It seems like sloppy programming, or premature optimization. – David-W-Fenton May 28 '11 at 21:08
@David: You're probably correct in Access. I have hazy memories of a VB6/SQL Server project (unbound forms) where it seemed appropriate at the time.... – RolandTumble May 31 '11 at 17:38
It might be justifiable in a very low-bandwidth environment, I guess, on the assumption that it retrieves the recordset only once. But that's not a very good environment for Access... – David-W-Fenton Jun 02 '11 at 20:02
It's coming back to me.... We were using disconnected ADO recordsets, and it was "very low bandwidth". Some of our clients were on dial-up (thankfully not all, and they stopped that in the first major update...). – RolandTumble Jun 03 '11 at 18:57

Code To Loop Through and Edit Recordsets

2 Answers2