468,114 Members | 1,983 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,114 developers. It's quick & easy.

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

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
Expand|Select|Wrap|Line Numbers
  1. EFFDT       ACTION    
  2. 8/23/2015  PLA
  3. 12/4/2015  PLA    Need to remove
  4. 3/6/2016   RFL
  5. 10/18/2018 PLA
  6. 1/28/2019  PLA    Need to remove
  7. 6/9/2019   RFL
Expand|Select|Wrap|Line Numbers
  1. Public Sub OpenRecordset()
  2. Dim db As Database
  3. Dim rs As Recordset
  4.  
  5. Set db = CurrentDb
  6.  
  7. Set rs = db.OpenRecordset("tblLOA")
  8.  
  9. '---------Work in Process for evaluating Records and Delete
  10.             Last = rs.MoveLast
  11.         For i = Last To 2 Step -1
  12.                 If (Fields(i, "Action").Value) = (Fileds(i - 1, "Action").Value) Then
  13.                 Fields(i, "Action").EntrieRow.Delete
  14.                 End If
  15. '--------------------------------------------------------------------------------------
  16. For i = 0 To rs.RecordCount - 1
  17. Debug.Print rs.Fields("ACTION")
  18.         rs.MoveNext
  19. Next i
  20.  
  21. rs.Close
  22. Set rs = Nothing
  23. db.Close
2 Weeks Ago #1
1 2967
NeoPa
32,023 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. Set rs = db.OpenRecordset(...)
  2. With rs
  3.     Do Until .EOF
  4.         ...
  5.         Call .MoveNext
  6.     Loop
  7. 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 :
Expand|Select|Wrap|Line Numbers
  1. 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.
2 Weeks Ago #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Mad Hot dog | last post: by
3 posts views Thread by cmrchs | last post: by
21 posts views Thread by Gaemic | last post: by
3 posts views Thread by Grant Schenck | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.