472,995 Members | 1,579 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,995 software developers and data experts.

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
  5. Set db = CurrentDb
  7. Set rs = db.OpenRecordset("tblLOA")
  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
  21. rs.Close
  22. Set rs = Nothing
  23. db.Close
Mar 30 '21 #1
1 3609
32,550 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.
Mar 30 '21 #2

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

Similar topics

by: Mad Hot dog | last post by:
Hello group! I use MS Visual C++ 6.0, ADO, MS SQL Server 2000. When I attempt to open my database I meet with a following problem: when I try to get a bookmark of the current record in a...
by: Alex | last post by:
Is there a way to reference the "parent" object that creates a child object from within the child object? Or do I have to pass a reference to the parent object to the constructor of the child...
by: DrUg13 | last post by:
In java, this seems so easy. You need a new object Object test = new Object() gives me exactly what I want. could someone please help me understand the different ways to do the same thing in...
by: Ian | last post by:
I want to open a form at a particular record, but I think I'm running into problems because the recordsource query is executing asynchronously. In the form's open event I use...
by: Chua Wen Ching | last post by:
Hi there, I am super confuse on this recommendation by FxCop. 1) http://www.gotdotnet.com/team/fxcop/docs/rules/UsageRules/OperatorEqualsOverridesRequireEqualsOverridel.html First of all,...
by: cmrchs | last post by:
Hi, why is it requested that when Equals() is implemented in a class that GethashCode() must be implemented as well ? thnx Chris ...
by: Gaemic | last post by:
Some argues that you should not use the C# keyword "object" instead of the real type System.Object. What would be the general rule of thumb for choosing one over the other? Thanks in advanced....
by: nick | last post by:
I have the following code in JScript. Should I close and release the database objects before run response.redirect("some url")? or will the response.redirect automatically close the database...
by: tlk | last post by:
Hi, everyone. I'm having a problem that I hope some more experienced developers can help me figure-out. We have a project called Records that has been around for a year or so. We're currently...
by: Grant Schenck | last post by:
Hello, I have a Windows Service developed in C# .NET. I'm making it a remote server and I can, via an IPC Channel, expose methods and call them from a client. However, I now want my remoted...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.