Looping in recordset evaluating Object in Rows-remove the record if the object equals

[sdjmagoo]

I am needing to evaluate a record set and remove records. My table has multiple Records with Unique Effective Dates by Action. I need to compare each record to the next record and remove the record in the table if the Action in the record below it is equal. I started with a simple OpenRecordset for printing to identify database and record set below. I am a beginner of Access/VBA

EFFDT	   ACTION	
8/23/2015  PLA
12/4/2015  PLA    Need to remove
3/6/2016   RFL
10/18/2018 PLA
1/28/2019  PLA    Need to remove
6/9/2019   RFL

Public Sub OpenRecordset()
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb

Set rs = db.OpenRecordset("tblLOA")

'---------Work in Process for evaluating Records and Delete
    		Last = rs.MoveLast
		For i = Last To 2 Step -1
    			If (Fields(i, "Action").Value) = (Fileds(i - 1, "Action").Value) Then
			    Fields(i, "Action").EntrieRow.Delete
        		End If
'--------------------------------------------------------------------------------------
For i = 0 To rs.RecordCount - 1
Debug.Print rs.Fields("ACTION")
        rs.MoveNext
Next i

rs.Close
Set rs = Nothing
db.Close

[NeoPa]

Hi & welcome to Bytes.com.

Let's start with a quick tip (Before Posting (VBA or SQL) Code) about posting code that will help everyone involved. Lots of time & effort can be saved that way.

Moving on to your fundamental question. Again, let's start with some basic advice. Database tables (Access or anywhere) are sets of data, but they are not generally considered to be ordered sets of data. It may be that in some circumstances they can be used that way - but it's not good practice ever to assume that data from a table has any order. That's not a serious problem as the SQL ORDER BY clause can produce an ordered set for you. You must design the table in such a case to ensure it has data that you can use to specify the order. Options for this are to set a standard AutoNumber field or to set a field where the DefaultValue is =Now().

At this point I'll assume you're now working with a Recordset in your code that includes ordering properly. It's actually one of those rare areas where processing through the Recordset in VBA is easier & more straightforward than trying to execute the same operation directly in SQL. However, I strongly advise against a procedure called OpenRecordset() as that is a reserved name. It can work for you, but it will throw you over many pitfalls and is certainly better to avoid.

Processing through a DAO.Recordset is normally handled using a Do loop such as :

Set rs = db.OpenRecordset(...)
With rs
    Do Until .EOF
        ...
        Call .MoveNext
    Loop
End With

Your procedure should have a String variable (EG. strLast) Dimmed which you can use to save the value for the last (previous) record. When the new value equals the previous one then delete the current record.

Deleting a record, within your With structure & Do loop of course, is done something like :

Call .Delete

In case it helps, MS have a page (Delete a record from a DAO Recordset) with example code for doing very similar work you can use as a starting point. The style isn't great (It doesn't even use With ... End With.) but it illustrates what's what.