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)
Dim
med 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 :
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.